Re: expressing one-or-more constraint structurally

"Marshall Spight" <mspight_at_dnai.com> wrote in message news:<E%fDc.99863$Hg2.52910_at_attbi_s04>...
> Hi all,
>
> When one wants to express a cardinality constraint on one item
> vs. another, being able to do it structurally is the best approach.
> The most basic example is one-to-many. If, for each a in A, you
> have 0 or more b in B, then have B have, in addition to its own
> primary key, a foreign key to A.
>
> If each a:A has zero-or-one b:B, then have B have an attribute
> that is both the primary key of B and a foreign key to A.
>
> What if one wants to express a one-or-more constraint?
> For each a:A there exists at least one b:B? One could always
> write a check constraint, but that is not as desirable as a
> structural way to express the same.
>
> Any ideas?

I'm not clear about what you mean by "structural" here. Surely a foreign key constraint is just a handy shorthand for a check constraint anyway? The check constraint would be something like:

CHECK (EXISTS (SELECT * FROM A WHERE B.A_ID = A.A_ID)) So are you saying that you wish there was also a shorthand for the check constraint to enforce the one-or-more constraint also? Apart from convenience, what benefit would this give? Your use of the word "structural" implies there is more to it than just a syntax shorthand. Received on Mon Jun 28 2004 - 11:36:36 CEST