CODEWITHSUNDEEP
asp.netsqlvb.netnormalizationrdbms
user1263981
user1263981

Reputation: 3147

Two foreign keys referencing the same primary key

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

Upvotes: 45

Views: 57622

Answers (4)

Bhargav Variya
Bhargav Variya

Reputation: 765

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.

Upvotes: 1

Ashish Shukla
Ashish Shukla

Reputation: 1294

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)

Upvotes: 4

Kevin Aenmey
Kevin Aenmey

Reputation: 13419

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.

Upvotes: 28

Rab
Rab

Reputation: 35572

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

Upvotes: 2

Related Questions