CODEWITHSUNDEEP
sqlpostgresqltriggersforeign-keyssql-delete
Sagar
Sagar

Reputation: 5596

Conditional CASCADE operation for foreign key constraint?

I have parent and child table where child has a FK pointing to the PK of parent table. When I delete something in parent table I can have child records deleted as well by having ON DELETE CASCADE.

However, in my parent table I don't delete records at all. Instead I set the column state = "passive". I want to delete related entries in the child table.

Do we have something like a "conditional CASCADE" in Postgres? Or is the solution to manually delete entries in the child table?

Upvotes: 2

Views: 2194

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656804

There is nothing like "conditional CASCADE". The closest thing that comes to mind would be to disable triggers. But that's not helpful in your case.

Assumptions:
- state is defined NOT NULL.
- parent_id never changes. If it does you'll want to cascade that UPDATE as well.

The condition to fire the trigger ON UPDATE should be:

     NEW.state =  "passive"
AND  OLD.state <> "passive"

.. since you do not want to trigger it over and over again, only once when parent is set to "passive".

CREATE OR REPLACE FUNCTION trg_upbef()
  RETURNS TRIGGER AS
$func$
BEGIN

DELETE FROM child
WHERE  parent_id = OLD.parent_id;  -- OLD works for UPDATE & DELETE

RETURN NEW;
END
$func$ LANGUAGE plpgsql;

Instead of checking the condition in the trigger function, you can do that in the trigger directly since Postgres 9.0, thereby saving a bit of overhead:

CREATE TRIGGER upd_cascade_del
BEFORE UPDATE ON parent
FOR EACH ROW
WHEN (NEW.state =  "passive" AND
      OLD.state <> "passive")      -- parenthesis required
EXECUTE PROCEDURE trg_upbef();

Don't forget to add a trigger for ON DELETE as well. You don't normally DELETE, but if you do, you want to either raise an exception or cascade the operation.
Trigger function has to RETURN OLD, since NEW is not defined in this case. Otherwise the same:

CREATE OR REPLACE FUNCTION trg_delbef()
...    
RETURN OLD;
...
$func$ LANGUAGE plpgsql;


CREATE TRIGGER del_cascade_del
BEFORE DELETE ON parent
FOR EACH ROW
WHEN (OLD.state <> "passive")      -- only if not already passive
EXECUTE PROCEDURE trg_delbef();

Upvotes: 0

Kirk Roybal
Kirk Roybal

Reputation: 17837

You would have to do this in a trigger that takes action ON UPDATE. Where the NEW.state = "passive", delete the child rows.

Upvotes: 1

Related Questions