unique constraint on col & allow more than 1 null
-
I created a unique constraint on a column called Col1 in table Test1 to enforce uniqueness with the exception of NULL. As soon as I add data and try to enter a second record with NULL in Col1, I receive a message that says I've violated the unique constraint. What is the best way to get around this problem? I created trigger on insert/update to check it column violate uniqueness constraint. Is there any other better way to enfore uniqueness on null columns
-
Select columns that are defined as NOT NULL when you choose columns for a unique index or unique constraint. If you have a need to have a unique constraint on a column that you need to remain nullable, it may be a sign that you need to rethink your design.
A.J.
DBA with an attitude -
satnam,
unfortunately not. this is where (IMHO) Microsoft doesn't adhere to the ansi standard which states "no two NULLs are equal" which you would think should allow you to permit multiple NULL columns in a MSSQL unique constraint. This is not the case however. I think the CREATE INDEX doco in BOL states this somewhere.
-
Look over this example. This allows multiple null values for the varchar column nulltest, but each non-null must be unique.
CREATE TABLE NullPKTest
(
nid int PRIMARY KEY
, nulltest varchar(10) NULL -- unique non-null values enforced via triggers
)
GO
CREATE TRIGGER triu_NullPkTest ON NullPKTest
FOR INSERT, UPDATE
AS
BEGIN
IF EXISTS (SELECT i.nulltest, Count(*) AS ntCount
FROM NullPKTest n
JOIN inserted i
ON n.nulltest = i.nulltest
GROUP BY i.nulltest
HAVING Count(*) > 1)
BEGIN
ROLLBACK TRANSACTION
RAISERROR ('Insert/Update failed because at least one duplicate value for column "nulltest" was encountered.' , 10, 1)
END
END
GO
----------------------------------------------------------------------------
--
-- TEST Data
--
----------------------------------------------------------------------------
INSERT NullPKTest (nid, nulltest) VALUES (1, 'FIRST')
INSERT NullPKTest (nid, nulltest) VALUES (2, 'SECOND')
INSERT NullPKTest (nid, nulltest) VALUES (3, 'THIRD')
SELECT * FROM NullPKTest
GO
INSERT NullPKTest (nid, nulltest) VALUES (4, 'THIRD')
SELECT * FROM NullPKTest
GO
UPDATE NullPKTest SET nulltest = 'FIRST' WHERE nid = 2
SELECT * FROM NullPKTest
GO
INSERT NullPKTest (nid) VALUES (5)
SELECT * FROM NullPKTest
INSERT NullPKTest (nid) VALUES (6)
INSERT NullPKTest (nid) VALUES (7)
INSERT NullPKTest (nid) VALUES (8)
SELECT * FROM NullPKTest
GO
PRINT 'Changing NULL for nid=8 to "FIRST"'
UPDATE NullPKTest SET nulltest = 'FIRST' WHERE nid = 8
GO
SELECT * FROM NullPKTest
-
-- My Solution: Use an indexed view: (Borrowing Names from mkeast
) CREATE TABLE dbo.NullPKTest
(
nid int PRIMARY KEY
, nulltest varchar(10) NULL -- unique non-null values enforced via triggers
)
GO
CREATE VIEW dbo.wvNullPKTest WITH SCHEMABINDING
AS
SELECT NullPKTest.nulltest
FROM dbo.NullPKTest
WHERE nulltest IS NOT NULL
GO
CREATE UNIQUE CLUSTERED INDEX IX_CL_wvNullPKTest on wvNullPKTest(nulltest)
GO
-------- That's it!
---------
--Proof: |
-- V
--
INSERT NullPKTest (nid, nulltest) VALUES (1, 'FIRST')
INSERT NullPKTest (nid, nulltest) VALUES (2, 'SECOND')
INSERT NullPKTest (nid, nulltest) VALUES (3, 'THIRD')
INSERT NullPKTest (nid, nulltest) VALUES (4, 'THIRD')
---------------------------------------------------------
-- Server: Msg 2601, Level 14, State 3, Line 1
-- Cannot insert duplicate key row in object 'wvNullPKTest' with unique index 'IX_CL_wvNullPKTest'.
-- The statement has been terminated.
------------------------------------------------------------------------
INSERT NullPKTest (nid) VALUES (5)
INSERT NullPKTest (nid) VALUES (6)
INSERT NullPKTest (nid) VALUES (7)
INSERT NullPKTest (nid) VALUES (8)
SELECT * FROM NullPKTest
UPDATE NullPKTest SET nulltest = 'FIRST' WHERE nid = 8
-------------------------------------------------------
-- Server: Msg 2601, Level 14, State 3, Line 1
-- Cannot insert duplicate key row in object 'wvNullPKTest' with unique index 'IX_CL_wvNullPKTest'.
-- The statement has been terminated.
------------------------------------------------------------------------
Cheers,
* Noel
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply