Multiple foreign keys to the same table in a database?

Question

I have a SQL Server database and it contains a table to record a employee salary.

It has 3 columns declared as foreign keys, and reference to the employee table's column, employee_id:

• employee_id
• submitted_by
• confirmed_by

But is it best practice to make it all as FK, or do I only need employee_id?

Because in my application, submitted_by and confirmed_by will be selected by a drop down list and assume it exist on employee table.

Thanks you for advice.

Answer 1

Yes, since all users of your system are also Employees modelled by your system, if you wish to have Referential Integrity (RI) enforced in the database, all three columns should have foreign keys back to the referenced employee table. Note that since confirmed by sounds like part of a workflow process, where the user confirming may not be available at the time the record is inserted, you can make the field confirmed_by in table EmployeeSalary nullable (confirmed_by INT NULL), in which case RI will only be enforced at the later time when the field is actually populated.

You should name each of the foreign keys appropriately by expressing the role in the foreign key, e.g.

• FK_EmployeeSalary_SalariedEmployee
• FK_EmployeeSalary_EmployeeSubmittedBy
• FK_EmployeeSalary_EmployeeConfirmedBy

Although the front end may restrict choices via the drop down, referential integrity is still beneficial:

• Protect against bugs, e.g. where the submitted by employee is omitted (in the case of a non-nullable FK) or the employee provided doesn't exist in the employees table.
• Prevent accidental deletion of an employee to which foreign key data is linked.

There is a (very) minor performance penalty on RI whereby the DB will need to check the existence of the PK in the employee table - in most instances this will be negligible.

Answer 2

Any column that references a key in another table should be declared as a foreign key. This way, if you mistakenly try to put a nonexistent value there, the database will report an error.