none
Unique Constraint and null?

    Question

  •  

    Can I create a unique constraint on a column that can contain null values?

    I need the control of the non null values (must be unique)...null is Ok if there's more than one.

    I tried creating a unique constraint but i'm getting the error (duplicate keys <null>)

    Wednesday, February 27, 2008 3:31 PM

Answers

  • There was a thread on how to deal with this a few days ago...can't find it for the life of me.  Maybe one of the moderators knows where it's hiding.  One of the suggestions from that thread come to mind:

      - Create a view where that column IS NOT NULL (SELECT * FROM MyTable WHERE MyColumn IS NOT NULL), and create a unique index on that view.

     

    Just make sure you're aware of the caveats of indexed views (see MSFT technet article)
    Wednesday, February 27, 2008 3:42 PM
  • When it comes to the UNIQUE constraint the multiple NULL values are considered as duplicate (ie, NULL=NULL => TRUE), even  if you change the DB level settings (using EXEC sp_dboption 'dbname','ANSI NULLS', 'ON'), this behavior won’t change.

     

    Test the following code,

     

    Code Snippet

    CREATE TABLE Numbers

    (

           Number int primary key,

           Word  varchar(20) null UNIQUE

    )

     

    Go

    Insert Into Numbers values(1,'One') -- Success

    Insert Into Numbers values(2,'Two') -- Success

    Insert Into Numbers values(3,'Two') -- Error is expected bcs Two is duplicated

    Insert Into Numbers values(6,NULL) -- Success

    Insert Into Numbers values(7,NULL) -- Suprise, the error for duplicate

     

     

     

     

    So, In SQL Server if we add the UNIQUE constraint we can’t add more than one NULL values in the table.

     

     

    Work Around / Solution

    OK. How to overcome this issue - Simple, we need to cheat the index – my values are always non-null. Hide the NULL values from the UNIQUE INDEX.

     

    Steps,

    1. Create table without UNIQUE constraint

    2. Create a VIEW & filter only the NON-NULL values

    3. Now create the UNIQUE constraint against the VIEW (INDEXED VIEW)

     

    That’s it..! As much possibleJ we Hacked/Cheated the SQL Server..!

     

    Sample Code,

     

    NOTE: Without this SET statements you can’t able to create the INDEXED VIEWS.

                   

     

    Code Snippet

    SET NUMERIC_ROUNDABORT OFF;

    SET ANSI_PADDING, ANSI_WARNINGS

    SET CONCAT_NULL_YIELDS_NULL, ARITHABORT ON;

    SET QUOTED_IDENTIFIER, ANSI_NULLS ON;

     

    CREATE TABLE Numbers

    (

        Number int primary key,

        Word  varchar(20) null

    )

     

    Go

    Create View view_Numbers With Schemabinding

    As

    Select

         Word

    from

        dbo.Numbers

    Where

        Word is not null

    Go

     

    Create Unique Clustered Index index_unique_Numbers

    on view_Numbers (Word)

     

    Go

     

    --Now you can forget the Index & View ::

    --Don't worry about these objects any more

    --Come back to the tables

     

    Insert Into Numbers values(1,'One') -- Success

    Insert Into Numbers values(2,'Two') -- Success

    Insert Into Numbers values(3,'Two') -- Error ‘Two’ is duplicated

     

    Insert Into Numbers values(6,NULL) -- Success

    Insert Into Numbers values(7,NULL) -- Success

     

     

     

    Wednesday, February 27, 2008 4:38 PM
  •  Sara4 wrote:

    So, what are my options? No easy solutions? There's no way to tell a constraint to ignore the nulls?

    Now I see you added the BOL quote...

     

     

     

     

    A couple of them are:

     

    1 - Use a calculated column (Steve Kass, who came with this idea, called it nullbuster) to take the value from a unique column when the value of the column in question is null.

     

    Example:

     

    Code Snippet

    create table dbo.t1 (
    c1 int not null identity primary key,
    c2 int null,
    c3 as case when c2 is null then c1 else 0 end,
    constraint uq_t1_c2_c3 unique (c2, c3)
    )
    go
    insert into dbo.t1(c2) values(NULL)
    insert into dbo.t1(c2) values(NULL)
    insert into dbo.t1(c2) values(NULL)
    go
    insert into dbo.t1(c2) values(1)
    insert into dbo.t1(c2) values(2)
    go
    -- will fail here
    insert into dbo.t1(c2) values(1)
    go
    drop table dbo.t1
    go


    2 - Use an indexed view, as TheSQL suggested.

    In SQL Server 2008, we will have a new feature named filtered indexes and you will be able to do something like:

     

    create t1_c2_u_nc_ix
    on dbo.t1(c2)
    where c2 is not null
    go

     

    AMB

    Wednesday, February 27, 2008 4:46 PM

All replies

  • A Column bound Unique Constraint can only contain 1 of each value, including NULL.

     

    Direct from BOL:

     

    Handling NULL Values

     

    For indexing purposes, NULL values compare as equal. Therefore, you cannot create a unique index, or UNIQUE constraint, if the key values are NULL in more than one row. Select columns that are defined as NOT NULL when you choose columns for a unique index or unique constraint.

     

    Wednesday, February 27, 2008 3:37 PM
  • So, what are my options? No easy solutions? There's no way to tell a constraint to ignore the nulls?

    Now I see you added the BOL quote...

    Wednesday, February 27, 2008 3:42 PM
  • There was a thread on how to deal with this a few days ago...can't find it for the life of me.  Maybe one of the moderators knows where it's hiding.  One of the suggestions from that thread come to mind:

      - Create a view where that column IS NOT NULL (SELECT * FROM MyTable WHERE MyColumn IS NOT NULL), and create a unique index on that view.

     

    Just make sure you're aware of the caveats of indexed views (see MSFT technet article)
    Wednesday, February 27, 2008 3:42 PM
  • Unfortunately, a UNIQUE CONSTRAINT on a single column will only allow a single row to have a NULL value.

    As you discovered, adding a second row with a NULL value will violate the CONSTRAINT.

     

     

     

    Wednesday, February 27, 2008 3:43 PM
  • You could enforce uniqueness using a Trigger, allowing as many NULL values as required, but ensuring all other values are unique.

     

     

    Code Snippet

    CREATE TRIGGER tr_Test ON t

    INSTEAD OF INSERT

    AS

     

    DECLARE @var int

    SET @var = (SELECT Value FROM Inserted)

     

    IF NOT @var IS NULL

    BEGIN

    IF EXISTS (SELECT 1 FROM t WHERE Value = @var)

    BEGIN

    ROLLBACK TRAN

    END

    ELSE

    INSERT t (Value)

    VALUES (@var)

    END

    ELSE

    INSERT t (Value)

    VALUES (@var)


    Edit: I've revised the above code to use an INSTEAD OF trigger which should now do what you want it to.

    Wednesday, February 27, 2008 3:53 PM
  • When it comes to the UNIQUE constraint the multiple NULL values are considered as duplicate (ie, NULL=NULL => TRUE), even  if you change the DB level settings (using EXEC sp_dboption 'dbname','ANSI NULLS', 'ON'), this behavior won’t change.

     

    Test the following code,

     

    Code Snippet

    CREATE TABLE Numbers

    (

           Number int primary key,

           Word  varchar(20) null UNIQUE

    )

     

    Go

    Insert Into Numbers values(1,'One') -- Success

    Insert Into Numbers values(2,'Two') -- Success

    Insert Into Numbers values(3,'Two') -- Error is expected bcs Two is duplicated

    Insert Into Numbers values(6,NULL) -- Success

    Insert Into Numbers values(7,NULL) -- Suprise, the error for duplicate

     

     

     

     

    So, In SQL Server if we add the UNIQUE constraint we can’t add more than one NULL values in the table.

     

     

    Work Around / Solution

    OK. How to overcome this issue - Simple, we need to cheat the index – my values are always non-null. Hide the NULL values from the UNIQUE INDEX.

     

    Steps,

    1. Create table without UNIQUE constraint

    2. Create a VIEW & filter only the NON-NULL values

    3. Now create the UNIQUE constraint against the VIEW (INDEXED VIEW)

     

    That’s it..! As much possibleJ we Hacked/Cheated the SQL Server..!

     

    Sample Code,

     

    NOTE: Without this SET statements you can’t able to create the INDEXED VIEWS.

                   

     

    Code Snippet

    SET NUMERIC_ROUNDABORT OFF;

    SET ANSI_PADDING, ANSI_WARNINGS

    SET CONCAT_NULL_YIELDS_NULL, ARITHABORT ON;

    SET QUOTED_IDENTIFIER, ANSI_NULLS ON;

     

    CREATE TABLE Numbers

    (

        Number int primary key,

        Word  varchar(20) null

    )

     

    Go

    Create View view_Numbers With Schemabinding

    As

    Select

         Word

    from

        dbo.Numbers

    Where

        Word is not null

    Go

     

    Create Unique Clustered Index index_unique_Numbers

    on view_Numbers (Word)

     

    Go

     

    --Now you can forget the Index & View ::

    --Don't worry about these objects any more

    --Come back to the tables

     

    Insert Into Numbers values(1,'One') -- Success

    Insert Into Numbers values(2,'Two') -- Success

    Insert Into Numbers values(3,'Two') -- Error ‘Two’ is duplicated

     

    Insert Into Numbers values(6,NULL) -- Success

    Insert Into Numbers values(7,NULL) -- Success

     

     

     

    Wednesday, February 27, 2008 4:38 PM
  •  Sara4 wrote:

    So, what are my options? No easy solutions? There's no way to tell a constraint to ignore the nulls?

    Now I see you added the BOL quote...

     

     

     

     

    A couple of them are:

     

    1 - Use a calculated column (Steve Kass, who came with this idea, called it nullbuster) to take the value from a unique column when the value of the column in question is null.

     

    Example:

     

    Code Snippet

    create table dbo.t1 (
    c1 int not null identity primary key,
    c2 int null,
    c3 as case when c2 is null then c1 else 0 end,
    constraint uq_t1_c2_c3 unique (c2, c3)
    )
    go
    insert into dbo.t1(c2) values(NULL)
    insert into dbo.t1(c2) values(NULL)
    insert into dbo.t1(c2) values(NULL)
    go
    insert into dbo.t1(c2) values(1)
    insert into dbo.t1(c2) values(2)
    go
    -- will fail here
    insert into dbo.t1(c2) values(1)
    go
    drop table dbo.t1
    go


    2 - Use an indexed view, as TheSQL suggested.

    In SQL Server 2008, we will have a new feature named filtered indexes and you will be able to do something like:

     

    create t1_c2_u_nc_ix
    on dbo.t1(c2)
    where c2 is not null
    go

     

    AMB

    Wednesday, February 27, 2008 4:46 PM
  • Thank you all for your input.

     

    Here's my feedback Smile :

     

    1) I don't fully understand how do I implement the indexed view. If I open the table, change the field on which I have to run the duplicate check, how do I 'run' the view?

     

    2) the trigger idea could work fine, I need to do some modifications to be 100% sure, I'll let you know.

     

     

    Thursday, February 28, 2008 8:36 AM
  •  hunchback wrote:

    1 - Use a calculated column (Steve Kass, who came with this idea, called it nullbuster) to take the value from a unique column when the value of the column in question is null.

     

    Hi,

     

    I saw this, and thought it would solve my "unique or null" problems. Unfortunately, I have spent a day trying to get this to actually work, and it would see that I am hitting the "ARITHABORT" issues that so many others seem to have.

     

    Namely... "Update failed because the following SET options have incorrect settings: ARITHABORT"

     

    Now, having read this http://msdn.microsoft.com/en-us/library/ms175088(SQL.90).aspx, I have added to following SQL code...

     

     set ansi_nulls,ansi_padding,ansi_warnings,arithabort,concat_null_yields_null,quoted_identifier on
     set numeric_roundabort off

     

    ...to the script that adds the column and the unique constraint (immediately before the add). I put the code in my 'update' stored procedure (immediately before the update) and I added it to the script that created the stored procedure - and re-created the stored procedure. I created a table and had my update stored procedure write the sessionproperty values for all seven to the table. I checked, they were all as expected. Still my update fails.

     

    What gives ?

    (I'm using SQL2005)

     

    Here's the code for the computed column:

     

     alter table [dbo].[MyTable]
      add nullbuster as (case when Description is null then MyIdentityColumn else 0 end)

     

     ALTER TABLE [dbo].[MyTable]
      ADD CONSTRAINT [UNQ_Description]
       UNIQUE NONCLUSTERED ([Description], nullbuster)
       ON [PRIMARY]

     

    Interestingly enough, this doesn't seem to happen if I run the SP from a query window in SQL Management Studio. My application is VB2005 using ADO.Net.

     

    Thanks

    Thursday, October 16, 2008 12:09 PM
  •  Ross Watson wrote:

    Interestingly enough, this doesn't seem to happen if I run the SP from a query window in SQL Management Studio. My application is VB2005 using ADO.Net.

     

    Doing some more searching, it seems that this is a problem which no-one can explain, but which one possible work-around (while ugly) is demonstrated as follows:

     

        Sub Main()
            Using cnx As New SqlConnection(My.Settings.ConnectionString)
                cnx.Open()

                Using cmd As New SqlCommand("SET ARITHABORT ON", cnx)
                    cmd.ExecuteNonQuery()
                End Using
                Using cmd As New SqlCommand("up_Update", cnx)
                    cmd.CommandType = CommandType.StoredProcedure
                    cmd.Parameters.AddWithValue("@identity", 170)
                    cmd.Parameters.AddWithValue("@description", DBNull.Value)
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        End Sub

     

    No... I don't know why that should work (when having the "sets" in the stored procedure doesn't) either.

    Thursday, October 16, 2008 1:45 PM
  • Ross,

     

    That is a requirement for indexes on computed columns. We are using a unique constraint and the way SQL Server implement a unique constraint is creating a unique index.

     

    This is from BOL.

     

    The connection on which the index is created, and all connections trying INSERT, UPDATE, or DELETE statements that will change values in the index, must have six SET options set to ON and one option set to OFF. The optimizer ignores an index on a computed column for any SELECT statement executed by a connection that does not have these same option settings.

    • The NUMERIC_ROUNDABORT option must be set to OFF, and the following options must be set to ON:

    • ANSI_NULLS

    • ANSI_PADDING

    • ANSI_WARNINGS

    • ARITHABORT

    • CONCAT_NULL_YIELDS_NULL

    • QUOTED_IDENTIFIER

     

    Creating Indexes on Computed Columns

    http://msdn.microsoft.com/en-us/library/ms189292.aspx

     

    > Interestingly enough, this doesn't seem to happen if I run the SP

    > from a query window in SQL Management Studio.

     

    That is because SSMS set "SET ARITHABORT" on by default. To check it in SSMS, click Tools - Options... - Query Execution - SQL Server - Advanced.

     

    AMB

    Thursday, October 16, 2008 3:08 PM
  • Hi Hunchback,

    thank you for taking the time to re-visit this topic. I read BOL and the microsoft web sites that document indexes on computed columns but... well... I must be missing the point.

    Is it saying that these seven options must be set on all connections, explicitly, regardless of what any stored procedure, run on that connection, does ? I mean... I set these options a) when I created the procedure, b) within the procedure immediately prior to the update and c) immediately prior to creating the column and index.

    It would seem bizarre that the options set in the stored procedure do not affect the outcome (or does it abort before it even gets there ?). However, I did notice (when trying to write out the session settings prior to the update) that I got the error and no record was written to my trace table.

    My code runs using a SQLDataAdapter. Getting it to run an extra "SET" command before running my stored procedure (in the UpdateCommand) is not that straight-forward.

    Is there a way to set these options as a default, either using ADO.Net or in the configuration of the server ?

    Thanks
    Thursday, October 16, 2008 7:33 PM
  • Ross,

     

    I haven't used .net programming for long time now. Try including the set options in the same string used by the sqlcommand, used for the adapter updatecommand.

     

    set arithabort on;

    set ansi_nulls on;

    ...

    exec my_sp ...

     

     

    AMB

    Thursday, October 16, 2008 8:15 PM
  • Hi,

    I'm using a stored procedure, with a lot of parameters, (ie. commandtype = storedprocedure - and all the mapping of DataColumns to procedure parameters), and don't really fancy trying to get the code to use an sql command (ie. commandtype = text) instead.

    I'm only really even thinking of doing this because it seems crazy that this problem seems to be asked about a fair bit (well... google seems to think so), and yet it's not easy to understand a) what the problem is and b) how to fix it.

    I don't understand why setting these options (what seems like) everywhere doesn't work, and yet setting them on the same connection does, regardless of what the SP might be doing.

    Thanks again for your help and suggestions,
    Thursday, October 16, 2008 9:19 PM
  • Ross,

     

    I can not tell you exactly how it works internally, because I do not know. My guess is that the execution plan is created before executing the batch, and SQL Server uses some of the SET options as part of the key that identify the plan. If you set them outside of the sp, then SQL Server will know about them prior to executing the stored procedure, because SET ARITHABORT is set during execution time and not during parse time.

     

    AMB

     

    Friday, October 17, 2008 1:38 PM
  • Thanks again,

     

    One of the downsides of working alone, is that almost any solution seems like a good one. Hence, I make no claims for my "solution". It works for me.

     

    Given that I have limited access to the commands and connections used by the "typed dataset" generated code that uses SQLCommand and SQLConnection objects, I decided to do something that seems to work (for the time being). Namely, as I can get access to the connection object, I placed a handler to the "state change" event, and when that state change is "connection is now open", I execute the "SET ARITHABORT" code. Then, when the ADO code runs my stored procedure, it has the correct settings and all is happy with the world.

     

    Ugly... best put lots of green code around that bit...

     

    Friday, October 31, 2008 3:48 PM