foreign key violation within trigger on insert of 'new' id in other table as foreign key

Question

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

How is the "'Discarding 3NF With Triggers" relevant? They have only 1 table in their example. — ypercubeᵀᴹ, Feb 15 '17 at 21:46
Also, do you know hat you can insert into 2 or more tables in Postgres, in one statement (using CTEs)? — ypercubeᵀᴹ, Feb 15 '17 at 21:46
Link relevance: someone looking at this Q/A may wonder why I am doing this. Also playing defense, so someone doesn't ask "why are you even doing this". insert into 2 tables: No i didn't. Here is a link for someone looking at this: http://stackoverflow.com/questions/20561254/insert-data-in-3-tables-at-a-time-using-postgres — mountainclimber11, Feb 15 '17 at 22:04
Yes, see also mine (and best Erwin's again!) answer in this one, about CTEs and inserts in multiple tables (so without the need of deferred): http://dba.stackexchange.com/questions/46410/how-do-i-insert-a-row-which-contains-a-foreign-key/46415#46415 — ypercubeᵀᴹ, Feb 15 '17 at 22:35
And a simple example with deferrable constraints: http://dba.stackexchange.com/questions/152734/how-to-enforce-that-a-parent-must-have-at-least-one-child-when-the-pk-is-composi/152737#152737 — ypercubeᵀᴹ, Feb 15 '17 at 22:36

joanolo · Accepted Answer · 2017-02-16T20:17:12.263

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:

SET CONSTRAINTS sets the behavior of constraint checking within the current transaction. IMMEDIATE constraints are checked at the end of each statement. DEFERRED constraints are not checked until transaction commit. Each constraint has its own IMMEDIATE or DEFERRED mode.

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 them INTIALLY 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:

ALTER TABLE table_a
    ADD CONSTRAINT table_a_col_name_table_b_fk FOREIGN KEY (col_name)
    REFERENCES table_b (col_name)
    ON UPDATE NO ACTION   /* or whichever action is deemed appropriate */
    ON DELETE NO ACTION   /* or whichever action is deemed appropriate */
    DEFERRABLE INITIALLY DEFERRED;

This way, the FK constraint is checked, by default, at the end of the transaction, and the SET CONSTRAINT isn't needed.

I didn't know this was possible. Very cool. You can't do begin/commit in a function (as far as I know), so I assume the begin/commit is implied in my case because it is a single insert. In other words, a DEFERRABLE INITIALLY DEFERRED fk would work for my situation, right? — mountainclimber11, Feb 15 '17 at 22:08
Right. I think you could also use SET CONSTRAINT within the trigger function, and that would work as well. You are within a transaction, even if it is an implicit one. This setting won't work any longer once your INSERT is done, because you're then out of the trasnaction. — joanolo, Feb 15 '17 at 22:35
I think a key to this if one wants to conditionally defer is adding DEFERRABLE when you create the fk. Then you can use set constraints. That wasn't immediately clear to me with your answer. This link helped me: http://stackoverflow.com/questions/20198011/how-to-change-a-table-constraint-to-deferrable-in-postgresql — mountainclimber11, Feb 16 '17 at 14:44

foreign key violation within trigger on insert of 'new' id in other table as foreign key

1 Answers1