Na ez egy igazán egy érdekes probléma: képzeljük el azt az esetet, amikor egy tábla oszlopa engedi a NULL értékeket, de azt is akarjuk, hogy minden egyéb érték egyedi legyen, pl.:
USE tempdb;
GO
CREATE TABLE [dbo].[CheckConstraintTest]
(
[id] int NULL,
[name] char(1) NULL
);
Első körben a UNIQUE constraint jutott eszembe, de ez nem lesz jó. Miért is? Persze, a UNIQUE constraint nem enged egynél több NULL értéket sem, ahogy ez az alábbi példán is látszik:
ALTER TABLE [dbo].[CheckConstraintTest]
ADD CONSTRAINT [UQ_ID] UNIQUE ([id])
Majd próbáljunk meg adatokat beletenni a táblába:
INSERT INTO [dbo].[CheckConstraintTest] VALUES (NULL, 'a');
INSERT INTO [dbo].[CheckConstraintTest] VALUES (NULL, 'a');
Persze a második insert nem fog megtörténni, hanem kapunk egy szép hibaüzenetet:
(1 row(s) affected)
Msg 2627, Level 14, State 1, Line 2
Violation of UNIQUE KEY constraint 'UQ_ID'. Cannot insert duplicate key in object 'dbo.CheckConstraintTest'. The duplicate key value is (<NULL>).
The statement has been terminated.
Na jó, de akkor most mit lehet tenni? A megoldás igen egyszerű (és ez nem az egyetlen megoldás):
A táblára nem kell UNIQUE constraint, hanem egy filtered index-el oldom meg. Az alábbi kóddal módosítom a táblát, majd ismét megpróbálok adatokat beletenni a táblába:
ALTER TABLE [dbo].[CheckConstraintTest]
DROP CONSTRAINT [UQ_ID];
CREATE UNIQUE NONCLUSTERED INDEX [NCU_ID] ON [dbo].[CheckConstraintTest] ([id])
WHERE [id] IS NOT NULL;
INSERT INTO [dbo].[CheckConstraintTest] VALUES (NULL, 'a');
INSERT INTO [dbo].[CheckConstraintTest] VALUES (NULL, 'a');
Így már sikerült több NULL értéket bevinni, és az ID oszlopomban is garantált, hogy nem ismétlődik ugyan az az érték.