Re: Enforcing functional dependecy constraints

From: paul c <toledobythesea_at_oohay.ac>
Date: Mon, 12 Dec 2005 17:06:47 GMT
Message-ID: <H6inf.93094$ki.54920@pd7tw2no>

x wrote:
> "David Cressey" <david.cressey_at_earthlink.net> wrote in message
> ...
> This is the standard theoretical decomposition.
> How the AB->C is enforced ?
>
>

>>Do you have a different answer?

>
> Yes.
> a)R(A,B,C) with (A,B) primary key, (B,C) foreign key into T and T(B,C) with
> C primary key.
> b)S(A,C) and T(B,C) with some triggers
> c)R(A,B,C) with some triggers
> d) S(A,C) and T(B,C) with R(A,B,C) a view with some unique index on it
> e) ...

It would be nice to avoid b) and c), in order to avoid triggers.

I think d) is a problem because because producing the view R could fail with a 'duplicate' (A,B) value. How would one decide which value to use unless there were 'inter-table' constraints/triggers on S and T?

1. would appear to preserve both FD's. However, T's key is C. Don't most current SQL products require the foreign key (B,C) to be 'primary', ie., to be primary in T?

If the above is right, does it mean that most or all current SQL products would force one to use triggers? That seems a shame to me. Maybe some of them allow a foreign key to reference a superkey which would seem to work for this case. I've seen it argued that the referenced value ought not to be limited to a key in the first place, which makes sense to me.

A quick search seems to indicate that sql server wants a primary key reference, db2 wants a candidate key reference. I'm not sure about Oracle. Mysql seems to want not only a candidate key, but an index on both tables which seems bizarre on the face of it. All of them seem to muddle the notion of a key with physical indexes.

If I'm seeing this right, that would explain why there seems to be so much talk of triggers and elaborate constraints in the SQL world.

Personally, I think I like a)'s schema the best but I think that implementing such a basic set of facts and FD's is more complicated than

   it ought to be.

cheers,
p Received on Mon Dec 12 2005 - 11:06:47 CST