From: Christof Glaser <gcg@gl.aser.de>
To: Andreas Tille <tillea@rki.de>
To: PostgreSQL SQL <pgsql-sql@postgresql.org>
Subject: Re: Foreign key problem
Date: Mon, 25 Jun 2001 16:06:48 +0200
Message-ID: <01062516064802.00728@pinguin> (raw)
In-Reply-To: <Pine.LNX.4.33.0106250933390.905-100000@wr-linux02.rki.ivbb.bund.de>
References: <Pine.LNX.4.33.0106250933390.905-100000@wr-linux02.rki.ivbb.bund.de>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gcg@gl.aser.de, tillea@rki.de
Subject: Re: Foreign key problem
In-Reply-To: <01062516064802.00728@pinguin>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox