Received: from matt.w80.math-hat.com (zukerman-0.dsl.speakeasy.net [216.254.75.141]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5PE7Xa38353 for ; Mon, 25 Jun 2001 10:07:33 -0400 (EDT) (envelope-from zukerman@math-hat.com) Received: from zukerman by matt.w80.math-hat.com with local (Exim 3.22 #1 (Debian)) id 15EX1N-0005wo-00; Mon, 25 Jun 2001 10:07:21 -0400 To: Andreas Tille Cc: PostgreSQL SQL Subject: Re: Foreign key problem References: Mail-Copies-To: never From: Itai Zukerman Date: 25 Jun 2001 10:07:21 -0400 In-Reply-To: (Andreas Tille's message of "Mon, 25 Jun 2001 09:34:01 +0200 (CEST)") Message-ID: <878zig1wme.fsf@matt.w80.math-hat.com> Lines: 38 User-Agent: Gnus/5.090004 (Oort Gnus v0.04) Emacs/20.7 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Archive-Number: 200106/400 X-Sequence-Number: 3697 Je Mon, 25 Jun 2001 09:34:01 +0200 (CEST), Andreas Tille scribis: > CREATE TABLE ResKulturDetail > ( > IdLabNr int, > IdIndex smallint > ); > > CREATE TABLE ResKulturDetailDay > ( > IdLabNr int, > IdIndex smallint > ); > > CREATE INDEX IX_IdLabNr_KulturDetail ON ResKulturDetail(IdLabNr) ; > > ALTER TABLE ResKulturDetailDay ADD CONSTRAINT FK_ResKulturDetailDay > FOREIGN KEY (IdLabNr,IdIndex) > REFERENCES ResKulturDetail (IdLabNr,IdIndex) ; > Can anybody explain, why the foreign key constraint fails? According to the documentation for CREATE TABLE: In addition, the referenced columns are supposed to be the columns of a UNIQUE constraint in the referenced table, however Postgres does not enforce this. Well, it looks like PostgreSQL *does* enforce it. Try it with: CREATE UNIQUE INDEX IX_IdLabNr_KulturDetail ON ResKulturDetail(IdLabNr, IdIndex) ; I'm not sure why this restriction is necessary... -- Itai Zukerman