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