Part 1  |  Part 2  |  Part 3

In part one of this article, we talked about how it is absurd to put all of the data integrity, verification, and validation rules in the front end application programs, so we would not have to bother with those constraints on the server side.

The converse of this would be to put the constraints only on the database. This approach will actually work better than the first one! Every front-end program will be tested against an identical set of constraints, and will get an identical set of error messages back. This is good and desirable. So, what is wrong with it?

Remember how we had to postulate perfect programmers in the first part of this series for application-only constraints to work? Now we need to make a leap of faith and postulate perfect users who never input bad data for back-end only constraints to work. Without a perfect user, the overhead of going back and forth between the application and the database can kill a system.

When you write SQL in a hurry, you often forget that there is a “CONSTRAINT <name>” clause that can be added to any constraint. It gives the constraint a name that will appear in error messages. If you do not use this clause, most SQL products will generate an ugly, random string for a name that no human being can understand or use. Your constraint name can be quite long in modern SQL products. You could actually send back an error message with the name, like:

TABLE: Personnel
COLUMN: street_address
CONSTRAINT VIOLATION: extraneous_blanks_in_street_address

Let’s be honest, the user is more apt to see:

TABLE: Personnel
COLUMN: street_address
CONSTRAINT VIOLATION: CC-124323423-LNMLIKTR-CSSRE-12323543X

Very helpful message, isn’t it?

Finding input errors one at a time like this is what we called “The Wright Brother School of Systems Engineering” in the 1970’s — put it all together, push it off a cliff and see if it flies. Go to the crash site, fix one and only one problem, put it all together, push it off a cliff and see if it flies.

Another real problem with database side constraints is that we want to lump them into one constraint. Using my street address example, let’s assume that I want to avoid extraneous blanks and capitalize each word in that string. If I have a regular expression function in my SQL, (Note: The regular expression function in Standard SQL is called SIMILAR TO and is based on POSIX) I can probably write one long regular expression inside a CHECK() constraint that will preclude extraneous blanks and also use leading lowercase letters in that string. But the name I give the constraint will not pin down the exact input error. I would be better off with one constraint for extraneous blanks and one for improper capitalization, so as to get more precise error messages.

One complaint that I have had with putting lots of constraints in the database is that the table declarations get big and look ugly. I simply cannot buy that as a reason not to use constraints. An application program can easily run to hundreds of lines of code, so mere size should not be a problem. The database is the common source of data for all those applications, so writing most of the logic in one place is a tremendous savings in space and total effort.

I think the real reason is that new database programmers do not like to do a complete specification at the start. It is much easier to throw the problems over the wall to the applications side of the house.

--

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.