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

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

"" <dueyduey@freemail.hu> writes:
> I am experiencing a very interesting, actually very frightening thing: I
> have a foreign key constraint along with rows violating it.

You have not provided enough information to let anyone reproduce or
debug the problem...

FWIW, we recently found a problem in VACUUM FULL that could result in
symptoms a bit similar to this --- do you routinely do VACUUM FULL while
the database is active?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org