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.
| Author |
Topic |
|
anomie
Starting Member
16 Posts |
 Posted - 2008-11-07 : 20:50:08
|
| Hi there!I apologise if i am posting in the wrong directory, but i need a solution FAST!!Ive come accross a slight problem. I have an existing table named 'bedroom' and it has one foreign key. The FK has been named 'FK_bedroomLoc'. The problem is that the FK needs to reference two independent columns in two sperate tables. The first table is called 'hall' (column ID:'locNo') the other, 'student_flat' (column ID:'flatNo'). The 'bedroom' table stores a list of all bedrooms available from both 'hall' and 'student_flat' tables. The reason i need a FK to these tables is because i have a 'location' column in the 'bedroom' table which references the bedroom location, be it from 'hall' (column ID:'locNo') or 'student_flat' (column ID:'flatNo').Is it possible to create two references on the fly?Any help on this topic will be GREATLY appreciated!Thanx! |
|
|
PeterNeo
Constraint Violating Yak Guru
357 Posts |
Posted - 2008-11-08 : 01:20:46
|
No, U can't use same FK to reference two different columns(of different tables) at the same time. Instead of this While inserting the "location", u can check the existence of the same in both tables based on "Type""There is only one difference between a dream and an aim. A dream requires soundless sleep to see, whereas an aim requires sleepless efforts to achieve..!!" |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-11-08 : 01:31:31
|
You cant define two foreign keys on same column to link to two tables. I think as a workaround what you can do is to create two columns halllocation & flatlocation instead of location. then link halllocation to 'hall' (column ID:'locNo') via a foreign key and also link flatlocation to 'student_flat' (column ID:'flatNo') by means of another foreign key. This ensures both the columns will have only allowed values from master table. Now to make sure you've either one of columns having a value in each record of bedroom create a check constraint to ensure either of them is not null. so it will be likeCONSTRAINT FK_bedroomFlatLoc FOREIGN KEY (flatlocation)REFERENCES student_flat(flatNo), CONSTRAINT FK_bedroomHallLoc FOREIGN KEY (halllocation)REFERENCES hall(locNo) CONSTRAINT CK_bedroomLocationHallOrFlatCHECK ( (flatlocation IS NULL AND halllocation IS NOT NULL)OR (flatlocation IS NOT NULL AND halllocation IS NULL) ) |
|
|
|
PeterNeo
Constraint Violating Yak Guru
357 Posts |
Posted - 2008-11-08 : 01:37:36
|
if u do really want a single column with Location details, then u can use a Computed column with condition like ISNULL(flatlocation, halllocation)."There is only one difference between a dream and an aim. A dream requires soundless sleep to see, whereas an aim requires sleepless efforts to achieve..!!" |
|
|
|
|
|
|
multiple foreign key references