BUG #3243: foreign key constraint not working?

From: "" <dueyduey(at)freemail(dot)hu>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #3243: foreign key constraint not working?
Date: 2007-04-19 13:56:41
Message-ID: 200704191356.l3JDufEo052753@wwwmaster.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


The following bug has been logged online:

Bug reference: 3243
Logged by:
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?
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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message William Lawrance 2007-04-19 14:23:22 BUG #3244: problem with PREPARE
Previous Message Marcin Waldowski 2007-04-19 12:50:19 Re: BUG #3242: FATAL: could not unlock semaphore: error code 298