Re: Distributed foreign keys (was Re: Category Types)

"Bob Badour" <bbadour_at_golden.net> wrote in message news:rQIJa.388$Z_1.57198698_at_mantis.golden.net... [snip]
> Did you not say: "In other words, we should have a shorthand for ... Every
> row in CALLED has *at most* one matching row in either DOES_JOB or
> UNEMPLOYED." ?
>
> Does the above not equate to a foreign key constraint where every value
> references zero or one rows in DOES_JOB or UNEMPLOYED?
>
> Does a reference to
> zero or one rows not amount to the same thing as saying a value might or
> might not reference a tuple in some relation? What am I missing?

Sorry. You are right. I was reading Hugh's "distributed foreign key" the wrong way around - thought it was just a shorthand for defining the 3 FKs (6,7, & 8)
If we don't hold the UNK information (the two tables one views scenario), then a "distributed key" and plain FKs enforce our constraints.

Still I do think our current set of constraint shorthands - Keys, Foreign Keys and now Distributed Keys and Distributed Foreign Keys - is rather stingy.

For one, Distributed Foreign Keys, could be extended for the case when the referenced table(s) can have multiple tuples with the same DFK value.

Of course, a real nice RDBMS would allow users to create their own shorthands.

P.S. does anyone share my dislike of the term 'Foreign Key'? to me it's a misnomer, for one they are not Keys (the FK columns do not have to be unique) and if "Foreign" is meant to imply that *all* the values in the PK are also in the FK then that is wrong also. Plus I always spell foreign wrong (i.e. foriegn)

> A distributed key over DOES_JOB and UNEMPLOYED already states that every key
> value appears at most once in the two relations.

Agreed.

> Or are you suggesting a
> constraint that would allow the value to appear multiple times in DOES_JOB
> and UNEMPLOYED as long as no referencing value appears in CALLED?

No.

Regards
Paul Vernon
Business Intelligence, IBM Global Services Received on Tue Jun 24 2003 - 15:40:58 CEST