CHECK约束中的NULL条件

今天同事在添加约束的时候，意外的添加了一个NULL值约束，导致了约束的失效。

看一个简单的例子：

SQL> CREATE TABLE T
2 (
3 ID NUMBER,
4 FLAG CHAR(1) NOT NULL,
5 CONSTRAINT CK_FLAG CHECK (FLAG IN ('A'))
6 );

表已创建。

SQL> INSERT INTO T (ID, FLAG) VALUES (1, 'A');

已创建 1 行。

SQL> INSERT INTO T (ID, FLAG) VALUES (1, 'B');
INSERT INTO T (ID, FLAG) VALUES (1, 'B')
*第 1 行出现错误:
ORA-02290: 违反检查约束条件 (YANGTK.CK_FLAG)

SQL> INSERT INTO T (ID, FLAG) VALUES (1, NULL);
INSERT INTO T (ID, FLAG) VALUES (1, NULL)
*第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("YANGTK"."T"."FLAG")

由于FLAG列上的CHECK约束和NOT NULL约束，会导致除了’A’以外的数据都无法插入。但是如果在约束上添加一个NULL值：

SQL> ALTER TABLE T DROP CONSTRAINT CK_FLAG;

表已更改。

SQL> ALTER TABLE T ADD CONSTRAINT CK_FLAG CHECK (FLAG IN ('A', ''));

表已更改。

Oracle不会检查同一个列上的多个CHECK约束是否会发生冲突，因此上面添加FLAG IN NULL这个约束并不会报错。

但是这种添加的NULL，会导致这个CHECK约束失效：

SQL> INSERT INTO T (ID, FLAG) VALUES (1, 'A');

已创建 1 行。

SQL> INSERT INTO T (ID, FLAG) VALUES (1, 'B');

已创建 1 行。

SQL> INSERT INTO T (ID, FLAG) VALUES (1, 'C');

已创建 1 行。

SQL> INSERT INTO T (ID, FLAG) VALUES (1, NULL);
INSERT INTO T (ID, FLAG) VALUES (1, NULL)
*第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("YANGTK"."T"."FLAG")

现在CHECK约束CK_FLAG已经完全失去作用，对于FLAG列除了NULL值外，其他任何值都可以插入。

CHECK的约束保证插入的数据不会导致CHECK检查的结果为假，CHECK结果为真和为空的记录都可以插入。

对于FLAG IN (‘A’, ‘’)的约束而言，任何记录的CHECK结果不是TRUE就是NULL。因此任何记录都是可以插入的。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/4227/viewspace-69341/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/4227/viewspace-69341/