I enjoy reading questions from blog readers and answering them. One of the another SQL enthusiastic is Imran who also regularly 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 (https://blog.sqlauthority.com)
