Re: foreign key constraint versus referential integrity constraint

From: paul c <toledobythesea_at_oohay.ac>
Date: Thu, 22 Oct 2009 16:45:33 GMT
Message-ID: <N_%Dm.48969$Db2.30675_at_edtnps83>


Mr. Scott wrote:
...
> A foreign key constraint is an inclusion dependency and an inclusion
> dependency is a referential constraint, but there are referential
> constraints that are not inclusion dependencies and there are inclusion
> dependencies that are not foreign key constraints. For example, a
> constraint that states that if there is a row in one table there cannot be a
> corresponding row in a different table is neither an inclusion dependency
> nor a foreign key constraint but is still a referential constraint
> nonetheless. ...

Tempts me to use a new term (at least I'm guessing it's new) - exclusion dependency, even if there is nothing new about what it connotes. I think it is hard to separate the influence of the typical dbms product that doesn't allow one to express all the possibilities that an algebra allows (maybe easier for me being quite ignorant of most products). This makes talk of theory harder so most people end up habitually confusing various product documentation with theory. Personally, given that any dbms is likely to have a number of practical limitations, I don't see why a dbms couldn't allow restricted use of negation so that a foreign 'reference'/exclusion dependency might be read as "A{attr} = A{attr} AND (NOT B{attr})", which is basically of the same form as any inclusion dependency or what I call a reference. (Any dbms that has a 'delete' operator already supports similar negation implictly.) When people talk of referential integrity I suspect that they are usually talking about such an equation. Also suspect that we have a number of qualified names for that basic form (of which 'primary key' was apparently the first) simply because using the terms in a dbms' language makes it easy for implementers to physically optimize. IMHO the implementation artifacts don't really contribute to, nor involve, any essential theory except for the usually ignored theory of optimization.

(I once read an interview of Codd where he more or less acknowledged that in his first two papers he was struggling to find the best terms to use. Eg., he said that the American 'normalization' of relations with China inspired him to use same term. I've read where CJ Date hadn't heard of this, but I recall the interview, probably from a magazine called 'DBMS' around 1994.) Received on Thu Oct 22 2009 - 18:45:33 CEST

Original text of this message