[SQL] Foreign key problem

[Andreas Tille]

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.



---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]