Re: Surrogate primary key plus unique constraint vs. natural primary key: data integrity?

On Wednesday, March 13, 2013 10:18:37 AM UTC-7, Wolfgang Keller wrote:
> I just had to find the right keywords to search for, in this case it's
> the term "overlapping foreign keys". It's actually addressed in some
> textbooks.

Beware that "overlapping foreign keys" is just a special case of introducing constraints with a surrogate key. Here what you are doing can require a constraint even if there is no other foreign.

Eg. Suppose you have S{a,...} and T{x,...} with constraint (among others) S(a,...) IMPLIES T(f(a),...). Ie for row s in S EXISTS t in T [f(s.a)=t.x]. You add surrogate k as key in S and fk in T to get S{k,a,...} and T{k,x,...}. Then the new constraint is S(k,a,...) IMPLIES T(k,f(a),...). Ie for row s in S EXISTS t in T [f(s.a)=t.x AND s.k=t.k]. So in the SQL style you have to add the "AND s.k=t.k". This is independent of whether S origninally had any fk into T.

To not miss any cases yet be mechanical about it you have to do what I said in my first message. Determine:
> corresponding new/old table variable membership conditions, values of table variables and query results, and query and constraint expressions (including literals).

philip Received on Wed Mar 20 2013 - 19:31:56 CET