Enforcing Uniqueness on Optional Fields in SQL Server Database Tables

Enforcing the uniqueness of data in a table is a common requirement.  For example an "Employees" table might contain an "EmployeeID" field that must uniquely identify each row.  This is usually handled by creating a primary key or unique constraint on the field.  This works well when the field in question is required.  In our example each employee is assigned an employee ID number.  Sometimes however this is not sufficient to cover all circumstances.  Let us assume that each employee may be assigned a company email address, but not all employees will be given an email address.  No two employees can share the same email address.  We want the "EmailAddress" field in the Employees table either to be empty or contain a value not already in the table.  We can enforce the uniqueness of the email address values either by using an indexed view or by creating an index.

Here is the T-SQL used to create the Employees table we will use for our examples.

CREATE TABLE Employees

(

EmployeeID CHAR(4) NOT NULL PRIMARY KEY,

LastName VARCHAR(20) NOT NULL,

FirstName VARCHAR(20) NOT NULL,

EmailAddress VARCHAR(30) NOT NULL DEFAULT ''  

);

Option 1: Using an Indexed View

We can create a view of Employee records with an email address then create a unique index on the EmailAddress field.  If a user tries to insert a duplicate email address the insert will fail.

Here is the T-SQL used to create the view and index.   Note that the view only contains records which have a non-empty email address.

CREATE VIEW EmployeeEmailAddresses WITH SCHEMABINDING

AS

SELECT EmailAddress

FROM dbo.Employees

WHERE EmailAddress != '';

GO

CREATE UNIQUE CLUSTERED INDEX Index_EmployeeEmailAddresses_EmailAddress

ON EmployeeEmailAddresses ( EmailAddress );

We can then insert some records into the Employees table using the T-SQL statements below.

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E100', 'Smith', 'Jon', 'smith@example.com' );

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E200', 'Pond', 'Amy', '' );

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E300', 'Noble', 'Donna', 'noble@example.com' );

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E400', 'Jones', 'Martha', '' );

-- this insert will fail

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E500', 'Smith', 'Sarah-Jane', 'smith@example.com' );

The last insert will fail because a record already exists with the email address "smith@example.com".  This approach will work in both SQL Server 2005 and 2008.

Option 2: Create an Index

SQL Server 2008 introduced the WHERE clause into the syntax for the CREATE INDEX statement.  We can create a unique index on records which are not empty with the T-SQL statement below.

CREATE UNIQUE INDEX Index_Employees_EmailAddress

ON Employees ( EmailAddress )

WHERE ( EmailAddress != '' );

To test this index first drop the indexed view and empty the Employees table, then insert our test data again.  The last insert will fail.

-- this will drop the indexed view we created earlier if it exists

IF EXISTS ( SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'EmployeeEmailAddresses' )

BEGIN

DROP VIEW EmployeeEmailAddresses;

END;

--  empty the Employees table

DELETE 

FROM Employees;

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E100', 'Smith', 'Jon', 'smith@example.com' );

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E200', 'Pond', 'Amy', '' );

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E300', 'Noble', 'Donna', 'noble@example.com' );

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E400', 'Jones', 'Martha', '' );

-- this insert will fail

INSERT INTO Employees ( EmployeeID, LastName, FirstName, EmailAddress )

VALUES ( 'E500', 'Smith', 'Sarah-Jane', 'smith@example.com' );

This technique is new to SQL Server 2008 and is not backwards compatible.  

For more information on each approach consult the SQL Server documentation.

References

Create View from SQL Server Books Online:

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

Create Index from SQL Server Books Online:

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