Tạo Ràng Buộc Duy Nhất Bằng Filtered Index trong SQL server 2008

Ràng buộc duy nhất (unique constraint) được xây dựng trong SQL Server hơi khác so với định nghĩa trong lý thuyết CSDL. Theo định nghĩa (và cũng được qui định trong chuẩn ANSI) thì ràng buộc duy nhất yêu cầu các giá trị phải khác nhau trừ khi chúng là NULL.

Ví dụ nếu một bảng có 1000 bản ghi, và trường C có 800 bản ghi có các giá trị khác nhau, còn ở 200 bản ghi còn lại đều là NULL, thì vẫn thỏa mãn ràng buộc duy nhất.
SQL Server cũng yêu cầu các giá trị phải khác nhau nhưng đồng thời chỉ cho phép tối đa một giá trị NULL. Nói cách khác là nó coi NULL như một giá trị thông thường và hai bản ghi cùng NULL sẽ bị coi là trùng nhau:

CREATE TABLE dbo.CaSy(
CaSy_ID INT IDENTITY PRIMARY KEY,
Ten NVARCHAR(100),
Email VARCHAR(100),
CONSTRAINT UC_CaSy UNIQUE (Email)
)
GO
INSERT INTO dbo.CaSy VALUES(N'Thái Thanh','thanh@thaithanh.com')
INSERT INTO dbo.CaSy VALUES(N'Khánh Ly',NULL)
INSERT INTO dbo.CaSy VALUES(N'Trần Thái Hòa',NULL)

Lệnh tạo bảng và hai lệnh INSERT đầu diễn ra bình thường, nhưng lệnh INSERT thứ ba thì gây ra lỗi sau:

Msg 2627, Level 14, State 2, Line 1
Violation of UNIQUE KEY constraint 'UC_CaSy'. Cannot insert duplicate key in object 'CaSy'.
The statement has been terminated.

Như vậy là SQL Server từ chối thêm một giá trị NULL thứ hai vào cột “Email” vì nó có ràng buộc duy nhất. Điều này thật khó thuyết phục, vì NULL không phải là một giá trị để có thể so sánh được (ví dụ mệnh đề WHERE colname = @Val khi @Val là NULL sẽ không trả về bản ghi nào). Tôi cho rằng đây thuộc về lỗi nhiều hơn là một đặc tính của SQL Server.

Vậy để tạo một ràng buộc duy nhất với cách ứng xử giống như định nghĩa thông thường thì phải làm thế nào? Nếu bạn dùng bản 2005 trở về trước, có một vài cách có thể làm nhưng đều rườm rà:

- Dùng trigger: mỗi khi có INSERT hoặc UPDATE thì kiểm tra xem giá trị mới đã có trong bảng chưa, và chỉ cho phép thực hiện nếu chưa xuất hiện.

- Tạo một ràng buộc CHECK dbo.CountCol(Email) < 2, trong đó dbo.CountCol(@Email) là hàm UDF (bạn cần phải viết) trả về số lần xuất hiện của giá trị @Email trong cột Email.

- Cách làm “chuẩn” nhất có lẽ là tạo một indexed view từ trường Email với điều kiện WHERE Email IS NOT NULL. Tuy nhiên vẫn rườm rà, khi cần tìm kiếm trên trường Email bạn lại phải nhớ tìm trên view này, hoặc phải tạo một index cho trường Email trên bảng CaSy.

Từ bản SQL Server 2008 bạn có thể dùng tính năng filtered index để áp đặt ràng buộc này. Filtered index về bản chất là một non-clustered index nhưng cho phép chọn ra các bản ghi cần được index qua mệnh đề WHERE. Do vậy bạn có thể tạo một index như sau:

CREATE UNIQUE INDEX idx_CaSyEmail ON dbo.CaSy(Email)
WHERE Email IS NOT NULL

Lệnh trên tạo một unique index trên trường Email, nhưng chỉ các bản ghi có Email không NULL mới được index. Vì thế các Email NULL có thể được thêm vào bảng thoải mái, nhưng Email không NULL nếu bị lặp khi thêm vào sẽ bị index này chặn lại, do dó tính duy nhất được đảm bảo.