SQL Server use same Guid as primary key in 2 tables

Andreas Reiff 0
Viewed 52

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?

shared-primary-key primary-key-design primary-key sql-server sql
1 Answers

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 Zivkovic0
Related