The following bug has been logged online:
Bug reference: 3243
Email address: dueyduey(at)freemail(dot)hu
PostgreSQL version: 8.1.8
Operating system: Linux: Ubuntu 6.06 (Dapper Drake)
Description: foreign key constraint not working?
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
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.
pgsql-bugs by date
|Next:||From: William Lawrance||Date: 2007-04-19 14:23:22|
|Subject: BUG #3244: problem with PREPARE|
|Previous:||From: Marcin Waldowski||Date: 2007-04-19 12:50:19|
|Subject: Re: BUG #3242: FATAL: could not unlock semaphore: error code