CODEWITHSUNDEEP
postgresqlforeign-keyssql-deletecascade
Dave
Dave

Reputation: 19150

In Postgres, how can I delete a row from table B when a row from table A is deleted?

I’m using Postgres 9.5.0. I have the following table

myproject=> \d my_objects;
                                  Table "public.my_objects"
       Column        |            Type             |              Modifiers              
---------------------+-----------------------------+-------------------------------------
 name                | character varying           | 
 day                 | date                        | 
 distance            | double precision            | 
 user_id             | integer                     | 
 created_at          | timestamp without time zone | not null
 updated_at          | timestamp without time zone | not null
 distance_unit_id    | integer                     | 
 import_completed    | boolean                     | 
 id                  | character varying           | not null default uuid_generate_v4()
 linked_my_object_time_id | character varying           | 
 web_crawler_id      | integer                     | 
 address_id          | character varying           | 
Indexes:
    "my_objects_pkey" PRIMARY KEY, btree (id)
    "index_my_objects_on_user_id_and_day_and_name" UNIQUE, btree (user_id, day, name)
    "index_my_objects_on_user_id" btree (user_id)
    "index_my_objects_on_web_crawler_id" btree (web_crawler_id)
Foreign-key constraints:
    "fk_rails_5287d445c0" FOREIGN KEY (address_id) REFERENCES addresses(id) ON DELETE CASCADE
    "fk_rails_970b2325bf" FOREIGN KEY (distance_unit_id) REFERENCES distance_units(id)
    "fk_rails_dda3297b57" FOREIGN KEY (linked_my_object_time_id) REFERENCES my_object_times(id) ON DELETE CASCADE
    "fk_rails_ebd32625bc" FOREIGN KEY (web_crawler_id) REFERENCES web_crawlers(id)
    "fk_rails_fa07601dff" FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE

Right now, each my_object has an address field. What I would like is when I delete the my_object, the corresponding address entry be deleted as well. Without moving the address_id column out of the my_objects table, is it possible to set something up such that when I delete a row from the my_objects table, any corresponding address data is deleted as well? Obviously, the foreign key I have set up will not get the job done.

Upvotes: 0

Views: 132

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246533

You can do this with a trigger:

CREATE OR REPLACE FUNCTION remove_address() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   DELETE FROM public.addresses WHERE id = OLD.address_id;
   RETURN OLD;
END;$$;

CREATE TRIGGER remove_address
   AFTER DELETE ON public.my_objects FOR EACH ROW
   EXECUTE PROCEDURE remove_address()

Upvotes: 3

Related Questions