Part 1  |  Part 2  |  Part 3

In the first part of the series, we considered putting all of the data integrity, verification and validation rules in the front end. In the second part, we considered putting all of the data integrity, verification and validation rules in the database. Neither approach was really workable.

This leads us to the conclusion that some things are best done in the database, some in the applications, and some in both places. Let's make a list:

1) Some things you get for free in SQL. You simply cannot put an invalid date into a temporal data type column; you cannot put alpha strings into a numeric data type column, and so forth.

That sounds obvious to an SQL programmer, but consider that in file systems, a field could hold any string of characters and it was up to the application programs to interpret them.

When you see a newbie who is using "NVARCHAR(<big number>)" for his columns regardless of the datatype that should be used, he is actively trying to roll back the clock to a punch card file system.

2) Some things you get cheap in SQL. The DEFAULT clause will provide a value when the front end does not provide an explicit one. A common error is not making the DEFAULT value the same data type as the column. This is confusing to the maintenance programmers and leads to needless type casting.

The application programs probably ought to use the DEFAULT option instead of providing an explicit default value in their code. This way, if the default changes, they do not have to dig through all their code and replace their hardwired value. Likewise, using the CURRENT_TIMESTAMP in SQL is probably better than getting the current date in the application.

3) Some things you get at cost in SQL. The use of primary keys, unique constraints, foreign keys and referential actions has some overhead. How much overhead depends on the SQL product, of course. How to write DDL that takes advantage of these features is a series of articles in itself.

I might not want to have a check digit constraint everywhere in the database where I use a particular code. If the code is a foreign key in several tables, the validation needs to be done only on the table where it is a primary key.

4) Some things you get with a little effort in SQL. This is where the CHECK() constraints come in. You can do a lot with CHECK constraints because SQL is very rich in predicates.

The most common mistake is assuming that the schema will understand something because it is obvious to you. The biggest violation is not having "CONSTRAINT x_must_be_positive CHECK (x >= 0)" on columns that are obviously always positive; not having "CONSTRAINT x_must_have_text CHECK (CHAR_LENGTH (x) >= 0)" on columns that obviously always have text in them; etc.

Now the big question: Should the front end repeat the same validations as the database? The general rule is that you mop the floor and fix the leak. The puddle is the application side and the leak is the database side. The database will be the final, trusted authority since it will reject rows that violate constraints.

When an application is doing edits on data that it gets from the database, there is something wrong. It should expect to be given clean data. For example, the front end should not be trimming out redundant blanks and capitalizing street addresses that it gets from the database.

The real shame in most shops is when the application programmers find that they are getting dirty and have to mop it up there is no mechanism for them to report it to the DBA and get him to fix the leak.

--

Joe Celko was a member of the ANSI X3H2 Database Standards Committee and helped write the SQL-92 standards. He is the author of over 750 magazine columns and seven books, the best known of which is SQL for Smarties, now in its third edition. For more articles, puzzles, resources, and more, go to www.celko.com.