正如我在本文中所解释的,从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'