foreign key question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

foreign key question

Author

Topic

MirandaJ
Starting Member

35 Posts

Posted - 2008-02-01 : 14:15:17

Let's say I have 3 tables
TableA has a field called IDField this is the Primary Key
TableB has a field called IDField this is the Primary Key
These tables are not related to each other.
The 3rd table TableC has a field called IDField and will hold data from both of the above tables.

I notice that in the database diagram that I can set a foreign key relationship between TableA and TableC on IDField and also
between TableB and TableC on IDField

Is there a problem doing this? Is it possible to have two different tables point to the same column and have a foreign key relationship between them as described above?

Miranda

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-01 : 15:37:24

???
if TableA and TableB both have IDField=1 and TableC has IDField=1 then how do you know which table's foreign key it is (tableA or TableB)?

Be One with the Optimizer
TG

MirandaJ
Starting Member

35 Posts

Posted - 2008-02-01 : 15:52:42

each IDField is set to char(9)

In tableA the IDField begins with 'DY' and is followed by 7 random numbers

In tableB the IDField begins with 'YV' and is followed by 7 random numbers

So dependant on if it begins with DY or YV i will know

Miranda

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-01 : 16:01:11

no, you can't have the same column REFERENCE multiple columns in a FK relationship

Be One with the Optimizer
TG

MirandaJ
Starting Member

35 Posts

Posted - 2008-02-01 : 16:10:17

that is what i thought However, i was able to set it up in the database diagram on my development server showing IDField as a foreign key to each table though. I did not try this in the production server

Miranda

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-01 : 16:24:27

Well, in the example you gave wouldn't the "DY" values in tableC would violate the FK constraint with TableB and the same for the "YV" values in TableC, they would violate the constriant in tableA, right?

Be One with the Optimizer
TG

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2008-02-01 : 16:44:51

I think essentially what she is doing is using a single lookup table for more than one table or a similar scenario.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-01 : 16:52:26

quote:
Originally posted by Van

I think essentially what she is doing is using a single lookup table for more than one table or a similar scenario.

I may be wrong but the way I read the initial question was the opposite. Two lookup tables with a single [values] table.

Be One with the Optimizer
TG

MirandaJ
Starting Member

35 Posts

Posted - 2008-02-01 : 16:58:58

No, two values tables, 1 lookup table

Basically there was very little I could break the value tables down any further on which would have then allowed me to have 1 value table

Miranda

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-02-01 : 17:19:06

Oh, sorry I misunderstood the question.
That model is certainly possible to implement but depending on some factors like: table sizes, concurrent activity, and number of value tables referencing the single lookup table you may have some significant contention issues on the lookup table.

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources