Re: OT: Stupid SQL Question

[Leonardo Quijano Vincenzi]

AFAIK this is not legal and foreign keys must map to a specific table.
Usually this case would get handled by an association table.

--
Ing. Leonardo Quijano Vincenzi
Director Técnico
DTQ Software


Patrick Casey wrote:

            Ok, I admit it, this is probably a stupid question, but I can't
(curiously) seem to find an answer to this in the SQL spec.

            Is it legal for a child table (the n side of a 0..n)
relationship to have a foreign key relationship to *more than one* parent
table?

            e.g.

            is this legit:

            family

            ---------

            id

            name

            company

            -------

            id

            name

            cars

            --------

            id

            owner_class (either family or company)

            owning_id (foreign_key company(id)), foreign_key family(id))?

            In that either a family, or a company, might potentially own a
car?

            I'm actually not using cars, of course, it's an attachment table
that currently all my tables share (any attachment to any object, gets
stuffed into attachment).

            Naturally, mySQL lets me get away with it, but then mySQL lets
you get away with *anything* so my question is, should I refactor this now
on the expectation that some day I may need to use a real database? Or is
having multiple foreign key constraints on the same column copasetic under
the ANSI spec?

            --- Pat

---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]