Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Voluntary Constraint?

By Phil Factor,

"The more constraints one imposes, the more one frees one's self of the chains that shackle the spirit."
                  Igor Stravinsky (1882 - 1971), Poetics of Music

Domain Constraints are such a fundamental part of the relational database that it always takes me by surprise when someone tries to argue that they aren’t necessary. If I had to sacrifice a feature of relational databases, it wouldn’t be domain constraints. These defend the database against bad data by ensuring that every element from a relation conforms to the type and restrictions of its corresponding attribute: They are simple logical expressions that can check that the data is valid for that relation. Yet so often I’m told by despairing DBAs that integrity constraints aren’t being used in their applications.

An interesting article over on Simple-Talk,‘Constraints and the Test-Driven Database’, was published this week, and provoked some interesting discussion, including wistful sentiments that domain constraints were ‘not allowed’ by the developers.. The idea that Domain constraints could in any way be an ‘optional extra’ to a relational database is surely wrong. You leave them out at your peril.

Defense in depth is the only way to design a database properly. Bad data has an uncanny knack of insinuating itself into tables, especially after the developers have sworn on the bones of their ancestors that such a thing could never happen with all the input validation that is done in an application. It takes me back to those far-off days I spent as a systems programmer. As part of defence in depth, I learned to put in checks for all sorts of impossible conditions of the data. I think it was Dennis Ritchie who described how he would always put in an error handler into the default condition of a switch statement that could not be reached, with the message ‘This is impossible’. How often, he mused, he saw that error message. The same is true of data in tables; Nature seems to abhor a clean table. A colleague once described it to me as being like the froth on beer, you sort a table of ,for example, customers, only to find at the top or bottom, names that are worse than ‘Little Bobby Tables’, email addresses that would be illegal even for a Vogon, and phone ..er.. letters rather than numbers. It seems to happen even in the well-ordered universe, and it is far more likely to happen without constraints being in place.

Total article views: 85 | Views in the last 30 days: 2
 
Related Articles
FORUM

Check Constraint Person Relations

Triggers - Constraints

ARTICLE

Stairway to Database Design Level 2: Domains, Constraints and Defaults

A clear understanding of SQL Data Types and domains is a fundamental requirement for the Database De...

FORUM

Modeling relational databases

Modeling relational databases

FORUM

corrupted relations in database

corrupted relations in database

ARTICLE

Relational Database Without Relations

The strength and holy grail of relational databases lies in the very name: relations. Microsoft has ...

Tags
database weekly    
editorial    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones