Can a foreign key also be used as the primary key in the same table?
Can a foreign key also be used as the primary key in the same table?
Yes. This situation occurs in one-to-one relationships.
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
For example:
create table Table1 ( T1_Id integer not null primary key , T1_Data varchar(9) not null ) create table Table2 ( T2_Id integer not null primary key , T2_Data varchar(37) not null , foreign key (T2_Id) references Table1 (T1_Id) )
Why would someone design two tables like this? If it's a one-to-one relationship, couldn't you just include T2_Data in Table1? Yes, you could, although then you would also need to allow T2_Data to be NULL, if not every T1_Id has a matching T2_Id—in this case it's actually a one-to-zero-or-one relationship.
If it's a true one-to-one relationship, where every T1_Id has a matching T2_Id, then you could combine them without NULLs. Then the only reason why you might want separate tables would be if T2_Data were (a) infrequently queried, and (b) so large that it would result in many fewer physical rows on a physical database page, which would imply poorer performance for queries that used only T1_Data.