relationship between tables

I need to create a relationship between 3 tables,say x,y,z. y and z being th
e
parent tables to x. there's an id field in all the 3 tables. i need to creat
e
a relationship between these 3 tables such that the id field in x is related
to either y or z. in other words, sometimes the id field in table y should
act as the primary key constraint and some other times the id field in table
z should act as the primary key constraint.
Is it possible? if so,how?
Thanks!"HP" <HP@.discussions.microsoft.com> wrote in message
news:C58E1008-C4C2-47A3-B6CE-396BFE08AE27@.microsoft.com...
>I need to create a relationship between 3 tables,say x,y,z. y and z being
>the
> parent tables to x. there's an id field in all the 3 tables. i need to
> create
> a relationship between these 3 tables such that the id field in x is
> related
> to either y or z. in other words, sometimes the id field in table y should
> act as the primary key constraint and some other times the id field in
> table
> z should act as the primary key constraint.
> Is it possible? if so,how?
> Thanks!
Gakkk!
While you could probably figure out a way to do this, why in the world do
you want to break normalization rules like this.
Bad design with tend to lead to poor performance and a host of
maintainability issues. Relational design and normalization is based on
proven mathematical formulas. Violate the formulas and you bring integrity
issues in to the system.
Rick Sawtell
MCT, MCSD, MCDBA|||Seems like you are missing a table. Create a fourth table to act as the
parent of all the other three.
David Portas
SQL Server MVP
--|||No it is not. A foreign key references one other table. Any other method
for referencing other than DRI is a kluge.
You should have y,z, x1 and x2, where y is the parent of x1 and z is the
parent of x2.
This is not redundant data, by the way. The x's are separate entities,
each describing similar "child" data of separate "parent" entities.
HP wrote:

>I need to create a relationship between 3 tables,say x,y,z. y and z being t
he
>parent tables to x. there's an id field in all the 3 tables. i need to crea
te
>a relationship between these 3 tables such that the id field in x is relate
d
>to either y or z. in other words, sometimes the id field in table y should
>act as the primary key constraint and some other times the id field in tabl
e
>z should act as the primary key constraint.
>Is it possible? if so,how?
>Thanks!
>