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