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.