Rule 1: Bred in Country

[Previous] [Next]

Each horse is bred in exactly one country.

This is a perfect example of a rule that you should implement in the database by using a foreign key constraint. A foreign key constraint is a rule that ensures that the data in two related database tables will remain logically consistent.

A Foreign Key Constraint

Figure 4-2, which is a detail from Figure 4-1, shows that the Horses table already has a foreign key constraint that relates to the Countries table. The diagram represents this constraint with a relationship line between the two tables, showing a key on the Countries side and the symbol for infinity on the Horses side. This relationship indicates that each horse must be connected to exactly one country¹ and that each country can be connected to an infinite number of horses.

Figure 4-2. The Countries and Horses tables have a one-to-many relationship.

A look at the properties of this relationship (see Figure 4-3) reveals that it's based on equality between the BredIn column of the Horses table and the CountryCode column of the Countries table. Just as it should be!

A foreign key constraint between two tables makes the database software (in this case, Microsoft SQL Server) guarantee the following, using the Horses/Countries foreign key as an example:

• No horse record is accepted for insertion unless the value of the BredIn column matches an existing value in the CountryCode column of the Countries table.
• No row in the Countries table is deleted if its CountryCode value exists in the BredIn column of any row in the Horses table.
• The software won't accept updates to any CountryCode value if the original value exists in the BredIn column of any row in the Horses table.
• No update to a BredIn value will be accepted if the new value doesn't match a value that exists in the CountryCode column of the Countries table.



Figure 4-3. This dialog box displays the details of the foreign key relationship between the Horses and Countries tables.

Now the database itself is responsible for observing rule 1. You'll have to admit that foreign key constraints give you a lot of value for a small effort.

Not all relationships, however, lend themselves to foreign key constraints. Suppose you need a cascading update that refreshes data for one or more horses at the same time that you refresh data for a country table referred to by those horses. The foreign key constraint just described outlaws the behavior you need. Until foreign keys in SQL Server support cascades, you'll have to implement the one-country-perhorse rule a different way.

Following the ANSI Standard

Foreign key constraints, as Microsoft has implemented them in SQL Server and as we have described them, are part of the ANSI standard for structured query language (SQL). In other words, even if you change your database software from SQL Server to another database management system (DBMS), you should be able to use your foreign key constraints without having to change anything at all. This is true at least if the other DBMS also follows the ANSI standard for foreign keys.

This, we think, is another good reason to use foreign key constraints when applicable.

Database as the Last Defense

Another reason to use foreign key constraints is that the database itself then serves as the last defense against improper database modifications. Everyone must follow every rule that's declared within the database. There's no way around it.

Let's see what happens if we try to break this rule by sending a few potentially dangerous SQL statements to the database.

We tried to delete a country in which at least one (in fact, quite a few) of the horses in the Horses table had been bred. Figure 4-4 shows you SQL Server's response.

Figure 4-4. You can't delete a country in which any horse in your database has been bred.

As you can see from the error message, SQL Server won't allow this, thanks to the foreign key constraint.

Notice that, as far as SQL Server is concerned, the conflict didn't occur in the Countries table but in the Horses table.

You might also note that we're careful not to allow any mistakes on our part to damage the database. We include BEGIN TRANSACTION as the first statement of the batch, which allows us to roll back any unforeseen and unwanted changes.

As Figure 4-5 shows, the foreign key constraint also protects you from breaking the reference between any horse and its country by modifying the CountryCode value of the country in which the horse was bred.

Figure 4-5. You can't change the CountryCode value of a country in which any horse listed in the database has been bred.

Now let's try to alter the data for a horse in such a way that its BredIn value stops referring to an existing country. Dry Sludge is a horse bred in Sweden. Dry Sludge's HorseId value is 90. Let's change the BredIn value to the invalid CountryCode SWO. See what happens in Figure 4-6.

Figure 4-6. You can't change any row of the Horses table in such a way that its BredIn value refers to a CountryCode value that's not in the Countries table.

Evidently, no row in the Countries table shows SWO as a CountryCode value, so SQL Server again denies the update.

The same constraint applies if you try to insert a new horse, giving it an invalid BredIn value. The foreign key constraint is foolproof!

You can, however, modify the BredIn column for a horse from one acceptable value to another. I guess this should go without saying, but having given you instances of an unwilling database, we'll balance our samples with an example of an obliging database, which you can see in Figure 4-7.

Figure 4-7. SQL Server (like other ANSI databases) does change the database when statements it receives don't break any rules.

Now we are really glad we started the batch with a BEGIN TRANSACTION statement. Dry Sludge was bred in Sweden, not in Denmark. A rollback transaction statement will put everything back as it should be again, and we'll be ready to move on.

Rule 1 Is Set

First, however, some final observations about rule 1: The rule is now secured directly in the database. There's no way for a user to break this rule; the user can't bypass this layer. Even a direct SQL statement trying to break the rule will fail.

Furthermore, the rule is secured in a standardized way. We have secured it in a SQL Server database, but it could also have been in an Oracle or Informix database. In fact, if it were in an Oracle database you could technically secure it in exactly the same way we did, using Microsoft Visual Database Tools to draw a diagram.