SQL PROGRAMMING STYLE- P35:Im mot trying to teach you to program in SQL in this book. You might want to read that again. If that is what you wanted, there are better books. This ought to be the second book you buy, not the first. I assume that you already write SQL at some level and want to get better at it. If you want to learn SQL programming tricks, get a copy of my other book, SQL for Smarties (3rd edition, 2005). | Put CHECKO Constraint Near what they Check 47 Exceptions Ifyour SQL product has a CREATE DOMAIN statement you will include DEFAULT and CHECK constraints in the domain declaration so the use of the DOMAIN is enough. Multicolumn constraints on columns that are far apart should be moved to the end of the table declaration. This will give you one place to look for the more complex constraints rather than trying to look all over the DDL statement. It can also be argued that none of this really matters because most of the time we should be going to the schema information tables to retrieve the constraint definitions not the DDL. Constraints may have been removed or added with subsequent ALTER statements and the system catalog will have the correct current state whereas the DDL may not. Consider Range Constraints for Numeric Values Rationale The whole idea of a database is that it is a single trusted repository for all of the data in the enterprise. This is the place where the business rules must be enforced. The most common constraint on numbers in a data model is that they are not less than zero. Now look at actual DDL and see how often you find that constraint. Programmers are lazy and do not bother with this level of details. Exceptions When the column really can take any value whatsoever. Consider LIKE and SIMILAR TO Constraints for Character Values Rationale Again the whole idea of a database is that it is a single trusted repository for all of the data in the enterprise. This is the place where the business rules must be enforced. An encoding will have a format that can be validated with a LIKE or SIMILAR TO predicate. Now look at actual DDL and see how often you find that constraint. This is not as portable an option as numeric range checking and many programmers who did not use UNIX in their youth have problems with regular expressions but it is still important. Exceptions When the column really can take any value whatsoever. 48 CHAPTER 3 DATA .
