0

I have a existing database with several tables, unfortunately there ins't any data load functionality provided for said database and I am trying to rectify this issue.

I have an insert function that processes and inserts the data we need as needed into the relevant tables and sets up the interlinking fkeys that link all the new data together.

the insert statement is set up to return the new primary keys of all the new rows its created a records table to allow rollback. What I want to do is set up a function where if a row in the records table is deleted it will also delete all its referencing rows in the rest of the database (without having to change all the other tables)

example : http://rextester.com/SKJUJ87661

basically when I run delete from t where tid = 1; I want corresponding data in t(upload records) and u(tables where data was created) to be deleted and v(existing reference tables) untouched

and yes, I know I can just run delete from u where uid = (select t.uid from t where t=1); for the relevant tables, but I'm looking for a more elegant solution then writing a massive delete statement (in the actual system t references 18 rows)

update

using triggers i can do this http://rextester.com/MCDGR84399

but how do I make the trigger scroll through all the Fkeys that t is linked to instead of just one

update = 2

my basic question is there a prettier way to do this :

CREATE OR REPLACE FUNCTION delete_cascade() RETURNS trigger AS
$$BEGIN
   delete from u 
   where u.uid = OLD.uid;
   delete from c 
   where c.cid = OLD.cid;
   delete from b 
   where b.bid = OLD.bid;
   delete from a 
   where a.aid = OLD.aid;
   return OLD;
END;$$ LANGUAGE plpgsql;

CREATE TRIGGER t_cascade
   AFTER DELETE ON t FOR EACH ROW
   EXECUTE PROCEDURE delete_cascade();

example code http://rextester.com/UAZU30980

1

You can cascade action by defining a Foreign Key to those tables. Here is an example from Postgresql documentation

CREATE TABLE products (
    product_no integer PRIMARY KEY,
    name text,
    price numeric
);

CREATE TABLE orders (
    order_id integer PRIMARY KEY,
    shipping_address text,
    ...
);

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE CASCADE,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

Here, deleting product or order deletes all corresponding items in order_items. You can apply the same for your case.

In case you want to delete all items in primary table and delete corresonding record, it is possible with Truncate cascade. Something like

TRUNCATE TABLE products CASCADE

This will delete all data in products plus all dependent tables.

Apart form these two, you have to somehow manage the deletion yourself. I would in that case use scripting language, and I don't know much about PL/SQL

  • I'm trying to do the exact opposite. I want it set up so that (in that case) when I delete from order_items rows in order and items are deleted. – mai Aug 3 '18 at 14:51
  • In that case (as strange that is), reverse the Constraint. order_items becomes the main table and orders PK becomes also a foreign key referencing order_items.order – Stefano Mtangoo Aug 4 '18 at 12:10

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.