# Database Discussions > MS SQL Server 7/MS SQL Server 2000 >  Composite Primary Key and Foreign Key - Relations

## Srinivasan

Hi,

I have a doubt, can anyone please clarify me.

I have created the following table,

create table z
(
eno int,
deptno char(2),
ename varchar(5),
constraint pk_eno_deptno primary Key(eno,deptno)
)
and I tried to establish a foreign key based on the above defined table. It&#39;s giving error, 

create table z_dup
(
eno int FOREIGN KEY REFERENCES z(eno), 
deptno char(2)
)
go

Error:- 
*****
There are no primary or candidate keys in the referenced table &#39;z&#39; that match the referencing column list in the foreign key &#39;FK__z_dup__eno__18CE1BA6&#39;.Could not create constraint. See previous errors.

Even I tried to create a foreign key like the following,

create table z_dup
(
eno int FOREIGN KEY REFERENCES z(eno,deptno), 
deptno char(2)
)
go

Error:-
********
More than one key specified in column level FOREIGN KEY constraint, table &#39;z_dup&#39;.


Can anyone please help me to create a foreign key relation with the base table.

tks in advance,
Srinivasan

----------


## Kristine Greenlee

Try this syntax for your second table:

create table z_dup
(
eno int , 
deptno char(2),
constraint fk_eno_deptno FOREIGN KEY (eno, deptno) REFERENCES z(eno, deptno)
)
go


------------
Srinivasan at 9/19/00 11:07:00 AM

Hi,

I have a doubt, can anyone please clarify me.

I have created the following table,

create table z
(
eno int,
deptno char(2),
ename varchar(5),
constraint pk_eno_deptno primary Key(eno,deptno)
)
and I tried to establish a foreign key based on the above defined table. It&#39;s giving error, 

create table z_dup
(
eno int FOREIGN KEY REFERENCES z(eno), 
deptno char(2)
)
go

Error:- 
*****
There are no primary or candidate keys in the referenced table &#39;z&#39; that match the referencing column list in the foreign key &#39;FK__z_dup__eno__18CE1BA6&#39;.Could not create constraint. See previous errors.

Even I tried to create a foreign key like the following,

create table z_dup
(
eno int FOREIGN KEY REFERENCES z(eno,deptno), 
deptno char(2)
)
go

Error:-
********
More than one key specified in column level FOREIGN KEY constraint, table &#39;z_dup&#39;.


Can anyone please help me to create a foreign key relation with the base table.

tks in advance,
Srinivasan

----------


## Srinivasan

Hi Kristine,

Thanks for your help. But I want to represent &#39;z_dup.eno&#39; only as a foreign key. I don&#39;t want to make it &#39;deptno&#39; as a foreign key like this &#39;FOREIGN KEY (eno, deptno)&#39;. 

Can anyone please help me.

tks in advance,
Srinivasan.

------------
Kristine Greenlee at 9/19/00 11:46:12 AM

Try this syntax for your second table:

create table z_dup
(
eno int , 
deptno char(2),
constraint fk_eno_deptno FOREIGN KEY (eno, deptno) REFERENCES z(eno, deptno)
)
go


------------
Srinivasan at 9/19/00 11:07:00 AM

Hi,

I have a doubt, can anyone please clarify me.

I have created the following table,

create table z
(
eno int,
deptno char(2),
ename varchar(5),
constraint pk_eno_deptno primary Key(eno,deptno)
)
and I tried to establish a foreign key based on the above defined table. It&#39;s giving error, 

create table z_dup
(
eno int FOREIGN KEY REFERENCES z(eno), 
deptno char(2)
)
go

Error:- 
*****
There are no primary or candidate keys in the referenced table &#39;z&#39; that match the referencing column list in the foreign key &#39;FK__z_dup__eno__18CE1BA6&#39;.Could not create constraint. See previous errors.

Even I tried to create a foreign key like the following,

create table z_dup
(
eno int FOREIGN KEY REFERENCES z(eno,deptno), 
deptno char(2)
)
go

Error:-
********
More than one key specified in column level FOREIGN KEY constraint, table &#39;z_dup&#39;.


Can anyone please help me to create a foreign key relation with the base table.

tks in advance,
Srinivasan

----------


## Krishnan

a foriegn key can be referenced to primary key or a unique key.

Since the p.k consists of multiple column here u can refer to these both columns but if u want to refer only one key then u can declare it as unique and refer it 

or

U can declare both columns as unique and have eno as p.k. and refer it.

Hope this helps.........if u have any questions let me know.


------------
Srinivasan at 9/19/00 12:51:08 PM

Hi Kristine,

Thanks for your help. But I want to represent &#39;z_dup.eno&#39; only as a foreign key. I don&#39;t want to make it &#39;deptno&#39; as a foreign key like this &#39;FOREIGN KEY (eno, deptno)&#39;. 

Can anyone please help me.

tks in advance,
Srinivasan.

------------
Kristine Greenlee at 9/19/00 11:46:12 AM

Try this syntax for your second table:

create table z_dup
(
eno int , 
deptno char(2),
constraint fk_eno_deptno FOREIGN KEY (eno, deptno) REFERENCES z(eno, deptno)
)
go


------------
Srinivasan at 9/19/00 11:07:00 AM

Hi,

I have a doubt, can anyone please clarify me.

I have created the following table,

create table z
(
eno int,
deptno char(2),
ename varchar(5),
constraint pk_eno_deptno primary Key(eno,deptno)
)
and I tried to establish a foreign key based on the above defined table. It&#39;s giving error, 

create table z_dup
(
eno int FOREIGN KEY REFERENCES z(eno), 
deptno char(2)
)
go

Error:- 
*****
There are no primary or candidate keys in the referenced table &#39;z&#39; that match the referencing column list in the foreign key &#39;FK__z_dup__eno__18CE1BA6&#39;.Could not create constraint. See previous errors.

Even I tried to create a foreign key like the following,

create table z_dup
(
eno int FOREIGN KEY REFERENCES z(eno,deptno), 
deptno char(2)
)
go

Error:-
********
More than one key specified in column level FOREIGN KEY constraint, table &#39;z_dup&#39;.


Can anyone please help me to create a foreign key relation with the base table.

tks in advance,
Srinivasan

----------


## Kristine Greenlee

Then make the primary key on table z just on eno.


------------
Srinivasan at 9/19/00 12:51:08 PM

Hi Kristine,

Thanks for your help. But I want to represent &#39;z_dup.eno&#39; only as a foreign key. I don&#39;t want to make it &#39;deptno&#39; as a foreign key like this &#39;FOREIGN KEY (eno, deptno)&#39;. 

Can anyone please help me.

tks in advance,
Srinivasan.

------------
Kristine Greenlee at 9/19/00 11:46:12 AM

Try this syntax for your second table:

create table z_dup
(
eno int , 
deptno char(2),
constraint fk_eno_deptno FOREIGN KEY (eno, deptno) REFERENCES z(eno, deptno)
)
go


------------
Srinivasan at 9/19/00 11:07:00 AM

Hi,

I have a doubt, can anyone please clarify me.

I have created the following table,

create table z
(
eno int,
deptno char(2),
ename varchar(5),
constraint pk_eno_deptno primary Key(eno,deptno)
)
and I tried to establish a foreign key based on the above defined table. It&#39;s giving error, 

create table z_dup
(
eno int FOREIGN KEY REFERENCES z(eno), 
deptno char(2)
)
go

Error:- 
*****
There are no primary or candidate keys in the referenced table &#39;z&#39; that match the referencing column list in the foreign key &#39;FK__z_dup__eno__18CE1BA6&#39;.Could not create constraint. See previous errors.

Even I tried to create a foreign key like the following,

create table z_dup
(
eno int FOREIGN KEY REFERENCES z(eno,deptno), 
deptno char(2)
)
go

Error:-
********
More than one key specified in column level FOREIGN KEY constraint, table &#39;z_dup&#39;.


Can anyone please help me to create a foreign key relation with the base table.

tks in advance,
Srinivasan

----------


## Srinivasan

Hi Kristine / Krishnan,

Thanks for your timely suggestions.

regards,
Srinivasan.


------------
Kristine Greenlee at 9/19/00 2:25:36 PM

Then make the primary key on table z just on eno.


------------
Srinivasan at 9/19/00 12:51:08 PM

Hi Kristine,

Thanks for your help. But I want to represent &#39;z_dup.eno&#39; only as a foreign key. I don&#39;t want to make it &#39;deptno&#39; as a foreign key like this &#39;FOREIGN KEY (eno, deptno)&#39;. 

Can anyone please help me.

tks in advance,
Srinivasan.

------------
Kristine Greenlee at 9/19/00 11:46:12 AM

Try this syntax for your second table:

create table z_dup
(
eno int , 
deptno char(2),
constraint fk_eno_deptno FOREIGN KEY (eno, deptno) REFERENCES z(eno, deptno)
)
go


------------
Srinivasan at 9/19/00 11:07:00 AM

Hi,

I have a doubt, can anyone please clarify me.

I have created the following table,

create table z
(
eno int,
deptno char(2),
ename varchar(5),
constraint pk_eno_deptno primary Key(eno,deptno)
)
and I tried to establish a foreign key based on the above defined table. It&#39;s giving error, 

create table z_dup
(
eno int FOREIGN KEY REFERENCES z(eno), 
deptno char(2)
)
go

Error:- 
*****
There are no primary or candidate keys in the referenced table &#39;z&#39; that match the referencing column list in the foreign key &#39;FK__z_dup__eno__18CE1BA6&#39;.Could not create constraint. See previous errors.

Even I tried to create a foreign key like the following,

create table z_dup
(
eno int FOREIGN KEY REFERENCES z(eno,deptno), 
deptno char(2)
)
go

Error:-
********
More than one key specified in column level FOREIGN KEY constraint, table &#39;z_dup&#39;.


Can anyone please help me to create a foreign key relation with the base table.

tks in advance,
Srinivasan

----------

