Обсуждение: Foreign key problem

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.

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

Je Mon, 25 Jun 2001 09:34:01 +0200 (CEST),
Andreas Tille <tillea@rki.de> 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,
howeverPostgres 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  <http://www.math-hat.com/~zukerman/>

On 25 Jun 2001, Itai Zukerman wrote:

> 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:

Hmm, that paragraph should have been ripped out at the same time I added
the check.  Will see if I need to patch the docs (or if it was already
done by someone).

>   CREATE UNIQUE INDEX IX_IdLabNr_KulturDetail
>   ON ResKulturDetail(IdLabNr, IdIndex) ;
> 
> I'm not sure why this restriction is necessary...

Mostly because the semantics of the constraint as defined by the spec
don't make sense in certain cases against non-unique pk table rows.  For
example, a delete cascade would delete the *first* time a matched row was
deleted even if there were other rows that could be matched except in
MATCH PARTIAL (which we don't support).  Once we support MATCH PARTIAL,
we might make the case to allow MATCH PARTIAL references to non-unique
columns as an extension, but we'd need to think through the other effects
of that.