Q
Problem solve Get help with specific problems with your technologies, process and projects.

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?

Can a foreign key also be used as the primary key in the same table?

Yes. This situation occurs in one-to-one relationships.

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.

This was last published in June 2006

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Join the conversation

1 comment

Send me notifications when other members comment.

Please create a username to comment.

(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.


if t1 is t2 ( by virtue of reference )
shouldn't querying t2 indireclty reflect t1
and hence same load on performance .-.
Cancel

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close