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

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

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.

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).

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:

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 & tid_sn)

2. Create a Unique Index on each table using the table's PK and the table id field.

3. Add a tinyint field to your 'Deductions' table to store the second half of the foreign key (the Table ID)

4. 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.

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 —————————> empid <——————— empid
name               /|\          name
                    |  
                    |  
      deductions    |  
      ——————————    |  
      empid ————————+  
      name

type in the employee table would be ce or sn.

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!

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).

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