Re: expressing one-or-more constraint structurally

"Tony" <andrewst_at_onetel.net.uk> wrote in message news:c0e3f26e.0406280136.67283c76_at_posting.google.com...
> "Marshall Spight" <mspight_at_dnai.com> wrote in message news:<E%fDc.99863$Hg2.52910@attbi_s04>...
> >
> > 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.

Yes, as it turns out, I *was* thinking there was more to it when I made the original post, but the ensuing exchange convinced me that I was imagining things. Your analysis, esp. "Surely a foreign key constraint is just a handy shorthand for a check constraint anyway?" reflects my new, improved thinking.

Behold the clarifying power of discussion! :-)

Marshall Received on Mon Jun 28 2004 - 17:14:36 CEST