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.
EmployeeID是employee表中的主键，在时间表表中两次显示为外键。
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.
在时间表表格字段中，“TimsheetFor”将具有处理项目和“EnteredBy”或“FilledBy”字段的那个人的employeeID将具有填写此时间表的那个人的employeeid。
Which of the following option is correct?
以下哪个选项是正确的？
NOTE: Tables are showing only those fields which are related to this question.
注意：表格仅显示与此问题相关的字段。
3 个解决方案
#1
20
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.
我会选择选项1.由于每个外键值将引用相关表中的不同记录，因此在另一个表中引用相同主键列的两个外键列完全没问题。
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.
我确信选项2可以工作，但是你在TIMESHEET_TABLE和TIMESHEET_FILLED_BY之间基本上有一对一的关系，这使得两个表不必要并且更难以维护。
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.
实际上，如果成对需要ENTERED_BY和TIMESHEET_FOR，则使用选项1会更有意义，因为这是由数据库和外键自动强制执行的。
#2
3
yes, there is no problem with that...you can use a primary key of one table in other table as foreign key two times.
是的，没有问题...你可以使用其他表中一个表的主键作为外键两次。
#3
2
Option 1 is a perfect solution. You may define foreign key constraint as following
选项1是一个完美的解决方案。您可以定义外键约束，如下所示
1st foreign key constraint for Timesheet_For column
Timesheet_For列的第一个外键约束
ALTER TABLE TIMESHEETTABLE
ADD CONSTRAINT fk_TimesheetTable_EmployeeTable
FOREIGN KEY (TIMESHEET_FOR)
REFERENCES EMPLOYEETABLE(EMPLOYEE_ID)
2nd foreign key constraint for Entered_By column
Entered_By列的第二个外键约束
ALTER TABLE TIMESHEETTABLE
ADD CONSTRAINT fk_TimesheetTable_EmployeeTable_1
FOREIGN KEY (ENTERED_BY)
REFERENCES EMPLOYEETABLE(EMPLOYEE_ID)
#1
20
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.
我会选择选项1.由于每个外键值将引用相关表中的不同记录，因此在另一个表中引用相同主键列的两个外键列完全没问题。
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.
我确信选项2可以工作，但是你在TIMESHEET_TABLE和TIMESHEET_FILLED_BY之间基本上有一对一的关系，这使得两个表不必要并且更难以维护。
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.
实际上，如果成对需要ENTERED_BY和TIMESHEET_FOR，则使用选项1会更有意义，因为这是由数据库和外键自动强制执行的。
#2
3
yes, there is no problem with that...you can use a primary key of one table in other table as foreign key two times.
是的，没有问题...你可以使用其他表中一个表的主键作为外键两次。
#3
2
Option 1 is a perfect solution. You may define foreign key constraint as following
选项1是一个完美的解决方案。您可以定义外键约束，如下所示
1st foreign key constraint for Timesheet_For column
Timesheet_For列的第一个外键约束
ALTER TABLE TIMESHEETTABLE
ADD CONSTRAINT fk_TimesheetTable_EmployeeTable
FOREIGN KEY (TIMESHEET_FOR)
REFERENCES EMPLOYEETABLE(EMPLOYEE_ID)
2nd foreign key constraint for Entered_By column
Entered_By列的第二个外键约束
ALTER TABLE TIMESHEETTABLE
ADD CONSTRAINT fk_TimesheetTable_EmployeeTable_1
FOREIGN KEY (ENTERED_BY)
REFERENCES EMPLOYEETABLE(EMPLOYEE_ID)