The following bug has been logged online: Bug reference: 3243 Logged by: Email address: [EMAIL PROTECTED] 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
