Joe Celko s SQL for Smarties - Advanced SQL Programming P5. In the SQL database community, Joe Celko is a well-known columnist and purveyor of valuable insights. In Joe Celko's SQL for Smarties: Advanced SQL Programming, he picks up where basic SQL training and experience leaves many database professionals and offers tips, techniques, and explanations that help readers extend their capabilities to top-tier SQL programming. Although Celko denies that the book is about database theory, he nevertheless alludes to theory often to buttress his practical points. This title is not for novices, as the author points out. Instead, its intended audience. | 12 CHAPTER 1 DATABASE DESIGN When you use nulls in math calculations they propagate in the results so that the answer is another NULL. When you use them in logical expressions or comparisons they return a logical value of unknown and give SQL its strange three-valued logic. They sort either always high or always low in the collation sequence. They group together for some operations but not for others. In short nulls cause a lot of irregular features in SQL which we will discuss later. Your best bet is just to memorize the situations and the rules for NULLs when you cannot avoid them. CHECK Constraint The CHECK constraint tests the rows of the table against a logical expression which SQL calls a search condition and rejects rows whose search condition returns FALSE. However the constraint accepts rows when the search condition returns TRUE or UNKNOWN. This is not the same rule as the where clause which rejects rows that test unknown. The reason for this benefit-of-the-doubt feature is so that it will be easy to write constraints on NULL-able columns. The usual technique is to do simple range checking such as check rating BETWEEN 1 AND 10 or to verify that a column s value is in an enumerated set such as CHECK sex IN 0 1 2 9 with this constraint. Remember that the sex column could also be set to null unless a not null constraint is also added to the column s declaration. Although it is optional it is a really good idea to use a constraint name. Without it most SQL products will create a huge ugly unreadable random string for the name since they need to have one in the schema tables. If you provide your own you can drop the constraint more easily and understand the error messages when the constraint is violated. For example you can enforce the rule that a firm must not hire anyone younger than 21 years of age for a job that requires a liquorserving license by using a single check clause to check the applicant s birth date and hire date. However you cannot put the .
