如何在MySQL表上添加自定义CHECK约束? [英] How do I add a custom CHECK constraint on a MySQL table?
问题描述
我在这张桌子上遇到麻烦了
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))
);
我想要一个外键约束,并且可行.然后,我还想向属性status
添加一个约束,以便它只能使用值'a','d'和'u'.我无法将字段设置为Enum
或set
.
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
.
谁能告诉我为什么这段代码在MySQL中不起作用?
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
);
以前,仅在使用BEFORE INSERT和BEFORE UPDATE触发器时才可用:
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;
有关使用8.0.16之前的MySQL版本的数据库触发器模拟CHECK约束的更多详细信息,请参阅 查看全文