Problem creating FOREIGN key

Hi,

I am using SQL Server 7.0

TABLE1
======
Fld1
Fld2
Fld3
Fld4
Fld5
Fld6

Primary Key = Fld1 + Fld2 + Fld3

TABLE2
======
Fld1
Fld2
Fld3
Fld4
Fld5

Foriegn Key = Fld5 (Referencing TABLE1.Fld3) The datatypes are
matching in both tables.

I am trying to create the foreign key by giving the following command:

ALTER TABLE TABLE2 ADD
CONSTRAINT [FK_TABLE2_TABLE1] FOREIGN KEY
(
[Fld5]
) REFERENCES TABLE1 (
[Fld3]
)
GO

This is giving the following error:

There are no primary or candidate keys in the referenced table
'TABLE1' that match the referencing column list in the foreign key
'FK_TABLE2_TABLE1'.

Any help?

Regards,
KamleshHi

It is better to post DDL (Create/Alter table statements) and example data
(as insert statements) than a pseudo schema such as yours.

The foreign key will have to match all columns of the primary key of the
referenced table.

If the values in fld3 are unique, then this may be a better candidate for
the primary key for table 1,
otherwise you may need to create a table just containing the unique values
of fld3 in table 1, where both table1 and table2 can reference it as a
foreign key.

John

"Kamlesh" <kamlesh2000@.yahoo.com> wrote in message
news:2644c0f6.0309080711.1ee199b1@.posting.google.c om...
> Hi,
> I am using SQL Server 7.0
>
> TABLE1
> ======
> Fld1
> Fld2
> Fld3
> Fld4
> Fld5
> Fld6
> Primary Key = Fld1 + Fld2 + Fld3
>
> TABLE2
> ======
> Fld1
> Fld2
> Fld3
> Fld4
> Fld5
>
> Foriegn Key = Fld5 (Referencing TABLE1.Fld3) The datatypes are
> matching in both tables.
> I am trying to create the foreign key by giving the following command:
> ALTER TABLE TABLE2 ADD
> CONSTRAINT [FK_TABLE2_TABLE1] FOREIGN KEY
> (
> [Fld5]
> ) REFERENCES TABLE1 (
> [Fld3]
> )
> GO
>
> This is giving the following error:
> There are no primary or candidate keys in the referenced table
> 'TABLE1' that match the referencing column list in the foreign key
> 'FK_TABLE2_TABLE1'.
>
> Any help?
> Regards,
> Kamlesh|||A foreign key can only refer to the Primary Key of the referenced table
(or to another combination of columns that is uniquely indexed). So if
your Table1 has a three column primary key, then the foreign key should
also consist of three columns, and they should have the same data type.

Gert-Jan

Kamlesh wrote:
> Hi,
> I am using SQL Server 7.0
> TABLE1
> ======
> Fld1
> Fld2
> Fld3
> Fld4
> Fld5
> Fld6
> Primary Key = Fld1 + Fld2 + Fld3
> TABLE2
> ======
> Fld1
> Fld2
> Fld3
> Fld4
> Fld5
> Foriegn Key = Fld5 (Referencing TABLE1.Fld3) The datatypes are
> matching in both tables.
> I am trying to create the foreign key by giving the following command:
> ALTER TABLE TABLE2 ADD
> CONSTRAINT [FK_TABLE2_TABLE1] FOREIGN KEY
> (
> [Fld5]
> ) REFERENCES TABLE1 (
> [Fld3]
> )
> GO
> This is giving the following error:
> There are no primary or candidate keys in the referenced table
> 'TABLE1' that match the referencing column list in the foreign key
> 'FK_TABLE2_TABLE1'.
> Any help?
> Regards,
> Kamlesh