Rule 3: Value of Sex Must Be Valid

[Previous] [Next]

Only three values are valid for the sex of a horse. They are s (sto = filly), h (hingst = male), and v (vallack = gelding). As you probably remember, sto, hingst, and vallack are Swedish words.

This rule is all about a small set of valid values. Can we have the database protect this rule as well in a standardized way? Yes, we can. We can use a check constraint. Such a constraint is capable of checking column values against a list of valid values.

You can specify such a constraint using SQL, but in a Microsoft environment you can also use Visual Database Tools. All you have to do is follow these steps:

  1. Start Microsoft SQL Server Enterprise Manager.
  2. Expand the proper server in the left console tree.
  3. Expand the Databases folder, and select the correct database—in our case, the NewRacingDB database.
  4. Select Tables in the console tree to see the tables of the database in the right pane.
  5. Right-click the name of the table you want to modify, and then click Design Table in the shortcut menu.
  6. When the table design dialog box appears, click the Table And Index Properties toolbar button. (Although the toolbar buttons aren't labeled, hovering over the button will display its name within a ToolTip.)
  7. In the Properties dialog box, set the check constraint as you see in Figure 4-10 by clicking the New button and entering the text in the Constraint Expression box.
  8. Figure 4-10. A check constraint on the sex column is declared.

You can also declare the constraint this way:

Sex IN ('s', 'v', 'h')

This is what we did. You see in Figure 4-10 what SQL Server changed it to.

Figure 4-11 shows an example from another test run. In that run, we tried to change the sex column for Dry Sludge to an invalid value, namely m. As you can see from the figure, SQL Server didn't let us do that.

click to view at full size.

Figure 4-11. Only valid sex values are accepted by the database because of the check constraint.

Rule 3 Is Set

We have implemented rule 3 in much the same way we did the first two rules. For rule 3, however, we don't use a foreign key constraint but a check constraint. Check constraints have the capability of checking each value of a specific database column against a given list of constants or an expression. This expression, however, is unable to access information outside the row itself. Check constraints, then, are pretty limited in their capabilities, but when they fit the requirements they're beautiful.

Like foreign key constraints, check constraints belong to the ISO/ANSI SQL standard. Also, they normally don't involve any real code, just a declaration with a small piece of code doing comparisons as in our example.

You should be able to use check constraints with any database that complies with the ISO/ANSI SQL standard.

NOTE
If you're a database person, you might think we've belabored the issues of the first three rules. You don't need to discuss simple things such as foreign key constraints and check constraints because you already know you should use them!

An object-oriented programmer, on the other hand, might think our treatment of the rules is peculiar. Why should you use these kinds of functions in the first place? A database, after all, is a place to store information, nothing else. The valuation of properties is the object's business and has nothing to do with the database, right?

We firmly believe that some things that the object person would place in objects should be in the database instead. We also firmly believe that some of the things the database person would like to see in the database would be much better off in an object.

That's why we believe this discussion is important. Our sample application might be oversimplified, but it still serves our purpose, which is to show that we need to consider for each business rule where we should put the code or the declarations that honor it.



Designing for scalability with Microsoft Windows DNA
Designing for Scalability with Microsoft Windows DNA (DV-MPS Designing)
ISBN: 0735609683
EAN: 2147483647
Year: 2000
Pages: 133

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net