Re: Declaring Unenforced Constraints

"Kenneth Downs" <firstinit.lastname_at_lastnameplusfam.net> wrote in message news:qdbgmc.bdp.ln_at_192.168.10.210...

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

My attitude towards the language police is the same as my attitude towards the sensitivity police: _at_#$% 'em.

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

The distinction between "tendencies" and "contraints" reminds me, once again, of the Shannon formulas for information and uncertainty. A signal comes off the read head, and we say that there's a 70% chance that it's a one, and a 30% chance that it's a zero. We have some information, but less than one bit's worth.

If we allow "tendencies" to influence the optimizer we run two very different kinds of risks:

We may sometimes choose strategies that take too long to complete or we may sometimes choose strategies that deliver wrong results. There are times when either of these are acceptable. But in most database work, anything less than 100%
correctness out of queries is judged to be a sign of a defect that can and should be corrected.

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

Add one more: range limitation.
>
> We'll let complex constraints be pursued by those who cherish them more
than
> I.
>

Agreed.

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

Sure, first name and last name among students. If you know the first name and the last name (including spelling), but you don't know the student ID, the user interface should let you take a shot at it, or shouldn't it?

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

This is the kind where "for performance reasons" is most likely to be bogus. Received on Fri Nov 05 2004 - 19:33:27 CET