MySQL使用CHECK约束限制特定值时未限制值插入报错如何解决

问题原因 #

你当前定义的CHECK约束逻辑覆盖范围不符合需求，约束要求每一行数据必须满足(name='one' AND year>1990) OR (name='two' AND year<1990)，也就是说只有name为one或two、且对应年份符合要求的数据才会被判定合法。当插入name为three的数据时，两个判断分支都不满足，就会触发约束报错。

解决方法 #

在CHECK约束中补充第三种合法情况：当name既不是one也不是two时，直接判定为合法，不对年份做限制。

新表创建写法 #

如果是新建表，直接使用修改后的约束即可：

CREATE TABLE Movie(
name VARCHAR(30),
year int,
CHECK ((name='one' AND year>1990) OR (name='two' AND year<1990) OR name NOT IN ('one', 'two'))
);

已有表修改写法 #

如果表已经创建完成，不想清空重建，可以先删除原有约束，再添加修改后的约束：

-- 删除原有约束，约束名和报错提示里的一致即可，这里示例为报错里的movie_chk_1
ALTER TABLE Movie DROP CHECK movie_chk_1;
-- 添加新的符合需求的约束
ALTER TABLE Movie ADD CHECK ((name='one' AND year>1990) OR (name='two' AND year<1990) OR name NOT IN ('one', 'two'));

修改后可以满足所有需求场景：

• name为one时，year必须大于1990才可插入
• name为two时，year必须小于1990才可插入
• name为其他任意值时，year没有限制，可正常插入

内容的提问来源于stack exchange，提问作者user10665914