Search code examples
sql-servercode-first

Use PK of one table as the PK for another

So I am working on a code first MVC with multiple SQL tables. The first contains basic information for a person {UID,FNAME,LNAME,DOB,GENDER} and the others contain data such as {BOOL1,BOOL2,BOOL3}. I am using different tables because some data may not be updated as frequently as others, and wish to keep this data separate. Is is acceptable to use the PK of the first table as the PK of the others? And how would I accomplish this without the problem of inserting an identity?

Solution

  • It depends on the scenario -

    1. One table is the master and the other table is a type of the first one: in this case the first table PK is FK for the second one. you may still make this field unique and also use it for clustered index if it makes sense.
    2. Two tables has different type of objects but should use a shared exclusive ids: in this case you may use a sequence for Id to make them unique.