UNIQUE constraint és több NULL érték

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.

Comments (3) -

Zoltán Horváth 11/2/2011 1:12:49 PM

Első mondatba beszúrnám, hogy "egyéb": "minden _egyéb_ érték egyedi legyen".

Zoltán Horváth 11/2/2011 1:13:03 PM

Milyen avatárt kaptam Smile

köszi Zoli, javítottam Smile

Add comment