MYSQL Contraints - Multiple Checks

Question

Well i am here writing some constraints for a db I am working on and i was wondering if I could do the following:

ALTER TABLE Course
ADD CONSTRAINT cs_level
CHECK (clevel in ('P','I','II','III'))

...instead of this:

ALTER TABLE Course
ADD CONSTRAINT cs_level
CHECK (clevel = 'P' OR clevel = 'I' OR clevel = 'II' OR clevel = 'III')

Answer 1

MySQL only supports foreign key constraints, not check constraints. Wherever you got that syntax from, it wasn't the MySQL manual.

Answer 2

Unfortunately, MySQL does not support the CHECK-constraints. They are parsed but ignored.

From the reference manual:

The CHECK clause is parsed but ignored by all storage engines.

You can try to put this check constraint in your business logic before inserting the row.

As Joe Celko says in his book Joe Celko's SQL for Smarties:

Small "pseudo-SQL" products have appeared in the open source arena. Languages such as MySQL are very different in syntax and semantics from Standard SQL, often being little more than a file system interface with borrowed reserved words

A little harsh indeed, but in a way he's correct, since MySQL doesn't support a bunch of Standard SQL features.

Answer 3

Even if MySQL enforced CHECK constraints, I'd still be creating a COURSE_LEVEL table:

• COURSE_LEVEL_CODE, VARCHAR(3), primary key
• COURSE_LEVEL_DESCRIPTION, VARCHAR(120)

...and create a FOREIGN KEY constraint on the COURSE table, clevel column:

ALTER TABLE COURSE
ADD FOREIGN KEY (clevel) REFERENCES COURSE_LEVEL(COURSE_LEVEL_CODE)