Microsoft KB Archive/322002

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

Article ID: 322002

Article Last Modified on 12/26/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 2000 64-bit Edition

-



This article was previously published under Q322002



IN THIS TASK
SUMMARY List of Possible Solutions
 * The Table - View Solution
 * The Calculated Column Solution
 * The Trigger Solution



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

Additional query words: unique &quot;indexed view&quot;

Keywords: kbhowtomaster KB322002

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.