How do I add a custom CHECK constraint on a MySQL table?

Question

I am having trouble with this table

CREATE TABLE `Participants` (   `meetid` int(11) NOT NULL,   `pid` varchar(15) NOT NULL,   `status` char(1) DEFAULT NULL,   PRIMARY KEY (`meetid`,`pid`),   CONSTRAINT `participants_ibfk_1` FOREIGN KEY (`meetid`) REFERENCES `Meetings` (`meetid`) ON DELETE CASCADE   CONSTRAINT `participants_ibfk_2` CHECK (status IN ('a','d','u'))   CONSTRAINT `participants_ibfk_3` CHECK (pid IN (SELECT name FROM Rooms) OR pid IN (SELECT userid FROM People)) ); 

I want to have a foreign key constraint, and that works. Then, I also want to add a constraint to the attribute status so it can only take the values 'a', 'd' and 'u'. It is not possible for me to set the field as Enum or set.

Can anyone tell me why this code does not work in MySQL?

Answer 1

CHECK constraints are not supported by MySQL. You can define them, but they do nothing (as of MySQL 5.7).

From the manual:

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

The workaround is to create triggers, but they aren't the easiest thing to work with.

If you want an open-source RDBMS that supports CHECK constraints, try PostgreSQL. It's actually a very good database.

Answer 2

I don't understand why nobody here has mentioned that VIEW WITH CHECK OPTION can be a good alternative to the CHECK CONSTRAINT in MySQL:

CREATE VIEW name_of_view AS SELECT * FROM your_table WHERE <condition> WITH [LOCAL | CASCADED] CHECK OPTION; 

There is a doc on the MySQL site: The View WITH CHECK OPTION Clause

DROP TABLE `Participants`;  CREATE TABLE `Participants` (   `meetid` int(11) NOT NULL,   `pid` varchar(15) NOT NULL,   `status` char(1) DEFAULT NULL check (status IN ('a','d','u')),   PRIMARY KEY (`meetid`,`pid`) );  -- should work INSERT INTO `Participants` VALUES (1,1,'a'); -- should fail but doesn't because table check is not implemented in MySQL INSERT INTO `Participants` VALUES (2,1,'x');  DROP VIEW vParticipants; CREATE VIEW vParticipants AS    SELECT * FROM Participants WHERE status IN ('a','d','u')   WITH CHECK OPTION;  -- should work INSERT INTO vParticipants VALUES (3,1,'a'); -- will fail because view uses a WITH CHECK OPTION INSERT INTO vParticipants VALUES (4,1,'x'); 

P.S.: Keep in mind that your view should be updatable! See MySQL Updatable Views (thanks Romeo Sierra for clarification in comments).