Foreign keys in postgresql can be violated by trigger

I tried to create a simple example that shows foreign key constraint being enforced. With this example I prove I'm not allowed to enter data that violates the fk and I prove that if the fk is not in place during insert, and I enable the fk, the fk constraint throws an error telling me data violates the fk. So I'm not seeing how you have data in the table that violates a fk that is in place. I'm on 9.0, but this should not be different on 8.3. If you can show a working example that proves your issue that might help.

--CREATE TABLES--CREATE TABLE parent(  parent_id integer NOT NULL,  first_name character varying(50) NOT NULL,  CONSTRAINT pk_parent PRIMARY KEY (parent_id))WITH (  OIDS=FALSE);ALTER TABLE parent OWNER TO postgres;CREATE TABLE child(  child_id integer NOT NULL,  parent_id integer NOT NULL,  first_name character varying(50) NOT NULL,  CONSTRAINT pk_child PRIMARY KEY (child_id),  CONSTRAINT fk1_child FOREIGN KEY (parent_id)      REFERENCES parent (parent_id) MATCH SIMPLE      ON UPDATE CASCADE ON DELETE CASCADE)WITH (  OIDS=FALSE);ALTER TABLE child OWNER TO postgres;--CREATE TABLES----INSERT TEST DATA--INSERT INTO parent(parent_id,first_name)SELECT 1,'Daddy'UNION SELECT 2,'Mommy';INSERT INTO child(child_id,parent_id,first_name)SELECT 1,1,'Billy'UNION SELECT 2,1,'Jenny'UNION SELECT 3,1,'Kimmy'UNION SELECT 4,2,'Billy'UNION SELECT 5,2,'Jenny'UNION SELECT 6,2,'Kimmy';--INSERT TEST DATA----SHOW THE DATA WE HAVE--select parent.first_name,       child.first_namefrom parentinner join child        on child.parent_id = parent.parent_idorder by parent.first_name, child.first_name asc;--SHOW THE DATA WE HAVE----DELETE PARENT WHO HAS CHILDREN--BEGIN TRANSACTION;delete from parentwhere parent_id = 1;--Check to see if any children that were linked to Daddy are still there?--None there so the cascade delete worked.select parent.first_name,       child.first_namefrom parentright outer join child        on child.parent_id = parent.parent_idorder by parent.first_name, child.first_name asc;ROLLBACK TRANSACTION;--TRY ALLOW NO REFERENTIAL DATA IN--BEGIN TRANSACTION;--Get rid of fk constraint so we can insert red headed step childALTER TABLE child DROP CONSTRAINT fk1_child;INSERT INTO child(child_id,parent_id,first_name)SELECT 7,99999,'Red Headed Step Child';select parent.first_name,       child.first_namefrom parentright outer join child        on child.parent_id = parent.parent_idorder by parent.first_name, child.first_name asc;--Will throw FK check violation because parent 99999 doesn't exist in parent tableALTER TABLE child  ADD CONSTRAINT fk1_child FOREIGN KEY (parent_id)      REFERENCES parent (parent_id) MATCH SIMPLE      ON UPDATE CASCADE ON DELETE CASCADE;ROLLBACK TRANSACTION;--TRY ALLOW NO REFERENTIAL DATA IN----DROP TABLE parent;--DROP TABLE child;

Everything I've read so far seems to suggest that constraints are only checked when the data is inserted. (Or when the constraint is created) For example the manual on set constraints.

This makes sense and - if the database works properly - should be good enough. I'm still curious how I managed to circumvent this or if I just read the situation wrong and there was never a real constraint violation to begin with.

Either way, case closed :-/

------- UPDATE --------

There was definitely a constraint violation, caused by a faulty trigger. Here's a script to replicate:

-- Create master tableCREATE TABLE product(  id INT NOT NULL PRIMARY KEY);-- Create second table, referencing the firstCREATE TABLE example(  id int PRIMARY KEY REFERENCES product (id) ON DELETE CASCADE);-- Create a (broken) trigger function--CREATE LANGUAGE plpgsql;CREATE OR REPLACE FUNCTION delete_product()  RETURNS trigger AS$BODY$    BEGIN      DELETE FROM product WHERE product.id = OLD.id;      -- This is an error!      RETURN null;    END;$BODY$  LANGUAGE plpgsql;-- Add it to the second tableCREATE TRIGGER example_delete  BEFORE DELETE  ON example  FOR EACH ROW  EXECUTE PROCEDURE delete_product();-- Now lets add a rowINSERT INTO product (id) VALUES (1);INSERT INTO example (id) VALUES (1);-- And now lets delete the rowDELETE FROM example WHERE id = 1;/*Now if everything is working, this should return two columns:(pid,eid)=(1,1). However, it returns only the example id, so(pid,eid)=(0,1). This means the foreign key constraint on theexample table is violated.*/SELECT product.id AS pid, example.id AS eid FROM product FULL JOIN example ON product.id = example.id;