Q

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

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close