Sql – Foreign key referring to primary keys across multiple tables

foreign-keysprimary-keysql

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

Best 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!

Related Topic