Foreign key referring to primary keys across multiple tables?
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!
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 <——————— empidname /|\ name | | deductions | —————————— | empid ————————+ name
type
in the employee table would be ce
or sn
.
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 & 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 GOALTER TABLE [dbo].[Deductions] NOCHECK CONSTRAINT [FK_600_WorkComments_employees_ce]GOALTER 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 GOALTER TABLE [dbo].[Deductions] NOCHECK CONSTRAINT [FK_600_WorkComments_employees_sn]GOemployees_ce--------------empid name tidkhce1 prince 1employees_sn----------------empid name tid khsn1 princess 2deductions----------------------id tid name khce1 1 goldkhsn1 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.