Primary key and Foreign Key

Question

Hello,

         I have a question which was asked in an interview

Can a Primary key in a table also act as a foreign key in the same table ?

If yes than please provide a simple example

Thanks

---

www.techgulf.blogspot.com

Answer 1

I would say yes, e.g. if you have a 1:1 relationship between two tables

---

Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

Answer 2

Hello,

         I have a question which was asked in an interview

Can a Primary key in a table also act as a foreign key in the same table ?

If yes than please provide a simple example

Thanks

---

www.techgulf.blogspot.com

There is no meaning to have this, as one FK should be referencing one other PK of another table.

---

Many Thanks & Best Regards, Hua Min

Answer 3

Of course there is, depending on the data model for example in a 1:1|0  (one-to-one or zero) relationship.

Ok, a rough example. Let's say you were member of a community, identified by a personal id (pid). You can be a member of a subcommunity in which you are also identified by your pid.

"pid" in the subcommunity would act as PK as well as FK

---

Bodo Michael Danitz - MCT, MCITP - free consultant - performance specialist - www.sql-server.de

Answer 4

See this illustration

create table t
(
id int identity(1,1) not null primary key,
val varchar(100)
)

create table t2
(
id int not null primary key,
val varchar(100)
)

-- make pk itself as fk to table t
alter table t2 add constraint fk_t2 foreign key (id) references t(id)

--some test values to t
insert t (val)
values ('test'),('test1'),('test2')


-- populate t2 first two suceeds
insert t2(id,val)
values (1,'iuyi')
insert t2(id,val)
values (2,'ef24r')

-- this will fail as we dont have record with id 4 in t
insert t2(id,val)
values (4,'weqw')


--check the output
select * from t
select * from t2

---

Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs