How to Create reference for Composite key

Hi All,

Can anyone tell me how to create a reference for composite key.

For ex, I have created tblEmp table successfully.

create tblEmp

(

empId varchar(100),

RegId varchar(100),

empname varchar(100),

constraint pk_add

primary key(empId, RegId)

)

And now, I am going to create another table which references the composite key.

create table tblAccount

(

acctId varchar(100) primary key,

empId varchar(100) references tblEmp(empId),

RegId varchar(100) references tblEmp(RegId)

)

But it gives error like

Server: Msg 1776, Level 16, State 1, Line 1
There are no primary or candidate keys in the referenced table 'tblEmp' that match the referencing column list in the foreign key 'FK__tbl'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.

Could anyone please let me know how to create reference for composite key.

Thanks in advance,

Arun.

The below code will do:

create table tblEmp( empIdvarchar(100), RegIdvarchar(100), empnamevarchar(100),constraint pk_addprimary key(empId, RegId) )create table tblAccount( acctIdvarchar(100)primary key, empIdvarchar(100) , RegIdvarchar(100),constraint fk_tblAccountforeign key ( empId, RegId)references tblEmp( empId, RegId) )

In case you've already created both tables, you can use below query to set the foreign key in the account table.

alter table tblAccountadd constraint fk_tblAccountforeign key ( empId, RegId)references tblEmp( empId, RegId)

Hope this will help.

|||

Hi,

We can also do it by

create tblEmp

(

empId varchar(100),

RegId varchar(100),

empname varchar(100),

primary key(empId, RegId)

)

And now, I am going to create another table which references the composite key.

create table tblAccount

(

acctId varchar(100) primary key,

empId varchar(100) ,

RegId varchar(100) ,

foreign key(empId,RegId) references tblEmp(empId,RegId)

)

It works perfect

|||

arunkumar.niit:

primary key(empId, RegId)

arunkumar.niit:

foreign key(empId,RegId) references tblEmp(empId,RegId)

Hey, your queries are exactly the same as what I've posted except one thing that I've given my own names to the constraints and you've left it over the SQL Server.

|||

Hi,

Thanks for your reply.

Thanks & Regards,

Arun.