Unique Constraints

[Previous] [Next]

The only unique constraint we have in our database, at least the parts we've studied so far, is the one making sure that no two horses bred in the same country have the same name. It's OK for two horses to have the same name as long as they were bred in different countries.

First, to prove our point, let's add another Lambada Hot. The one we have was bred in Ireland, so let's say the new one was bred in Sweden. The database should accept this new horse, and it does. The following SQL statement

 INSERT INTO Horses (HorseId, HorseName, BredIn, Sex, Birthyear, Active, Trainer) VALUES (99999, 'Lambada Hot', 'SWE', 's', 1989, 0, 16) 

adds a horse to the database, and when we ask for all data on all horses that are named Lambada Hot, we get the answer shown in Figure 22-1.

Figure 22-1. The database now houses two horses named Lambada Hot, but they're both accepted because they were bred in different countries.

We continue by issuing the following INSERT statement, trying to insert yet another horse named Lambada Hot, bred in Sweden. We've given that horse the unique ID value 99998 to avoid a primary key violation:

 INSERT INTO Horses (HorseId, HorseName, BredIn, Sex, Birthyear, Active, Trainer) VALUES (99998, 'Lambada Hot', 'SWE', 's', 1989, 0, 16) 

This time, fortunately, we were less lucky. Here's the message we received from SQL Server:

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

Even if the combination of horse name and country where bred isn't designated as the primary key of the table, the unique constraint defined in this column combination stops this horse from being inserted. The unique constraints feature is probably one of the most underused features of a DBMS such as SQL Server. You should always check the structure of all your database tables for columns or column combinations that should be unique and aren't designated as the primary key of the table.

Notice that the message number is no longer 547 as in the earlier cases but 2601.

Checking for Unique Constraint Violations

For tables that have a unique constraint, your data-modifying components should check for message number 2601 coming from SQL Server. In fact, all your data-modifying components should check for this message to allow for unique constraints being added to the database after your component is designed.