patternsqlMinor

Is there a way to allow only DELETES from CASCADES only?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

fromdeleteswayallowcascadesthereonly

Problem

I want to prevent anyone from deleting certain records from a table, except for when the parent record in another table gets deleted, then should it be deleted. Can I use triggers for this purpose?

Solution

For that you can use Foreign keys (referential integrity constraints):

REFERENCES orders ON DELETE CASCADE,

Restricting and cascading deletes are the two most common options. RESTRICT prevents deletion of a referenced row.

See the manual

I explain it a bit better.

You have a parent table A with an id, and you have a second table B, which has a reference to the id in Table A with CASCADE DELETE.

As the check constraints don't support referencing other tables, you can use a trigger with a function:

CREATE FUNCTION delete_tabentry () RETURNS trigger AS $
BEGIN
  IF EXISTS(SELECT 1 FROM parent WHERE id= OLD.id ) THEN
    RAISE NOTICE 'Cannot delete order that is partially or completely done.';
  ELSE
    RETURN OLD;
  END IF;
END;
$ LANGUAGE PLPGSQL;

CREATE TRIGGER before_delete_child
  BEFORE DELETE ON child
  FOR EACH ROW EXECUTE PROCEDURE delete_tabentry();

This would prevent deletion from child until its parent record is deleted.

Code Snippets

CREATE FUNCTION delete_tabentry () RETURNS trigger AS $$
BEGIN
  IF EXISTS(SELECT 1 FROM parent WHERE id= OLD.id ) THEN
    RAISE NOTICE 'Cannot delete order that is partially or completely done.';
  ELSE
    RETURN OLD;
  END IF;
END;
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER before_delete_child
  BEFORE DELETE ON child
  FOR EACH ROW EXECUTE PROCEDURE delete_tabentry();

Context

StackExchange Database Administrators Q#278440, answer score: 3

Revisions (0)

No revisions yet.