locked
Primary key and Foreign Key RRS feed

  • 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


    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

    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

    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


    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


    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

    Saturday, August 30, 2014 6:59 PM