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.