cascading_deletes.sql

DROP TABLE test_notes;
DROP TABLE billing_discount_notes;
drop table billing_notes;
DROP TABLE billing_discounts;

CREATE TABLE billing_notes (
     id SERIAL PRIMARY KEY,
     note text,
     created TIMESTAMP WITHOUT TIME ZONE,
     updated TIMESTAMP WITHOUT TIME ZONE
  );

CREATE TABLE billing_discounts (
        id SERIAL PRIMARY KEY,
        amount integer,
        type character varying(40),
        created TIMESTAMP WITHOUT TIME ZONE,
        updated TIMESTAMP WITHOUT TIME ZONE
     );

CREATE OR REPLACE FUNCTION cascade_delete_note()
RETURNS trigger AS
$BODY$
BEGIN
     DELETE FROM billing_notes WHERE id = OLD.note_id;
     RETURN NEW;
   EXCEPTION WHEN foreign_key_violation THEN
      RETURN NEW;

END;
$BODY$ LANGUAGE plpgsql;


  CREATE TABLE billing_discount_notes (
    discount_id integer REFERENCES billing_discounts ON DELETE CASCADE,
    note_id integer REFERENCES billing_notes ON DELETE RESTRICT
   );

  CREATE TABLE test_notes (
    note_id integer REFERENCES billing_notes ON DELETE RESTRICT
   );

  CREATE TRIGGER trigger_billing_discount_notes_cascade_delete AFTER DELETE ON billing_discount_notes FOR EACH ROW EXECUTE PROCEDURE cascade_delete_note();

insert into billing_notes (note) values ('hello note');

insert into billing_discounts (amount, type) values (200, 'absolute');

insert into billing_discount_notes (discount_id, note_id) values (1, 1);

insert into test_notes (note_id) values (1);