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

Answer 1

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!