MySQL 5.6 Check Constraint Implementation

Question

I was trying to add a check constraint to my create table DDL in the following manner:

CREATE TABLE TYPE_SCENE
 (
 ID_TYPE_SCENE  INTEGER(10)  PRIMARY KEY AUTO_INCREMENT,
 ID_TYPE  INTEGER(10)  NOT NULL,
 CREATED_BY      VARCHAR(20)  NOT NULL,
 CREATED_ON      TIMESTAMP  NOT NULL,
 COMPLETE_SCENE  CHAR(1), 
 INCOMPLETE_SCENE    CHAR(1),
 AMENDED_BY      VARCHAR(20),
 AMENDED_ON      TIMESTAMP,
 CONSTRAINT FK_TYPE FOREIGN KEY (ID_TYPE) REFERENCES  TYPE_DATA(ID_TYPE),
 CONSTRAINT COMPLETE_SCENE_CHECK CHECK (COMPLETE_SCENE IN (  'M', 'N', 'O'  )),
 CONSTRAINT INCOMPLETE_SCENE_CHECK CHECK (INCOMPLETE_SCENE IN ( 'A', 'B', 'C' ))
 )

However when I run this query in MySQL workbench, I get the following error: Error Code: 1215. Cannot add foreign key constraint

I know the problem is because of the CHECK constraint as the query gives me a syntax error pointing at the CHECK constraints. What am I doing wrong? I thought this was the way to implement CHECK constraints. Why does it say foreign key constraint problem?

Funny thing is it used to work on MySQL 5.1 before I upgraded to MySQL 5.6.

I read a few forums and questions where it says MySQL does not support this anymore and use triggers. However it used to work with earlier MySQL versions. Secondly, I should still be able to execute the query as the problem is that the constraint does not work in MySQL, however it never was a syntax issue.

Any ideas?

Thanks!

Just remove the check constraint (or comment them out). As the documentation clearly states: "The CHECK clause is parsed but ignored by all storage engines. " (http://dev.mysql.com/doc/refman/5.6/en/create-table.html). — Gordon Linoff, Feb 23 '15 at 22:23
@GordonLinoff, that was quick, I have updated my question. Yes I am aware of what you have said. But thats not the real issue. I am getting a syntax error and I cant execute the query when I run it. This used to work before. Although I understand that the CHECK constraint is not actually working even when it is added to a column. — Aman Mohammed, Feb 23 '15 at 22:25

MySQL 5.6 Check Constraint Implementation

0 Answers0