如何在MySQL表上添加自定义CHECK约束？?mysql
我在这张桌子上遇到麻烦
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 ); 以前，仅在使用BEFORE INSERT和BEFORE UPDATE触发器时才可用：
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约束的更多详细信息，请参阅本文。来源：stack overflow
2020-05-17 10:35:59赞同 展开评论