Rule 5: Between 1 and 15 Years Old

[Previous] [Next]

The birth year of a horse must be such that the horse is between 1 and 15 years old.

We don't need to say much about this rule. As with rule 3, you can implement it as a check constraint on the Birthyear column, as you can see in Figure 4-12. In contrast with the way you work with rule 3, however, you would use for rule 5 an SQL expression rather than a list of valid constants.

Figure 4-12. Rule 5 implemented as a check constraint in the database.

Now, if you try to update a horse to make its age less than 1 or more than 15 years, you get a standardized error message from SQL Server:

UPDATE Horses SET Birthyear = 1983 WHERE HorseId = 365 Server: Msg 547, Level 16, State 1 UPDATE statement conflicted with TABLE CHECK constraint 'CK_Horses_Birthyear'. The conflict occurred in database 'MSPress_ADBRacing', table 'Horses'. The statement has been aborted.

The client programs (especially those running on the server) should probably be written to recognize this message and convert it to one that brings more useful information to the user.

Following the Standard

We have said it before, and we will say it again. Database check constraints⁸ belong to the current SQL standard. This makes check constraints convenient to use, especially since there is no way around them. As with triggers, it doesn't matter how the client program changes the data. The rule will always be enforced, and invalid changes will always be rejected.