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。因此任何記錄都是可以插入的。