# Re: Constraints and Functional Dependencies

Date: Tue, 06 Mar 2007 16:02:46 GMT
Message-ID: <GggHh.7031\$PV3.63140_at_ursa-nb00s0.nbnet.nb.ca>

Marshall wrote:

```>>Marshall wrote:
>>
>>>...
>>>With such a system, a relation R with attribute a (which I will
>>>write as R(a)) having a as a foreign key into S(b) is expressed
>>>as follows:
>>
>>>  forall R(a): exists S(b): a = b
>>
>>>So we can express foreign keys this way.
>>>...
>>
>>I presume that if S had other attributes besides b, this definition
>>would mean that b doesn't need to be a so-called primary key?  (That
>>would be okay with me.)
```

>
> Yeah. You'd need another constraint to express that, and I don't think
> it would be possible to express the constraint that foreign keys must
> reference primary keys, because that would be a second order
> formula: it quantifies over formulas.
>
> The system itself might have an implementation constraint, but
> off the top of my head I don't see why it would need one.
>
> Of course, in practice I don't think one is likely to want to
> reference
> anything that isn't a key.

The whole issue of whether one wants to reference non-keys is artificial in the context of your original premise: namely a language that allows only wff's for integrity constraints.

Primary keys, candidate keys and foreign keys are shorthands for wff's with two principal benefits: 1) an abbreviated way of expressing important, frequently used constraints and 2) an opportunity for the dbms to perform additional semantic checks.

By defining the shorthands out of existence, you defined the benefits out of existence.

The foreign key reference is a stricter constraint than the wff. The foreign key shorthand says: "Each value in this relation must exist in some other relation as a candidate key."

In the situtation where one wants to enforce a reference to a non-key, what one really wants to says is: "This value must exist one or more times in some other relation" and the wff already exactly mirrors what one wants to say. Given the relative infrequency of the need, one doesn't benefit from a shorthand.

If one declares a foreign key reference to a non-key, the dbms should indicate an error. One can then correct the likely mistake or much less likely if one has the infrequent case, one can re-write the constraint as a wff. Received on Tue Mar 06 2007 - 17:02:46 CET

Original text of this message