Skip to content

Personal tools
You are here: Home » Of Interest » Articles of Interest » Mop the Floor and Fix the Leak - Part 1
Seeking new owner for this high-traffic site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 3558

Mop the Floor and Fix the Leak - Part 1

by Joe Celko

Part 1  |  Part 2  |  Part 3

At the end of August, there was an interesting posting on a newsgroup that probably reflects the thinking of a lot of bad SQL programmers. The poster was asked about some basic data integrity issue and in the discussion, he replied that all the validation would be done in the front end application program, so we did not have to bother with constraints on the server side.

Golly gee whiz, do you suppose that might be a bad idea? Let’s just list some of the assumptions and situations required for this approach to work.

1) This application and database code will remain the same forever.

Sorry, you are going to port apps and databases in the real world. I don’t just mean that if the application is successful, you will move it to a new database product. Every release of the application language or your same database package is effectively a port. Would you really freeze an application on unsupported software forever? Could you?

2) Nobody else will ever write new code against this database.

That tells us that that nobody else can find any information hidden in the data. That implies that your database is either very specialized and probably should be in a file system built to do this one task, or that your database is useless to the rest of the enterprise.

At the risk of blowing part of my reputation, I still believe that file systems have a place in IT. But not when the data has to be shared and has to maintain data integrity while being shared.

3) If anyone does write new application code against this database, he or she will always get the constraints correct and maintain the data integrity.

Okay, even if this is true today, we must have a policy of only hiring perfect programmers from this time forward. Each of our perfect programmers will always and forever enforce all of the constraints in their application code. And when the specs change, the perfect programmer will be able to find the altered constraints in tens of thousands of lines of code and fix them — perfectly.

Let’s get real, people! When all the integrity is in a thousand applications, all it takes to screw up the entire database is a single statement executed from a simple tool. Think about an UPDATE or DELETE FROM statement with a typo in the SET clause.

4) If several programmers write new application code against this database, they will always get the constraints correct and identical.

Even the best programmers can have different opinions as to the meaning of a spec. For example, a simple rule about not hiring anyone who is under 18 years of age could be read to mean that they must be over 18 at the time of their employment application or that they must be over 18 by their employment starting date.

And even when people agree on the specs, different programming languages and packages do not. They have different rules for rounding, truncation, precision so the same formula does not always give the same results.

There should be one and only one trusted source for the business rules. If the constraints are in the database, and not the application, this kind of question is answered by an error message that pops up in quality assurance testing. If you need to change the rule, then you can change it in one and only one place.

This has nothing to do with databases; it is simply good software engineering.


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

Contributors : Joe Celko
Last modified 2006-01-04 01:10 PM
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!

Powered by Plone