I enjoy reading questions from blog readers and answering them. One of the another SQL enthusiastic is Imran who also regulalry answer questions of users on this community blog. Recently he has answered in detail about when to use Unique Index and when to use Unique Constraint.
Cristiano asked following questions :
i need to know how work when there is a situation that there is a Unique Key and this field “alow null”, but when i am going to create a Unique Key the SQLSERVER saw that there were values duplicated and the values are “nulls”. How do i sove this problem?
Imran Mohammed answered in detail :
@Cristiano,
As you may be aware, Unique Key can allow only one null. Meaning only one null can be accepted not more than that. If you have more than one null on any column, you cannot make that column/field as unique key.
I am sure there must be another way of doing this, I would do something like this,
1. Since there is no way you can make that field a unique key, how about making a composite key.
a) Combine this field with any other unique column/field and make a composite unique key. If any other unique key already exists then add this field to the previous unique key.
b) if you do not have any unique key ( I am sure you do), but if you dont, then you can do this always, using Enterprise manager or SSMS object Explorer, create a new Identity column in the table and then create a unique composite key on these two field. ( You can create an identity column if you have data in the table, you DONT have to drop and recreate the table, using EM or SSMS you can always create a identity column in the table).
If you consider case a), then make sure there are no duplicates for the combination of new field and the field you chose for unique key.
If you create a composite unique key on cola and colb combined then you can have more than one null in cola and in colb, but again the combination of cola and colb should be unique.
Once you start making a composite unique key… this concept will be more easier.
Try doing this, either in Enterprise Manager or SSMS ( Object Explorer) much easier in interface than executing scripts.
Hope this helps.
Imran.
Reference : Pinal Dave (http://www.SQLAuthority.com)
pinaldave
I don’t see how this answers the original users’ question. He wants to have ColA where the DB will enforce a constraint on the column so that it can have unlimited null values, but all non-null values will be unique. The suggested solution is to join ColA with ColB in a composite unique key, where ColB itself is a unique column.
However, I don’t think that this will solve the problem. True, it will allow multiple nulls in ColA (since any combo of null + ColB will be unique). But it will also allow multiple non-unique, non-null values in ColA (since ColB is a unique column itself, all of the rows for ColA could equal the same value while still observing the unique constraint). Am I missing something here?
Just like mr. Ellis, I fail to see how any of the two proposed solutions actually solve the original problem.
If you truly, desperately must have a column of unique non-null values but still allow many null values, one cheesy way would be to add a computed column, defined to contain the same non-null data, and some other, unique, data when the original column is null. E.g. like this:
ALTER TABLE SomeTable
ADD DummyColumn AS CASE
WHEN UniqueColumn IS NULL THEN SomeReallyUniqueColumnOrCombinationsThereofThatAreGuaranteedNeverToOccurInTheOtherUniqueColumn
ELSE UniqueColumn
END PERSISTED
The problem of course is to find a set of deterministic data that is guaranteed to be unique for each row and at the same time guaranteed not to be present in the original column.
Hello all,
My first statement says… “I am sure there must be another way of doing this, I would do something like this,”
The question was: There is a Unique Key and this field “alow null”, but when i am going to create a Unique Key the SQLSERVER saw that there were values duplicated and the values are “nulls”.
What I understood from this question was, the column we are talking about itself is unique except the fact that it has more than one null values. Because of this we cannot create a unique constraint on that column and I believe there is no way to create a unique constraint on a column which has more than one null unless you make a composite key, to do this I suggested two ways,
1. Combine this with any other unique column. Here when I say these, I am talking for only null records, not for non null records, that wasn;t even discussed in the question. The only concern of that person was how to solve null values and still make this column unique. no where he said that he want to make this column who unique…. his concern was only for null records.
2. I suggested that he create an identity column, in case if he dont have a uniwue column. now create a composite unique constraint on previous column and newly created identity column, again the concern here was only for null values.
I understand your concern, you are asking if I make a unique constraint, on cola and colb, colb is identity, then I can have duplicate records in cola, but the combination of cola and colb will still be unique. You are correct, but again like I said, I answered this question thinking that the problem is only with null records and column by default have only unique non null records.
I am always ready to correct myself. Thanks for bringing up this fact.
Thanks,
Imran.
@Yaakov Ellis, @Bengan and @Imran Mohammed,
I wish all of blog reader read this particular thread. This is very interesting and in depth discussion of the subject. I really like the way all of you are participating to help community.
@Imran,
I really like your additional explanation and politeness towards other experts.
Regards,
Pinal Dave ( http://www.SQLAuthority.com )
Sorry Imran, but I still don’t see how your suggested solution enables the original poster to do what they want: have a unique constraint on one column that allows multiple null values, but ensures that all non-null values are unique.
SQL Server 2008 supports filtered indexes (WHERE clause for indexes). Filtered indexes allow you to enforce unique constraints on nullable columns.
I find Bengan solution to be perfect.
1. create a computed column.
2. Pass the ‘unique column’ value when it is not null
3. When it is null copy the Primary key’s value into the computed column.
4. Set the unique constraint on computed column
Nagaraj - this however has the same problem as originally mentioned by Bengan: “The problem of course is to find a set of deterministic data that is guaranteed to be unique for each row and at the same time guaranteed not to be present in the original column.“.
I think that the solution to this would be to use GUIDs (Type: UniqueIdentifier) as the datatype for the Primary Key row. This would ensure that no PK value would ever equal the unique column value. You would still have to put logic into your application to determine whether or not a real value or GUID-in-place-of-null is being used.
I would prefer an indexed view to enforce uniqueness on nullable columns (not in SQL Server 2008):
create view xy
with schemabinding
as
select
uniquevalue
from dbo.table
where
uniquevalue is not NULL
create unique clustered index cui on dbo.table(uniquevalue)
IMHO the only reasonable solution
Regards
Christop Ingenhaag
I’m running into this exact problem. Thanks for the discussion. We have thousands of suppliers and not all of them use DUNS number, but when they do we keep track of it in our vendor database at Lincoln Financial Group. We have a different internalsupplierid which is unique and is the primary key. I can handle unique checking on DUNS in the application (ugh, if I have to), but it sure would be nice if on the database SQL server made it easier to add a “no duplicates unless null” index option.
Nice topic and discussion. Thanks to all. I have a question. I have few join tables in database such as
user_role
user_role_ID PK
user_id FK
role_id FK
where a combination of user_id and role_id is unique. Is it a good idea to build a composite unique index on those kind of join tables or it is not necessary.
Thanks.