| 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: | Whole Thread | Raw Message | 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
| 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 |