HOW TO: Enforce Uniqueness For All Non-Null Values (ANSI UNIQUE CONSTRAINT) (322002)



The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
  • Microsoft SQL Server 2000 64 bit (all editions)

This article was previously published under Q322002

SUMMARY

This article shows how to enforce uniqueness for all non-NULL values (ANSI UNIQUE CONSTRAINT).

back to the top

List of Possible Solutions

There are three possible solutions that you can use to create an ANSI UNIQUE CONSTRAINT. Because database systems are configured differently, you have to evaluate which solution best fits your environment. The three solutions follow:
  • Create a table that allows nulls, create a view with the WITH SCHEMABINDING clause that excludes nulls, and then create a unique index on the view.
  • Create a calculated column that is based on the column that allows nulls, and then define a UNIQUE constraint on the calculated column.
  • Create a trigger that uses the EXISTS operator to see if the non-null value is already in the column.

back to the top

The Table - View Solution

This solution creates a view that excludes all null values. The view is created with the SCHEMABINDING option, which allows the creation of an index.
CREATE TABLE [Table1] (
	[pk] [int] IDENTITY (1, 1) NOT NULL ,
	[Col1] [int] NULL ,
	[Col2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL CONSTRAINT [DF_Table1_Col2] DEFAULT ('SomeValue')
) ON [PRIMARY]
GO

CREATE VIEW dbo.V_Table1
WITH SCHEMABINDING 
AS
SELECT    pk, Col1, Col2
FROM         dbo.Table1
WHERE     (Col1 IS NOT NULL)
GO

Set QUOTED_IDENTIFIER ON
GO

Set ARITHABORT ON
GO

CREATE UNIQUE CLUSTERED INDEX IDX1 ON V_Table1( Col1 )
GO
Test the solution with the following insert statements:
INSERT INTO Table1( Col1 ) VALUES( 1 )
INSERT INTO Table1( Col1 ) VALUES( 1 ) *
*This insert statement fails and you receive this error message:
Cannot insert duplicate key row in object 'V_Table1' with unique index 'IDX1'. The statement has been terminated.
INSERT INTO Table1( Col1 ) VALUES( 2 )
INSERT INTO Table1( Col1 ) VALUES( 3 )
INSERT INTO Table1( Col1 ) VALUES( NULL ) 
INSERT INTO Table1( Col1 ) VALUES( NULL )  'This INSERT runs without error.
INSERT INTO Table1( Col1 ) VALUES( NULL )  'This INSERT runs without error.
Now, if you query the table, you get these results:

SELECT * FROM Table1

pk          Col1        Col2       
----------- ----------- -------------
1           1            SomeValue
3           2            SomeValue
4           3            SomeValue
5           NULL         SomeValue
6           NULL         SomeValue
7           NULL         SomeValue

With this solution, all the rows can be inserted into the database, except for the inserts that cause the error. Also, because there was a violation of an index, an error can be raised to the calling application to notify the application when an insert fails.

back to the top

The Calculated Column Solution

The following solution uses a calculated column to prevent duplicate non-null values.
CREATE TABLE [Table2] (
	[pk] [int] IDENTITY (1, 1) NOT NULL , 
	[Col1] [int] NULL ,
	[Col2] AS ( 	
			CASE 
				WHEN Col1 IS NULL THEN pk 
				ELSE 0 
			END
		    )
	CONSTRAINT UNQ_NULLS UNIQUE ( Col1,Col2 )
)
GO
Test the solution with the following insert statements:
INSERT INTO Table2 ( Col1 ) VALUES( 1 )
INSERT INTO Table2 ( Col1 ) VALUES( 1 ) *
* This insert statement fails with this error:
Violation of UNIQUE KEY constraint 'UNQ_NULLS'. Cannot insert duplicate key in object 'Table2'. The statement has been terminated.
INSERT INTO Table2 ( Col1 ) VALUES( 2 )
INSERT INTO Table2 ( Col1 ) VALUES( 3 )
INSERT INTO Table2 ( Col1 ) VALUES( NULL )
INSERT INTO Table2 ( Col1 ) VALUES( NULL ) 'This INSERT runs without error.
INSERT INTO Table2 ( Col1 ) VALUES( NULL ) 'This INSERT runs without error.
Now, if you query the table, you get these results:

SELECT * FROM Table2

pk          Col1        Col2        
----------- ----------- -----------
1           1           0
3           2           0
4           3           0
5           NULL     5
6           NULL     6
7           NULL     7

The calculated column solution provides a clean solution to this problem. One advantage is that the constraint is applied to the table itself, and no extra view or trigger is required. Again, with this solution, an error is raised that can notify the calling application when the constraint is violated.

back to the top

The Trigger Solution

The last solution requires a trigger check to verify if the non-null value already exists in the column. If the value does exist, the transaction is rolled back.
CREATE TABLE [dbo].[Table3] (
	[pk] [int] IDENTITY (1, 1) NOT NULL ,
	[Col1] [int] NULL ,
	[Col2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY]
GO

CREATE TRIGGER Table3NullsTrigger ON Table3 FOR INSERT, UPDATE
AS
IF	(SELECT count( pk ) FROM inserted
    	WHERE exists (SELECT  pk  FROM Table3 WHERE Table3.Col1 = inserted.Col1 AND Table3.pk!= inserted.pk)) > 0 
BEGIN
    ROLLBACK TRANSACTION
END
GO
Test this solution with the following insert statements:
INSERT INTO Table3 ( Col1 ) VALUES( 1 )
INSERT INTO Table3 ( Col1 ) VALUES( 1 ) 'Error because of duplicate
INSERT INTO Table3 ( Col1 ) VALUES( 2 ) 'No value inserted because of rollback.
INSERT INTO Table3 ( Col1 ) VALUES( 3 ) 'No value inserted because of rollback.
INSERT INTO Table3 ( Col1 ) VALUES( NULL ) 'No value inserted because of rollback.
INSERT INTO Table3 ( Col1 ) VALUES( NULL ) 'No value inserted because of rollback.
INSERT INTO Table3 ( Col1 ) VALUES( NULL ) 'No value inserted because of rollback.
Now, if you query the table, you get these results:

SELECT * FROM Table3

pk          Col1        Col2                                               
----------- ----------- ---------
1           1           NULL

When the first duplicate non-null value was found, the transaction was rolled back; therefore, only one row was inserted. However, if each INSERT statement had executed individually, only this line would have failed:
INSERT INTO Table3 ( Col1 ) VALUES( 1 ) --Error because of duplicate
NOTE: Because there are no constraints or indexes used in this example, no errors are raised to the calling application.

back to the top


Modification Type:MinorLast Reviewed:12/26/2003
Keywords:kbHOWTOmaster KB322002 kbAudDeveloper