Re: Declaring Unenforced Constraints

Laconic2 wrote:

> There's a feature I would like to see in today's RDBMSes. It's the
> ability
> to declare a constraint to be true, but to tell the DBMS not to bother
> enforcing it. I'm talking here about SQLRDBMSes, and probably TRDBMSes as
> well.
>
>
> Your turn.

Well, the language police are going to come after us if we talk about "unenforced constraints", no? I don't know what to call a behavior that is not known to hold at all times, so just for fun I'll call it a "tendency".

Proposition: Employees tend to be unique.

We can then speak of the "confidence" we have in a tendency. If we have 100% confidence in a tendency, then it is a constraint. We can have 0% confidence, 100% confidence, or anything in-between.

Well, let's start with a partial list of constraint types to support.

1. unique
2. referential
3. column comparison (column a <= column b)
4. lookup comparison (column a <= UDF_GetSysVar("some_parameter"))

We'll let complex constraints be pursued by those who cherish them more than I.

For unique tendencies, it seems that you can only allow 100% confidence in uniqueness tendencies. Am not sure what a partially enforced constraint would do here. Do you have any examples of semi-unique entities?

For referential, it comes up here and there that a column can be null or empty, but if populated it must match the primary key of the parent table. Is this an example you had in mind?

For either type of comparison, we find some interesting ideas. These are fully in the domain of business rules, beyond the realm of structure. Once you relax these you are really saying that the business rules are recommendations rather than absolutes. So for instance, credit limit may be used as a constraint, to reject orders, or as a flag to warn the typist that the customer is over credit limit. This is intriguing.

As for the authoritative outside copy, it would have to have a column to denote the confidence required of a constraint.

-- 
Kenneth Downs
Use first initial plus last name at last name plus literal "fam.net" to
email me