SQL Server Performance Forum – Threads Archive
PK & FK
HiI have 2 tables Emp and Dept. EmpId is the PK for Emp table and Dept has no PK. Which is the best way to do from the following..
1)Having DeptId as an Identity column and EmpId as a FK in Dept so that I can have relation. or 2)Insert EmpId in Dept without any DeptId and make it as PK so that it has relation with Emp table. Can any one of you explain me which is the gud way and the reasons behind it?
Thanks!
Rule #1: All tables must have a PK. You just don’t mess with rule #1. Dept must have a PK, and your DeptId is actually a good idea. Start by adding this to your Dept table. For each employee, you should be able to say in which department they belong. That is what an FK does: it tells you how entities are related. So add DeptId as an FK to your Emp table, referring to your Dept table.
Adriaan
Thanks for ur reply.
Let me know the difference between adding DeptNo as FK in Emp table and adding EmpId as FK in Dept Table. Thanks!
Which entity contains which other entity: does one department contain lots of employees, or does one employee contain lots of departments? The entity that is being contained by another entity is the one that gets the FK, which refers to the PK of the containing entity.
yeah
its dept contains lots of employees. Thanks!
]]>