Received: from gl.aser.de (gl.aser.de [213.252.128.30]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5PE4qa36805 for ; Mon, 25 Jun 2001 10:04:52 -0400 (EDT) (envelope-from gcg@gl.aser.de) Received: from pinguin.gl.aser.de (pec-152-167.tnt8.b2.uunet.de [149.225.152.167]) by gl.aser.de (Postfix) with ESMTP id 3AF9F21E0F; Mon, 25 Jun 2001 16:17:54 +0200 (MEST) Received: from pinguin (pinguin.gl.aser.de [192.168.1.111]) by pinguin.gl.aser.de (Postfix) with SMTP id 1DA9412B9D; Mon, 25 Jun 2001 16:06:49 +0200 (CEST) Content-Type: text/plain; charset="iso-8859-1" From: Christof Glaser Organization: gl.aser software engineering . internet service To: Andreas Tille , PostgreSQL SQL Subject: Re: Foreign key problem Date: Mon, 25 Jun 2001 16:06:48 +0200 X-Mailer: KMail [version 1.2] References: In-Reply-To: MIME-Version: 1.0 Message-Id: <01062516064802.00728@pinguin> Content-Transfer-Encoding: 8bit X-Archive-Number: 200106/399 X-Sequence-Number: 3696 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