Two foreign keys referencing the same primary key

Question

Is this okay to have two foreign keys in one table referencing one primary key of other table?

EmployeeID is a primary key in the employee table and appearing as a foreign key twice in the timesheet table.

There will be few admin users filling up timsheets on the behalf of other employees.

In the timsheet table field 'TimsheetFor' will have employeeID of that person who has worked on projects and field 'EnteredBy' or 'FilledBy' will have employeeid of that person who has filled up this timesheet.

Which of the following option is correct?

NOTE: Tables are showing only those fields which are related to this question.

enter image description here

Yes - **of course!** Why do you ask? What's the issue/problem you're seeing? — marc_s, Jul 01 '12 at 18:08
I wanted to asked the same question. I'm glad that I'm a apart of this great site! Thanks for answering :) — Marcin Erbel, Nov 02 '13 at 00:20

Kevin Aenmey · Answer 1 · 2012-07-01T18:15:18.780

27

I would go with option 1. It is perfectly fine to have two foreign key columns referencing the same primary key column in a different table since each foreign key value will reference a different record in the related table.

I'm sure option 2 would work, but you would essentially have a 1-to-1 relationship between TIMESHEET_TABLE and TIMESHEET_FILLED_BY, making two tables unnecessary and more difficult to maintain.

In fact, if both ENTERED_BY and TIMESHEET_FOR are required in pairs, using option 1 makes far more sense because this is automatically enforced by the database and foreign keys.

edited Jul 01 '12 at 18:15

answered Jul 01 '12 at 18:08

Kevin Aenmey

13,259
5
46
45

2

I am SSS and I endorse this answer :) – SSS Jul 02 '12 at 02:00
It works, but only if both FK has Delete and Update Options to NO ACTION. If you put "Cascade", or other options, it will give you an error. I have the same situation on my SQL 2008 Server, where I have two tables, Currency and CurrencyHistory. Currency has IdCurrency as primary key, and the second table has IdCurrency and IdCurrencyRefference, wich has to be the same Id Column from Currency table. So, the delete or update I have to do it from code, programmatically. That's it :) – Vali Maties Mar 26 '17 at 22:37

score 4 · Answer 2 · answered Oct 12 '15 at 08:57

Option 1 is a perfect solution. You may define foreign key constraint as following

1st foreign key constraint for Timesheet_For column

ALTER TABLE TIMESHEETTABLE 
ADD CONSTRAINT fk_TimesheetTable_EmployeeTable
FOREIGN KEY (TIMESHEET_FOR)
REFERENCES EMPLOYEETABLE(EMPLOYEE_ID)

2nd foreign key constraint for Entered_By column

ALTER TABLE TIMESHEETTABLE 
ADD CONSTRAINT fk_TimesheetTable_EmployeeTable_1
FOREIGN KEY (ENTERED_BY)
REFERENCES EMPLOYEETABLE(EMPLOYEE_ID)

Raab · Answer 3 · 2012-07-01T18:14:26.470

2

yes, there is no problem with that...you can use a primary key of one table in other table as foreign key two times.

edited Jul 01 '12 at 18:14

answered Jul 01 '12 at 18:08

Raab

34,778
4
50
65

What is term for that method..?, I want to read more about that. – Tri Feb 18 '19 at 13:31
this is a common practice, I don't think there a special method or term for that. @Harvest1018 – Raab Feb 20 '19 at 14:23

score 1 · Answer 4 · answered Sep 23 '20 at 12:51

1

Your query like :

SELECT t.EMPLOYEE_ID, a.NAME as TimeSheetFor, b.NAME as EnteredBy 
FROM timesheet t
JOIN employee a ON t.timesheet_for =a.employee_id
JOIN employee b ON t.entered_by = b.employee_id

Using this query you will get result as you want.

answered Sep 23 '20 at 12:51

Bhargav Variya

725
1
10
18

Two foreign keys referencing the same primary key

4 Answers4

Linked

Related