mysql添加check约束,如何在MySQL表上添加自定义CHECK约束？

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?

解决方案

As I explained in this article, starting with version 8.0.16, MySQL has added support for CHECK constraints:

ALTER TABLE topic

ADD CONSTRAINT post_content_check

CHECK (

CASE

WHEN DTYPE = 'Post'

THEN

CASE

WHEN content IS NOT NULL

THEN 1

ELSE 0

END

ELSE 1

END = 1

);

ALTER TABLE topic

ADD CONSTRAINT announcement_validUntil_check

CHECK (

CASE

WHEN DTYPE = 'Announcement'

THEN

CASE

WHEN validUntil IS NOT NULL

THEN 1

ELSE 0

END

ELSE 1

END = 1

);

Previously, this was only available using BEFORE INSERT and BEFORE UPDATE triggers:

CREATE

TRIGGER post_content_check BEFORE INSERT

ON topic

FOR EACH ROW

BEGIN

IF NEW.DTYPE = 'Post'

THEN

IF NEW.content IS NULL

THEN

signal sqlstate '45000'

set message_text = 'Post content cannot be NULL';

END IF;

END IF;

END;

CREATE

TRIGGER post_content_update_check BEFORE UPDATE

ON topic

FOR EACH ROW

BEGIN

IF NEW.DTYPE = 'Post'

THEN

IF NEW.content IS NULL

THEN

signal sqlstate '45000'

set message_text = 'Post content cannot be NULL';

END IF;

END IF;

END;

CREATE

TRIGGER announcement_validUntil_check BEFORE INSERT

ON topic

FOR EACH ROW

BEGIN

IF NEW.DTYPE = 'Announcement'

THEN

IF NEW.validUntil IS NULL

THEN

signal sqlstate '45000'

set message_text = 'Announcement validUntil cannot be NULL';

END IF;

END IF;

END;

CREATE

TRIGGER announcement_validUntil_update_check BEFORE UPDATE

ON topic

FOR EACH ROW

BEGIN

IF NEW.DTYPE = 'Announcement'

THEN

IF NEW.validUntil IS NULL

THEN

signal sqlstate '45000'

set message_text = 'Announcement validUntil cannot be NULL';

END IF;

END IF;

END;

For more details about emulating CHECK constraints using database triggers for MySQL versions prior to 8.0.16, then check out this article.