SQL Clone
SQLServerCentral is supported by Redgate
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: 93 | Views in the last 30 days: 1
Related Articles

Check Constraint Person Relations

Triggers - Constraints


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...


Modeling relational databases

Modeling relational databases


Using Super Keys to Enforce Database Constraints

Example of Using Super Keys to Enforce Database Constraints, instead of procedural code


Synchronizing Databases Across Domain Boundries

Use Service Broker to sync a database on opposite sides of untrusted domains using end-point certifi...

database weekly