0

We have 2 tables with a 1:1 relationship. 1 table should reference the other, typically one would use a FK relationship. Since there is a 1:1 relationship, we could also directly use the same Guid in both tables as primary key.

Additional info: the data is split into 2 tables since the data is rather separate, think "person" and "address" - but in a world where there is a clear 1:1 relationship between the 2.

As per the tags I was suggested I assume this is called "shared primary key".

Would using the same Guid as PK in 2 tables have any ill effects?

Andreas Reiff
  • 7,961
  • 10
  • 50
  • 104
  • 2
    even if you use the same Guid in both tables as primary key, I still recommend setting up a foreign key to ensure the referential integrity of your data – GuidoG Sep 09 '22 at 09:08
  • 3
    There are no ill effects in using the same Guid value as primary key in both tables, but I do not recommend using a Guid type for the primary key, as this can have a bad effect on performance. Why not use an int or bigint ? – GuidoG Sep 09 '22 at 09:10
  • Also, what do you think you will gain by using the same value in the primary key for both tables ? Why would you want this anyway ? – GuidoG Sep 09 '22 at 09:13
  • 2
    Using same column as both PK and FK is quite OK. It's common when one entity extends another - ie: tables both tables Teacher and Student can extend and share PK from table Person. Maybe your case with Person and Address would not be best example. – Nenad Zivkovic Sep 09 '22 at 09:21
  • @GuidoG We need a key.. so either use same key as PK and have that for relationship or use differnet key but add FK column. As for Guid, this was a lot easier since we rely a lot on ADO.Net. Also it makes merging databases a lot easier. It does have a lot of bad effects too, though they all are rather minor, like more fragmentation, more resource usage etc. – Andreas Reiff Sep 09 '22 at 09:24
  • @NenadZivkovic Interesting, never thought of that. In our case splitting is just some form of normalization though. I also thought about combining the 2 tables, but having them separate also has its charm. – Andreas Reiff Sep 09 '22 at 09:25
  • 1
    I am just saying that even if you use the same key, you still need to setup a FK to enforce the 1:1 relationship, as far as I know, foreign keys are the only means to setup relationships that can be enforced and managed by the database and that will ensure your referential integrity remains intact. – GuidoG Sep 09 '22 at 09:29
  • 2
    While you can, joining tables on a guid is not going to perform as well as joining using an int. Use the guid if you must as the key for the first table but the FK should be an int, and remember if you cluster the table on a guid it's also going to be included in every non-clustered index. – Stu Sep 09 '22 at 09:30
  • What actual tables/relationships are you trying to model? Is it a `Person`/`Student` type of relationship? – Charlieface Sep 09 '22 at 11:06

1 Answers1

2

To consolidate info from comments into answer...

No, there are no ill effects of two tables sharing PK. You will still need to create a FK reference from 2nd table, FK column will be the same as PK column.

Though, your example of "Person" and "Address" in 1:1 situation is not best suited. Common usage of this practice is entities that extend one another. For example: Table "User" can hold common info on all users, but tables "Candidate" and "Recruiter" can each expand on it, and all tables can share same PK. Programming language representation would also be classes that extends one another.

Other (similar) example would be table that store more detailed info than the base table like "User" and "UserDetails". It's 1:1 and no need to introduce additional PK column.

Code sample where PK is also a FK:

CREATE TABLE [User]
(
   id INT PRIMARY KEY
 , name NVARCHAR(100)
); 

CREATE TABLE [Candidate] 
(
   id INT PRIMARY KEY FOREIGN KEY REFERENCES [User](id)
 , actively_looking BIT 
); 

CREATE TABLE [Recruiter]
(
   id INT PRIMARY KEY
 , currently_hiring BIT 
 , FOREIGN KEY (id) REFERENCES [User](id)
);

PS: As mentioned GUID is not best suited column for PK due to performance issues, but that's another topic.

Nenad Zivkovic
  • 18,221
  • 6
  • 42
  • 55
  • 1
    A `type` column is commonly used also, and you can make that part of the primary/foreign key, and on the child tables it would be a computed column. This prevents an entity being part of multiple child tables – Charlieface Sep 09 '22 at 16:56