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)