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

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).


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.


Beside triggers, for simple constraints like the one you have:

CONSTRAINT `participants_ibfk_2` 
  CHECK status IN ('a','d','u')

you could use a Foreign Key from status to a Reference table (ParticipantStatus with 3 rows: 'a','d','u' ):

CONSTRAINT ParticipantStatus_Participant_fk
  FOREIGN KEY (status)
    REFERENCES ParticipantStatus(status)