The following bug has been logged online:
Bug reference: 3243
Logged by:
Email address: dueyduey@freemail.hu
PostgreSQL version: 8.1.8
Operating system: Linux: Ubuntu 6.06 (Dapper Drake)
Description: foreign key constraint not working?
Details:
I am experiencing a very interesting, actually very frightening thing: I
have a foreign key constraint along with rows violating it. I guess such a
thing should never happen (except for deferred constraints, which mine is
none).
Here is the definition of the constraint:
ALTER TABLE buclic.owndata ADD CONSTRAINT
rel_34 FOREIGN KEY (contacts_id)
REFERENCES public.contacts (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE;
I checked it:
select * from public.owndata o where not exists(select null from
public.contacts c where c.id = o.contacts_id);
and yes, I got a result set of 52 rows. And yes, the foreign key constraint
is on the table, and it is working (I tried to insert a new record with a
nonexistent contacts_id reference and I got the good old error message
psql:/var/tmp/mydumpfile:2818085: ERROR: insert or update on table
"owndata" violates foreign key contraint "rel_34"
DETAIL: Key (contacts_id)=(3008974) is not present in table "contacts".
and the insertion did not happen). And I checked all this using a new
connection (so deferred application of the constraint is impossible).
Please tell me I'm missing something (and also what)...
I cannot reproduce the problem. Dumping and restoring the whole database
will fail adding the foreign key constraint during restore, which is another
evidence of the original database being inconsistent.
thanx