Re: Foreign key problem

On Monday, 25. June 2001 09:34, Andreas Tille wrote:
> Hello,
>
> I tried to track down the database definitions from a more complex
> database which I have to convert from MS SQL to PostgreSQL. I have
> only one last syntactical error. Here is the striped down code
> to the shortest snipped which shows the problem:
>
> CREATE TABLE ResKulturDetail
> (
> IdLabNr int,
> IdIndex smallint
> );
>
> CREATE TABLE ResKulturDetailDay
> (
> IdLabNr int,
> IdIndex smallint
> );
>
> CREATE INDEX IX_IdLabNr_KulturDetail ON ResKulturDetail(IdLabNr) ;
> CLUSTER IX_IdLabNr_KulturDetail ON ResKulturDetail ;
>
> ALTER TABLE ResKulturDetailDay ADD CONSTRAINT FK_ResKulturDetailDay
> FOREIGN KEY (IdLabNr,IdIndex)
> REFERENCES ResKulturDetail (IdLabNr,IdIndex) ;
>
> Here is the psql log, if I try to insert the code above:

[snip]

> reskultur=# ALTER TABLE ResKulturDetailDay ADD CONSTRAINT
> FK_ResKulturDetailDay
> reskultur-# FOREIGN KEY (IdLabNr,IdIndex)
> reskultur-# REFERENCES ResKulturDetail (IdLabNr,IdIndex) ;
> NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit
> trigger(s) for FOREIGN KEY check(s)
> ERROR: UNIQUE constraint matching given keys for referenced table
> "reskulturdetail" not found

Just create an additional UNIQUE index for the referenced fields:

CREATE UNIQUE INDEX need_a_better_name
ON ResKulturDetail ( IdLabNr, IdIndex );

> Can anybody explain, why the foreign key constraint fails?

Foreign keys must reference to unique field(s), as the error message
tries to tell you.

> Thanks and have a nice weekend

"I don't like mondays" comes to my mind :-)

That being said, sometimes there seems to be a weird delay in mail
delivery from PG lists. I get quite sometimes answers first while the
question arrives hours later. This is no complaint, I can live with it.

Christof.
--
gl.aser . software engineering . internet service
http://gl.aser.de/ . Planckstraße 7 . D-39104 Magdeburg