Unique constraints and nulls
-
I have a table with a nullable column, which must be unique for all of the non-null entries, but it is allowed to have many nulls.
Ideally I would like to enforce this using a unique constraint rather than a trigger. In BOL there are several contradictory entries about this - some say that a unique constraint will only enforce uniqueness for non-null values, whereas others say that only one null value will be allowed.
When I try it out, it seems that it will only allow one null, so it looks as if I will just have to go with the trigger.
Am I missing something?
-
Here's a solution that doesn't need a trigger... but I'd test it against the trigger to see which one is faster :
Create an indexed view on that table :
CREATE VIEW dbo.vwTableNameEnforceUniqueColNameHere
WITH SCHEMABINDING
AS
Select UniqueNullableColumn from dbo.YourTable where UniqueNullableColumn IS NOT NULL
GO
Then add the column as the clustered primary key of the view.
The problem with this solution is that you can't costumize the error message like you could in a trigger.
-
Unfortunately SQL Server does not implement unique constraints correctly as specified in the ANSI standard. Any entries in BOL that say "that a unique constraint will only enforce uniqueness for non-null values" is not correct and should be sent as feedback to MS to be corrected. That is the way it should work according to the ANSI standard, but as you noted clearly not how it does work in SQL Server.
This KB article discusses some possible solutions for this. I prefer the second solution (using a computed column) because it enables me to create a logical constraint that users can be aware of.
Also see these two old threads for more discussion regarding this:
-
Thank you. This has been very helpful.
-
I am having the same problem in an Inventory DB. I use SQL 2000 as the backend with an MS Access frontend on the users desktops that access this data. Problem is: Workstations have an Unique name, but not all are out in the field, so those are NULL. With MS Access, it's cake to index (No Dupes) required = No and allow Zero length. Grrrrrrrrrr Now today with SQL 2000 I am hit with this problem and now have spent about 6 hours trying work arounds!
The Question was asked to me. Is this going to be a "new" enhancemnt in sql 2005? Maybe it will go back to allowing Nulls, but also checking the existing data for unique?
Thanks,
Chris
-
The Question was asked to me. Is this going to be a "new" enhancemnt in sql 2005? Maybe it will go back to allowing Nulls, but also checking the existing data for unique?
No, SQL Server 2005 works like SQL Server 2000 in this aspect. For workarounds, see the solution posted by Remi above or the KB article I referenced.
-
Remi = rgR'us
. -
I'm just trying to force you into changing your nick back.
-
Not gonna happen
. -
i was wondering how this new guy was beeting Remi to the punch/post; must be that Remi-bot we heard so much about.
Seriosuly, thanks for all your contributions Remi/rqR'us
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
There's an actual meaning to that name... care to take a guess?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply