Re: Database wide constraint?

Emily Jones wrote:
> I have table 1, whose primary key may be used in table 2 table 3 .... table
> n, as a foreign key.
>
> Is there any constraint that can applied, hopefully once and in one place,
> that ensures that for any particular row in table 1, it's value is used in
> only one other table (though it may be used many times in that one other
> table)?
>
> Thanks, Emily

To paraphrase Hugh Darwen's succinct key constraints:

A key

   ...value is one that must exist no more than once    in the attribute for which the key is declared.

A foreign key

   ...value is one that must exist as a key value in    the referenced attribute if it exists in the    referencing attribute for which the foreign key is    declared.

A distributed key

   ...value is one that must exist no more than once    in the attributes (in different relations) for    which the distributed key is declared.

A foreign distributed key

   ...value is one that must exist as a key value in    the referenced attribute (in different relations)    if it exists in the referencing attribute for which    the foreign distributed key is declared.

I believe you're looking for distributed key support and I know of none. The lack of such prompted me to concoct my own declarative model language for which I wrote something that generated SQL model language and triggers necessary to enforce the key constraints listed above. Although triggers are completely unappealing, I convinced myself that they were okay as long as they were generated from some declarative language and were the only triggers in the design. Received on Wed Aug 09 2006 - 10:47:58 CEST