CODEWITHSUNDEEP
sqlforeign-keysprimary-key
David_Authors_Books
David_Authors_Books

Reputation: 39

SQL: Having a primary key also be a foreign key

For this question, I am referring to the specific case where you have table T, it has primary key K, but K is a foreign key. Is this valid? And how would you write it in SQL99?

All the other questions I've seen on here are just asking whether or not a primary key can be a foreign key for another table. That's not what I'm asking about. I'm asking about a table which has a foreign key where that is the primary key of that table.

Upvotes: 1

Views: 3446

Answers (2)

vc 74
vc 74

Reputation: 38179

If I understand you correctly, you want to create a hierarchical table, for instance:

create table hierarchical
(
    id number primary key, 
    parent_id number
);

alter table hierarchical add constraint 
    fk_parent_id foreign key(parent_id) references hierarchical(id);

This kind of table can contain employees/managers for instance.

Upvotes: 3

Gauravsa
Gauravsa

Reputation: 6528

A column can be a primary key as well foreign key. For example, refer to the following:

A column can be both a primary key and a foreign key. For example:

create table A 
(
id int not null
, constraint PK_A primary key (id)
);

create table B 
(
id int not null
,constraint PK_B primary key (id)
,constraint FK_B_ID foreign key (id) references A(id)
);

Though, this requires data to be present in Table B first.

Upvotes: 2

Related Questions