Rule 7: Unique Combination of Name and Country Where Bred

[Previous] [Next]

The combination of the name of the horse and the country it was bred in must be unique. The names of racehorses are in fact unique within the country the horse was bred in.

This kind of rule is almost omnipresent. In many cases, developers in fact forget to implement rules like this one. The result is a damaged database that might give faulty information.

In a properly constructed relational database, every table has a primary key. This key consists of one column or a combination of several columns. The values in this column or this combination of columns must be unique for each row of the table.

There are two kinds of primary keys: natural and synthetic.

A natural key is a column, or a combination of columns, that carries real data, data that helps in determining the state of the object that's represented in the table row. The combination of a horse's name and the country in which the horse was bred is an example of a natural key. At least for English thoroughbreds, this combination is always unique. In other words, the name of a racehorse is always unique within the country where the horse was bred.

For several good reasons, database designers and administrators don't like it when a primary key is large—in the sense of containing many characters. Large primary key values make searching, indexing, and joins inefficient. So a database administrator (DBA) would tend to create an alternative key, one that uses less space. Such a key doesn't carry any information about the object in question. Its only purpose is to uniquely identify the object. Since it doesn't carry any information, this sort of key is usually said to be synthetic.

This is exactly what we did with the Horses table. The table's natural key is the combination of the HorseName and BredIn columns. Because of the large size of this combination, however, we didn't want to use it as a primary key. So we added the HorseId column, made it a reasonably cheap integer, and nominated it as the primary key of the table. You can see the result in Figure 4-16.

Figure 4-16. HorseId is a synthetic key. The natural key is the combination of the HorseName and BredIn columns.

Don't forget the natural key

The primary key is always protected by an automatically created unique index. SQL Server creates this index the moment you set up the column as a primary key. The natural key won't be protected until you yourself take actions to protect it.

Protecting the natural key is easy. Just create a unique constraint on the column combination in question. The easiest way is to use the Properties dialog box for the table. In it, just select the Indexes/Keys tab and then select the Create UNIQUE check box and make sure the Constraint radio button is selected, as shown in Figure 4-17.

Figure 4-17. The combination of the HorseName and BredIn columns is now made unique.

By the way, this way of creating unique constraints should work equally well with Oracle.

Now we have also implemented rule 7. It was easy enough, yet developers and database administrators tend to overlook this kind of rule. That's the reason we emphasize this rule here in our book.