Wednesday 29 May 2013

Is it possible to create foreign key for child table if column has composit primary key?

No, But we can make it possible.

    suppose I have Parent table
eg.
 create table parent (id int  ,value int,primary key(id,value))
   
It has two column .Composit primary key is present on it.

Composit primary : If primary  key  is combination of more than one column then it called as composit primary key.

  I have child table

e.g

create tableChild(id int  foreign key(id) references   parent (id))

Now execute above statement .You will get error.
Message :There are no primary or candidate keys in the referenced table

But we can do it By following way
 make constrain unique on that column.It will work.

script :

Error :
 begin tran 
create table parent (id int  ,value int,primary key(id,value))
create table Child(id int  foreign key(id) references   parent (id))
rollback

Correct :
 begin tran 
create table parent (id int  unique,value int,primary key(id,value))
 create table Child(id int  foreign key(id) references   parent (id))
rollback






First Database In Sql Server