Breaking PostgreSQL foreign key referential integrity with triggers

foreignKeyTriggerInterference.sql

--------------------------------------------------
-- setup
--------------------------------------------------

CREATE TABLE a (
  id      integer PRIMARY KEY
  -- whatever else
);

CREATE TABLE b (
 -- whatever else
  a_id    integer NOT NULL REFERENCES a ON DELETE CASCADE
);

INSERT INTO a VALUES(1);
INSERT INTO b(a_id) VALUES(1);



--------------------------------------------------
-- normally deleting from a cascades to b properly
--------------------------------------------------

SELECT *
  FROM b
    JOIN a
      ON b.a_id=a.id;

--  a_id | id
-- ------+----
--     1 |  1
-- (1 row)

DELETE FROM a WHERE id = 1;

SELECT *
  FROM b
    LEFT JOIN a
      ON b.a_id=a.id;

--  a_id | id
-- ------+----
-- (0 rows)


--------------------------------------------------
-- but cascading deletes are managed as triggers
-- so other triggers can interfere
--------------------------------------------------


CREATE OR REPLACE FUNCTION do_something() RETURNS TRIGGER AS $$
  BEGIN
    RETURN NULL;
  END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER deletion_from_b
  BEFORE DELETE ON b
  FOR EACH ROW
  EXECUTE PROCEDURE do_something();

INSERT INTO a VALUES(1);
INSERT INTO b(a_id) VALUES(1);

SELECT *
  FROM b
    JOIN a
      ON b.a_id=a.id;

--  a_id | id
-- ------+----
--     1 |  1
-- (1 row)

DELETE FROM a WHERE id = 1;

-- DELETE 1
-- no error raised

SELECT *
  FROM b
    LEFT JOIN a
      ON b.a_id=a.id;

--  a_id | id
-- ------+----
--     1 |
-- (1 row)

-- There's now an entry in table b referencing a non-existant value in table a