Constraints在資料含有NULL時的隱憂

2018-01-26

NULL在資料庫中一直以來常會被拿出來警世，也看過一些書籍或聽一些演講都會提到盡量不要讓欄位是可以允許NULL的。

常被用來舉例的就是NULL無法比較(它既不是0也不是1，而是unknow)，因此往往範例都是趨向Where條件式會因為資料含有NULL而造成回傳的結果是錯誤的。
例如以下範例:

Use tempdb
GO
--建一資料表
Create Table tb1(id int identity Primary key,name varchar(10),sex char(1))
GO
--寫入資料
Insert Into tb1 values('Rock','M'),('Cary','F'),('John',NULL)
GO
--篩選出性別不是男生的資料
Select * From tb1 Where sex <> 'M'
GO

由結果來看(如下圖所示)，sex不是男性的資料只有一筆，而sex欄位是NULL的John沒有被篩選出來。

又或者是當NULL存在於NOT IN的Subquery裏也是一樣會發生悲劇

Use tempdb
GO
--建一資料表
Create Table tbtype(id int identity Primary key,typecode char(1))
Create Table tbObject(id int identity Primary key,ObjName varchar(50),typecode char(1))
GO
--寫入資料
Insert Into tbtype values('A'),('B'),(NULL)
Insert Into tbObject values('Apple','A'),('Asus','B'),('HTC','B'),('MI','C')
GO
--篩選出typecode不在tbtype資料表中的資料
Select * From tbObject Where typecode Not in(Select typecode From tbtype)
GO

如下圖紅色圈選處，我們發現tbObject中有一筆資料的typecode='C'，但是在我們測試中該筆資料並沒有被篩出來，反而回傳零筆資料回來。這就是當NULL存在NOT IN的Subquery時會發生的潛在問題。

然而以上這些問題都是大家經常拿來檢討該不該有NULL的理由，近日ROCK讀了一本電子書(Defensive Database Programming with SQL Server)發現NULL對於Constraints(條件約束)影響更是不容小歔。以下是簡易Demo Code環境建立。

Use Tempdb
GO
CREATE TABLE Boxes(id int identity PRIMARY KEY , Leng int ,Width int ,Height int) ;
GO
--第一個Check驗證 高<10 
ALTER TABLE Boxes ADD CONSTRAINT Boxes_Check1 CHECK(Height<10) ;
GO
--第二個Check驗證 長>寬>高
ALTER TABLE dbo.Boxes
ADD CONSTRAINT Boxes_Check2 CHECK(Height <= Width AND Width <= Leng) ;
GO

完成上述環境建立後，我們先驗證一下我們的Check Constraints是否可以正常運作。下圖中我們故意讓資料中高度等於10，這樣一來就會違背我們的Boxes_Check1條件而發生錯誤。

接下來我們故意讓寬度大於長度並寫入資料，如下圖所示會因為違背Boxes_Check2條件約束而發生錯誤。

以上兩個簡易測試可以知道目前的Check Constraints是正常運作的。

接下來我將含NULL的資料寫入資料表看看我們的Check Constrains是不是也擋得住。如下圖Insert語法中，我長度給8，寬度是NULL然後高度是9，高度大於長度顯然違反Boxes_Check2條件約束。然而該筆資料居然可以順利寫入Table而沒被Check Constraints給攔住。

下圖中再輸入資料的高度欄位上我故意給NULL值，我們也可以從結果發現該筆資料也避過Boxes_Check1條件約束而順利寫入到資料表中。

除了剛剛Demo的Constraints會有遇到NULL就無用武之地外，連確保資料完整性的Foreign Key也會因為關聯欄位值含NULL而破功，以下是簡易的Demo。

Use Tempdb
GO
Create Table tbFk1(id1 int not null,id2 int not null,name varchar(10),primary key(id1,id2));
GO
Create Table tbFk2(id int identity Primary Key,id1 int,id2 int,Phone varchar(10));
GO
--為tbFk1及tbFk2建立Foreign Key的關聯
Alter Table tbFk2 Add Constraint FK_tbFk1_tbFk2 Foreign Key(id1,id2)
References tbFK1(id1,id2);
GO

完成上面的環境建置後，我們來測試一下Foreign Key是否可以正常發會功效。如下圖所示我們在父資料表(tbfk1)中寫入一筆id1=1及id2=1的資料，接著我們也可以順利在子資料表(tbfk2)中寫入一筆id1=1及id2=1的資料。