SQL SERVER – Explanation about Usage of Unique Index and Unique Constraint

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 (http://blog.SQLAuthority.com)

About these ads

16 thoughts on “SQL SERVER – Explanation about Usage of Unique Index and Unique Constraint

  1. 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?

  2. 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.

  3. 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.

  4. @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 )

  5. 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.

  6. SQL Server 2008 supports filtered indexes (WHERE clause for indexes). Filtered indexes allow you to enforce unique constraints on nullable columns.

  7. 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

  8. 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.

  9. 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

  10. 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.

  11. 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.

  12. SET NUMERIC_ROUNDABORT OFF;
    SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
    GO
    CREATE TABLE t1 (id INT, title VARCHAR(20))
    GO

    — optional instead of trigger to disable the insert directly into the table
    CREATE TRIGGER trg_t1_DisableInsert ON t1
    INSTEAD OF INSERT
    AS
    BEGIN
    — use 18 to stop further processing
    RAISERROR (40000, 18, 1, ‘Use view dbo.v1 to insert data into table’)
    END
    GO

    CREATE VIEW v1
    WITH SCHEMABINDING
    AS
    SELECT id, title
    FROM dbo.t1
    WHERE id IS NOT NULL
    GO
    –Create an index on the view.
    CREATE UNIQUE CLUSTERED INDEX CI_V1_ID
    ON dbo.v1 (id);
    GO

    INSERT INTO v1 (id, title)
    SELECT 1, ‘title 1′ UNION ALL
    SELECT 2, ‘title 2′ UNION ALL
    SELECT 3, ‘title 3′ UNION ALL
    SELECT 4, ‘title 4′ UNION ALL
    SELECT null, ‘title null’

    — this fails because it clashes with the clustered index on the view
    INSERT INTO v1 (id, title)
    SELECT 1, ‘title 5′

    — this is ok because it doesn’t clash.
    INSERT INTO v1 (id, title)
    SELECT NULL, ‘title 5′

    — we see that the table can contain only unique values and more than one null
    SELECT * FROM t1
    SELECT * FROM v1

    GO
    DROP VIEW dbo.v1
    GO
    DROP TABLE dbo.t1

  13. If I already have unique non cluster index made up of composite keys and if I want to add a column having multiple null values in existing index, how can I do it ?
    If I try to add this column in existing index, it gives me following message:

    Drop failed for Index ‘IX_TABLE_A’. (Microsoft.SqlServer.Smo)

    ——————————
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ——————————

    An explicit DROP INDEX is not allowed on index ‘dbo.TABLE_A.IX_TABLE_A’. It is being used for FOREIGN KEY constraint enforcement. (Microsoft SQL Server, Error: 3723)

  14. Its not possible to create unique index with more than one null value in mssql, only in oracle.
    The best solution i found:
    http://sqlservercodebook.blogspot.com/2008/04/multiple-null-values-in-unique-index-in.html

    About the error:
    An explicit DROP INDEX is not allowed on index…

    Dont use the UI editor
    Use query analyser to drop the constraint first:
    ALTER TABLE table_name DROP CONSTRAINT FK_constraint_name
    and then recreate the constraint(then you can do in the UI editor)

  15. Actually Microsoft SQL Server’s implementation of the unique constraint does not adhere to the SQL-standard. So the fact that other databases (like Oracle, MySQL or Microsoft Access) allow multiple NULLs in a UNIQUE constraint/index shouldn’t be surprising.

    With SQL Server you can however work around the problem with indexed views (as of SQL Server 2000) or filtered indexes (as of SQL Server 2008).

    For more details on the SQL Server bug, see https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=299229

  16. Pingback: SQL SERVER – Weekly Series – Memory Lane – #045 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s