Within a trigger I am inserting the id of the table the trigger is assigned to as a foreign key in another table, but the 'new' id doesn't exist yet, so I get a foreign key constraint violation.
Here is a simplified version:
CREATE or replace FUNCTION trg_func()
RETURNS trigger
LANGUAGE 'plpgsql'
AS $BODY$
DECLARE
BEGIN
INSERT INTO other_tbl (
my_tbl_fk
,my_calc
)
SELECT new.id -- <- this one is the issue. Is fk in other_tbl & pk in my_tbl
,a.some_val / b.some_val calc
FROM (...subquery here...) a
,(...subquery here...) b
WHERE a.some_id = b.some_id;
RETURN new ;
END ;
$BODY$ ;
CREATE TRIGGER my_tbl_ins_upd_trg
BEFORE INSERT OR UPDATE
ON my_tbl
FOR EACH ROW
EXECUTE PROCEDURE trg_func();
I tried temporarily removing the fk before the insert then adding it back, but it doesn't seem to help.
I can remove the fk, trigger the trigger, then add the fk back again, so I know my insert values don't violate the constraint if applied generally.
I am guessing there is a method or strategy for doing this because it seems like something that would be done a lot for calculated fields via trigger.
I am trying to do the 'Discarding 3NF With Triggers' section of this link:
http://database-programmer.blogspot.com/2008/01/database-skills-third-normal-form-and.html
PostgreSQL 9.3
Best Answer
That should be simply achieved by setting the foreign key constraints to be checked at the end of the transaction. This is achieved by means of a
SET CONSTRAINTS ALL DEFERRED
(or, at least,SET CONSTRAINTS name_of_fk_constraint DEFERRED
.As per PostgreSQL (9.3) documentation about
SET CONSTRAINTS
:If you want to use this feature, you need to create your constraints with the
DEFERRABLE
option. If you intend to use them mostly in deferred mode, it is a good idea to make themINTIALLY DEFERRED
. By default, constraints are NOT DEFERRABLE.It is advisable to make constraint names unique (PostgreSQL doesn't force you to; they only need to be unique per table), and to create the names explictily. This way, you can control its deferrability with guarantee that you always specify the right one.
If you want a specific FK constraint to behave already as DEFERRED, you can declare it with:
This way, the FK constraint is checked, by default, at the end of the transaction, and the
SET CONSTRAINT
isn't needed.