Primary Key/Foreign Key Same Field in 1:1 Relationship?

• EsquireDeveloper

  Old Hand

  Points: 376

  More actions

  November 25, 2020 at 3:15 pm

  Go to Answer

  #3813901

  Hello~

  DB Design question -- say I have a primary table, tCompanies, where the primary key is CompanyID.

  And I have another table, say tCompanyMetrics, where every field in that table has a one to one relationship with CompanyID.

  In tCompanyMetrics, do I need both a primary key field and a foreign key field?  Cannot I put a field in tCompanyMetrics also called CompanyID and have that field as both the primary key and foreign key -- making that field a clustered unique index?

  This stackoverflow article seems to suggest this is fine -

  https://stackoverflow.com/questions/10982992/is-it-fine-to-have-foreign-key-as-primary-key#:~:text=It%20is%20perfectly%20fine%20to,stick%20with%20what%20you%20have.

  But before I build this, I wanted to see if there is another school of thought about this or if I'm missing something here.  Thanks!

   

• ScottPletcher

  SSC Guru

  Points: 101108

  More actions

  November 25, 2020 at 3:41 pm

  Answer

  #3813910

  Sure, you can do that.  And you should do that.

  You could also move those columns (attributes) into the main table, but it's often useful to have a separate 1-1 table for miscellaneous columns.

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".