Thread: BUG #1930: Cannot create foreign key constraint

The following bug has been logged online:

Bug reference:      1930
Logged by:          Warren Little
Email address:      warren.little@reardeninc.com
PostgreSQL version: 8.0.3
Operating system:   2.6.10-1.770_FC3smp #1 SMP Thu Feb 24 18:36:43 EST 2005
x86_64 x86_64 x86_64 GNU/Linux
Description:        Cannot create foreign key constraint
Details:

pg_restore generates the following error:
pg_restore: [archiver (db)] Error from TOC entry 2950; 16386 17827531 FK
CONSTRA INT asientry_r0 tigris
pg_restore: [archiver (db)] could not execute query: ERROR:  insert or
update on  table "asientry" violates foreign key constraint "asientry_r0"
DETAIL:  Key (pid)=(AA0003403544) is not present in table "pobject".
    Command was: ALTER TABLE ONLY asientry
    ADD CONSTRAINT asientry_r0 FOREIGN KEY (pid) REFERENCES pobject(pid);


Also running the alter table after restore generates same constraint
violation error.

The following query returns one row:

select * from asientry, pobject
where pobject.pid = 'AA0003403544'
and pobject.pid = asientry.pid;

Any ideas

"Warren Little" <warren.little@reardeninc.com> writes:
> pg_restore: [archiver (db)] could not execute query: ERROR:  insert or
> update on  table "asientry" violates foreign key constraint "asientry_r0"
> DETAIL:  Key (pid)=(AA0003403544) is not present in table "pobject".
>     Command was: ALTER TABLE ONLY asientry
>     ADD CONSTRAINT asientry_r0 FOREIGN KEY (pid) REFERENCES pobject(pid);

> Also running the alter table after restore generates same constraint
> violation error.

> The following query returns one row:

> select * from asientry, pobject
> where pobject.pid = 'AA0003403544'
> and pobject.pid = asientry.pid;

That seems a bit odd.  Are the two pid columns exactly the same datatype
(and what is it anyway)?  What plan does EXPLAIN show for that SELECT,
and what plan do you see for

SELECT fk.pid FROM ONLY asientry fk
  LEFT OUTER JOIN ONLY pobject pk
  ON pk.pid=fk.pid
  WHERE pk.pid IS NULL AND fk.pid IS NOT NULL;

(which is what is probably being used to test foreign key validity)?

            regards, tom lane