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

Answer 1

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.

 

Answer 2

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

Answer 3

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)

Answer 4

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.

 

 

 

Answer 5

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.

Answer 6

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

 

 

 

Answer 7

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

Answer 8

Thank you all for your input.

 

Here's my feedback :

 

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.

 

 

Answer 9

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