Problem creating FOREIGN key

[Kamlesh]

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_TABLE 1] 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_TABL E1'.


Any help?

Regards,
Kamlesh

[John Bell]

Re: Problem creating FOREIGN key

Hi

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@ya hoo.com> wrote in message
news:2644c0f6.0 309080711.1ee19 9b1@posting.goo gle.com...[color=blue]
> 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_TABLE 1] 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_TABL E1'.
>
>
> Any help?
>
> Regards,
> Kamlesh[/color]

[Gert-Jan Strik]

Re: Problem creating FOREIGN key

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:[color=blue]
>
> 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_TABLE 1] 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_TABL E1'.
>
> Any help?
>
> Regards,
> Kamlesh[/color]