Creating a Trigger That Rejects Duplicates

One of my database tables contains information about companies. All the table's companies have a name, but only companies listed on the stock market have a code. I created a unique index on the company-name field and would also like to create one on the company-code field because if a code exists, the company-code field must be unique. Companies without a code have either a NULL value or a zero-length string in this field. When I try to create the unique index, I receive the following error message:

Server Msg 1505, Level 16, State 1CREATE UNIQUE INDEX terminated because aduplicate key was found. Mostsignificant primary key is ''

Can I create a trigger that first determines whether the value already exists in a field, then returns an error if it finds a duplicate?

Using a trigger is a good choice for finding the data you want because a trigger fires for INSERT and UPDATE statements. Here's how the trigger works. First, the trigger joins the inserted table (which holds the new image of the rows modified in the Companies table). Next, it groups the rows by company code. Then, it determines whether company codes other than an empty string (represented by single quotation marks) include duplicates in the HAVING clause (HAVING COUNT(*) > 1). Note that you don't need to add an explicit filter to exclude NULLs from the test because a filter such as WHERE I.companycode <> '' also excludes NULLs. If duplicates other than empty strings (or NULLs) exist, the trigger rolls back the transaction. The code in Listing 9 creates a sample Companies table and an INSERT/UPDATE trigger. You can test the trigger by inserting rows that don't have duplicates and rows that have duplicates with an empty string, then confirm that they're all inserted into the table:

INSERT INTO Companies(companycode) VALUES(NULL)INSERT INTO Companies(companycode) VALUES(NULL)INSERT INTO Companies(companycode) VALUES('')INSERT INTO Companies(companycode) VALUES('')INSERT INTO Companies(companycode) VALUES('A')

Now, try to insert a nonempty-string duplicate value:

INSERT INTO Companies(companycode) VALUES('A')

The query fails.

The trigger solution works both in SQL Server 2000 and 7.0. However, in SQL Server 2000, you can use another trick, which I learned from SQL Server MVPs Kalen Delaney and Fernando G. Guerrero. First, create a view that filters nonempty strings and non-NULL company codes so that the uniqueness applies only to the non-NULL values, as Listing 10 shows. Then, just create a unique clustered index on the companycode column in the view:

CREATE UNIQUE CLUSTERED INDEX IDX_CI_UNQComp ON VUNQCompanies(companycode)

You can insert as many empty strings and NULLs into the table as you want because they're excluded from the view. The unique index will reject any other duplicate. For example, you should be able to successfully insert the following rows into the Companies table:

INSERT INTO Companies(companycode) VALUES(NULL)INSERT INTO Companies(companycode) VALUES(NULL)INSERT INTO Companies(companycode) VALUES('')INSERT INTO Companies(companycode) VALUES('')INSERT INTO Companies(companycode) VALUES('B')

Now, try to enter a duplicate:

INSERT INTO Companies(companycode) VALUES('B')

You should receive the following error message:

Server: Msg 2601, Level 14, State 3, Line 1Cannot insert duplicate key row in object 'VUNQCompanies' with unique index 'IDX_CI_UNQCompanies'. The statement has been terminated.