Foreign key referring to primary keys across multiple tables

Question

I have to two tables namely employees ce and employees sn under the database employees    They both have their respective unique primary key columns    I have another table called deductions  whose foreign key column I want to reference to primary keys of employees ce as well as employees sn  Is this possible   for example  employees ce -------------- empid   name khce1   prince  employees sn ---------------- empid   name khsn1   princess   so is this possible   deductions -------------- id      name khce1   gold khsn1   silver

User · Answer

Technically possible  You would probably reference employees ce in deductions and employees sn  But why don t you merge employees sn and employees ce  I see no reason why you have two table  No one to many relationship  And  not in this example  many columns   If you do two references for one column  an employee must have an entry in both tables

User · Answer

Assuming you must have two tables for the two employee types for some reason  I ll extend on vmarquez s answer   Schema   employees ce  id  name  employees sn  id  name  deductions  id  parentId  parentType  name    Data in deductions   deductions table id      parentId      parentType      name 1       1             ce              gold 2       1             sn              silver 3       2             sn              wood       This would allow you to have deductions point to any other table in your schema   This kind of relation isn t supported by database-level constraints  IIRC so you ll have to make sure your App manages the constraint properly  which makes it more cumbersome if you have several different Apps services hitting the same database

User · Answer

Assuming that I have understood your scenario correctly  this is what I would call the right way to do this   Start from a higher-level description of your database  You have employees  and employees can be  ce  employees and  sn  employees  whatever those are   In object-oriented terms  there is a class  employee   with two sub-classes called  ce employee  and  sn employee    Then you translate this higher-level description to three tables  employees  employees ce and employees sn    employees id  name  employees ce id  ce-specific stuff  employees sn id  sn-specific stuff    Since all employees are employees  duh    every employee will have a row in the employees table   ce  employees also have a row in the employees ce table  and  sn  employees also have a row in the employees sn table  employees ce id is a foreign key to employees id  just as employees sn id is   To refer to an employee of any kind  ce or sn   refer to the employees table  That is  the foreign key you had trouble with should refer to that table

User · Answer

You can probably add two foreign key constraints  honestly  I ve never tried it   but it d then insist the parent row exist in both tables   Instead you probably want to create a supertype for your two employee subtypes  and then point the foreign key there instead   Assuming you have a good reason to split the two types of employees  of course                     employee        employees ce                                    employees sn                                          type                                                empid                             gt  empid  lt                       empid name                            name                                                       deductions                                                          empid                                   name   type in the employee table would be ce or sn

User · Answer

I know this is long stagnant topic  but in case anyone searches here is how I deal with multi table foreign keys   With this technique you do not have any DBA enforced cascade operations  so please make sure you deal with DELETE and such in your code     Table 1 Fruit pk fruitid  name 1  apple 2  pear  Table 2 Meat Pk meatid  name 1  beef 2  chicken  Table 3 Entity s PK entityid  anme 1  fruit 2  meat 3  desert  Table 4 Basket  Table using fk s  PK basketid  fk entityid  pseudo entityrow 1  2  2  Chicken - entity denotes meat table  pseudokey denotes row in indictaed table  2  1  1  Apple  3  1  2  pear  4  3  1  cheesecake    SO Op s Example would look like this  deductions -------------- type    id      name 1      khce1   gold 2      khsn1   silver  types --------------------- 1 employees ce 2 employees sn

User · Answer

Yes  it is possible  You will need to define 2 FKs for 3rd table  Each FK pointing to the required field s  of one table  ie 1 FK per foreign table

User · Answer

Actually I do this myself  I have a table called  Comments  which contains comments for records in 3 other tables  Neither solution actually handles everything you probably want it to  In your case  you would do this   Solution 1    Add a tinyint field to employees ce and employees sn that has a default value which is different in each table  This field represents a  table identifier   so we ll call them tid ce  amp  tid sn  Create a Unique Index on each table using the table s PK and the table id field  Add a tinyint field to your  Deductions  table to store the second half of the foreign key  the Table ID  Create 2 foreign keys in your  Deductions  table  You can t enforce referential integrity  because either one key will be valid or the other   but never both   ALTER TABLE  dbo   Deductions   WITH NOCHECK ADD  CONSTRAINT  FK Deductions employees ce  FOREIGN KEY  id    fk tid   REFERENCES  dbo   employees ce    empid    tid   NOT FOR REPLICATION  GO ALTER TABLE  dbo   Deductions  NOCHECK CONSTRAINT  FK 600 WorkComments employees ce  GO ALTER TABLE  dbo   Deductions   WITH NOCHECK ADD  CONSTRAINT  FK Deductions employees sn  FOREIGN KEY  id    fk tid   REFERENCES  dbo   employees sn    empid    tid   NOT FOR REPLICATION  GO ALTER TABLE  dbo   Deductions  NOCHECK CONSTRAINT  FK 600 WorkComments employees sn  GO  employees ce -------------- empid    name     tid khce1   prince    1  employees sn ---------------- empid    name     tid  khsn1   princess  2  deductions ---------------------- id      tid       name   khce1   1         gold khsn1   2         silver             id   tid creates a unique index       Solution 2  This solution allows referential integrity to be maintained  1  Create a second foreign key field in  Deductions  table   allow Null values in both foreign keys  and create normal foreign keys       employees ce     --------------     empid   name     khce1   prince       employees sn     ----------------     empid   name          khsn1   princess       deductions     ----------------------     idce    idsn      name       khce1    NULL     gold      NULL   khsn1     silver            Integrity is only checked if the column is not null  so you can maintain referential integrity

[sql] Foreign key referring to primary keys across multiple tables?

Examples related to sql

Examples related to foreign-keys

Examples related to primary-key