Foreign key problem - Mailing list pgsql-sql

From Andreas Tille
Subject Foreign key problem
Date
Msg-id Pine.LNX.4.33.0106250933390.905-100000@wr-linux02.rki.ivbb.bund.de
Whole thread Raw
Responses Re: Foreign key problem
Re: Foreign key problem
List pgsql-sql
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:

reskultur=# CREATE TABLE ResKulturDetail
reskultur-# (
reskultur(#     IdLabNr   int,
reskultur(#     IdIndex   smallint
reskultur(# );
CREATE
reskultur=#
reskultur=# CREATE TABLE ResKulturDetailDay
reskultur-# (
reskultur(#     IdLabNr int,
reskultur(#     IdIndex smallint
reskultur(# );
CREATE
reskultur=#
reskultur=# CREATE INDEX IX_IdLabNr_KulturDetail ON ResKulturDetail(IdLabNr) ;
CREATE
reskultur=# CLUSTER IX_IdLabNr_KulturDetail ON ResKulturDetail ;
CLUSTER
reskultur=#
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


Can anybody explain, why the foreign key constraint fails?

Thanks and have a nice weekend
          Andreas.




pgsql-sql by date:

Previous
From: "datactrl"
Date:
Subject: When using COPY
Next
From: "David M. Richter"
Date:
Subject: Plsql Function with error: No space left on device.