SUMMARY
This article shows how to enforce uniqueness for all non-NULL values (ANSI UNIQUE CONSTRAINT).
back to the topList 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 topThe 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 topThe 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 topThe 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