Re: Integrity violation when adding foreign key constraint

Unfortunately there is a bug in 7.0.x's alter table add constraint
that gets the column definitions wrong (I had misinterpreted the
ordering of arguments to the trigger). I think I sent the patch
to someone over the mailing list so it should be in the archives.

On Mon, 26 Mar 2001, Daniel Hartmeier wrote:

> I hope somebody can help me with a question. I have two tables
>
> CREATE TABLE sk (
> fnr INTEGER,
> knr SMALLINT,
> [...]
> CONSTRAINT sk_pk_fnr_knr PRIMARY KEY ( fnr, knr )
> );
>
> CREATE TABLE sg (
> fnr INTEGER,
> knr SMALLINT,
> gpc CHAR(1),
> [...]
> CONSTRAINT sg_pk_fnr_knr_gpc PRIMARY KEY ( fnr, knr, gpc )
> );
>
> loaded with data. When I try to add a foreign key constraint with
>
> ALTER TABLE sg ADD CONSTRAINT sg_fk_fnr_knr FOREIGN KEY ( fnr, knr )
> REFERENCES sk;
>
> I get the error message
>
> NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for
> FOREIGN KEY check(s)
> ERROR: <unnamed> referential integrity violation - key referenced from
> sg not found in sk
>
> But when I try to find the offending row(s) in sg with
>
> SELECT * FROM sg WHERE NOT EXISTS ( SELECT * FROM sk WHERE
> sk.fnr = sg.fnr AND sk.knr = sg.knr );
>
> I get nothing (0 rows).
>
> This is PostgreSQL 7.0.3 on BSD, and I ran vacuum analyze on both tables
> first.
>
> Am I doing something wrong, or what might be a reason for what looks
> like a contradiction to me? Foreign keys on multiple columns work like
> this, don't they?
>
> Kind regards,
> Daniel
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>