I share with Joe Celko an irritation with seeing tables without their obvious constraints. If there is a general rule about data, it is that mistakes creep in unexpectedly and unpredictably. Mistakes in data seem to lead inexorably to ghastly errors in reports, in much the same way that rabbits seem to run straight for the wheels of your car. However, the SQL Standard shouldn't make it so unreasonably hard to maintain rules of data consistency in relational databases. CHECK constraints are rather clunky to use.

Sybase, followed by SQL Server, introduced the RULE to get around some of the awkwardness of CHECK constraints. A RULE allows you to bind rules about data to alias data types as well as columns. They have been 'deprecated' since SQL Server 2005, but these die-hard routines are still there in SQL Server 2014, mainly because there is no sensible alternative to them.

Compared with a RULE, a CHECK constraint is a primitive beast. I once wrote an automatic conversion utility to remove RULEs and replace them with constraints, but the result merely emphasized how messy it was to achieve the same thing with constraints, and how difficult it was to maintain such a system.

I believe that a 'rule' should only appear once for a particular specialized 'alias' data type. Maybe you are dealing with a specialized value, we'll call it a 'smidgen'. In our example, it has a very small, approximate value which can be used as an attribute to a number of unrelated entities. If we use a RULE, we can create an alias data type, using FLOAT, and enforce a range between two small positive values. With a constraint, we have to put it separately on every column, in every table, in which the smidgen appears. Then we discover that we'd mistakenly made the upper bounds too low so that valid data was being rejected. Would you rather alter the rule in one place, or alter umpteen constraints in tables? Umpteen is a very approximate number greater than ten, by the way.

There are problems with rules. Unlike constraints, they don't check existing data and can't be temporarily disabled. The biggest problem is that they aren't part of the SQL standard. Although one's first instinct is to comply, one can't help holding back and wondering if it is worth the gamble of waiting for the SQL Standard to come up with a better alternative than the CHECK constraint, and hoping that Microsoft don't ever lose their nerve and remove the RULE.

Phil Factor.