June 21, 2004 at 6:26 am
Is there a recommended way of dealing with the problem caused by a table that has a nullable column with a UNIQUE constraint only allowing a single NULL for that column?
Thanks.
Paul
June 21, 2004 at 7:52 am
The only thing you could do would be to remove the unique constraint.
June 21, 2004 at 8:18 am
Ok thanks. I thought that was probably the case, but I wondered whether there was a workaround.
Paul
June 21, 2004 at 11:00 am
Yes...according to the BOL you can have a UNIQUE column as long as only one NULL exists.
You can also combine two columns to make one unique constraint.
-SQLBill
June 22, 2004 at 8:19 am
If you absolutely must implement your design, you could try using a trigger to enforce uniqueness for non-null values. See the basic example below. Note that in this example, if a multi-row INSERT statement contains even one duplicate then the whole INSERT is rolled back.
DROP TABLE nulltest
CREATE TABLE nulltest
(
id int NOT NULL PRIMARY KEY,
col varchar(10) NULL
)
CREATE INDEX ix_nulltest_col ON nulltest (col)
GO
CREATE TRIGGER tiu_nulltest ON nulltest FOR INSERT, UPDATE
AS
IF (SELECT COUNT(*)
FROM inserted i JOIN nulltest n ON i.col = n.col
WHERE i.col IS NOT NULL
AND i.id <> n.id
) > 0
BEGIN
RAISERROR('Duplicate data INSERTed or UPDATEd', 16, 1)
ROLLBACK TRANSACTION
END
GO
INSERT nulltest (id, col) VALUES (1, 'Row 1')
INSERT nulltest (id, col) VALUES (2, 'Row 2')
INSERT nulltest (id, col) VALUES (3, 'Row 3')
INSERT nulltest (id, col) VALUES (4, null)
INSERT nulltest (id, col) VALUES (5, null)
INSERT nulltest (id, col) VALUES (6, 'Row 6')
INSERT nulltest (id, col) VALUES (7, null)
GO
INSERT nulltest (id, col) VALUES (8, 'Row 6') -- this should fail
INSERT nulltest (id, col) VALUES (9, 'Row 6') -- this should fail
GO
INSERT nulltest (id, col) VALUES (10, 'Row 10')
GO
SELECT * FROM nulltest ORDER BY id
July 9, 2004 at 9:49 am
Another possibility may be to create a view which only brings back non-null records and then set a unique index on the view.
Tony
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply