Answered by:
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
Saturday, August 30, 2014 8:08 AM
Answers
-
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
- Edited by Bodo Michael Danitz Saturday, August 30, 2014 9:12 AM
- Proposed as answer by Lydia ZhangMicrosoft contingent staff Monday, September 1, 2014 2:35 AM
- Marked as answer by Lydia ZhangMicrosoft contingent staff Monday, September 8, 2014 9:29 AM
Saturday, August 30, 2014 9:02 AM -
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
- Proposed as answer by Lydia ZhangMicrosoft contingent staff Monday, September 1, 2014 2:32 AM
- Marked as answer by Lydia ZhangMicrosoft contingent staff Monday, September 8, 2014 9:29 AM
Saturday, August 30, 2014 6:59 PM
All replies
-
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
Saturday, August 30, 2014 8:41 AM -
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
Many Thanks & Best Regards, Hua Min
- Edited by Jackson_1990 Saturday, August 30, 2014 8:47 AM
Saturday, August 30, 2014 8:47 AM -
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
- Edited by Bodo Michael Danitz Saturday, August 30, 2014 9:12 AM
- Proposed as answer by Lydia ZhangMicrosoft contingent staff Monday, September 1, 2014 2:35 AM
- Marked as answer by Lydia ZhangMicrosoft contingent staff Monday, September 8, 2014 9:29 AM
Saturday, August 30, 2014 9:02 AM -
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
- Proposed as answer by Lydia ZhangMicrosoft contingent staff Monday, September 1, 2014 2:32 AM
- Marked as answer by Lydia ZhangMicrosoft contingent staff Monday, September 8, 2014 9:29 AM
Saturday, August 30, 2014 6:59 PM