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

小编典典

正如我在本文中所解释的，从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约束的更多详细信息，请参阅本文。

2020-05-17