0

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?

Caleb Huggins
  • 89
  • 1
  • 1
  • 12

1 Answers1

1

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.
UV.
  • 492
  • 6
  • 9
  • Yes, both tables are different types of data. How would I make a sequence for the id? – Caleb Huggins Nov 02 '16 at 23:49
  • Look here - http://stackoverflow.com/questions/10991894/auto-increment-primary-key-in-sql-server-management-studio-2012 – UV. Nov 02 '16 at 23:53
  • Ok, an auto-incremented value. How would i ensure that the IDs of both tables were the same? Create them at one time, then save the changes to the database at one time? – Caleb Huggins Nov 02 '16 at 23:57
  • you specify the same sequence for the id in both tables. you get the next value when inserting - INSERT Test.TestTable (CounterColumn,Name) VALUES (NEXT VALUE FOR Test.CountBy1, 'Syed') ; ... it means you are not using identity for the field – UV. Nov 03 '16 at 00:08