Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group