Database 如何在 MySQL 表上添加自定义 CHECK 约束

发布于07月03日

我用这张桌子有点问题

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.

有人能告诉我为什么这段代码不能在MySQL中运行吗？

推荐答案

从8.0.16版开始，MySQL添加了对CHECK约束的支持:

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

以前，这只能使用插入前和更新前触发器:

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;