0

Imagine 2 tables :

CREATE TABLE a (a_id serial primary key, a_text text);
CREATE TABLE b (
    b_id serial primary key, 
    b_aid int not null REFERENCES a ON DELETE CASCADE);

If I delete from b

 DELETE FROM b WHERE b_id=3

I though that the record in a was going to delete too. But in fact it's not. I though I understand this feature until now... Is there a way to achieve this without trigger ?

I didn't find anything about this elsewhere but maybe I didn't search well. So feel free to mark as duplicate if this question have already been answered.

EDIT: Some ask some more details

I have 26 tables (let's call it a,b,c,d,...,z) that reference to a table named log (this table is going to be partitioned by table name soon). ex :

CREATE TABLE a (
  a_id uuid primary key default uuid_generate_v4(),
  a_somefield text
  a_someotherfield int
  a_log uuid not null REFERENCES log (log_id) default f_create_log(a::name)
); 


CREATE TABLE log (
 log_id uuid primary key default uuid_generate_v4(),
 log_creation_date timestamptz not null default now(),
 log_creation_user text not null default user::text,
 log_update_date timestamptz 
 log_update_user text,
 log_delete_date timestamptz,
 log_delete_user text,
 table_name text
);

The result that I would like to have is "When I delete a record in "a" it suppress the log record too". If you are wondering why I have a log_delete_* then, this because I accessed the data mostly from view and not the actual table (so when you delete a record in the view, there is a trigger that record the user and the time you suppress the record).

I hope this is more clear by now. I now that this design is not the best one, but it is the simplest I found when I started and I needed a really simple solution.

2
  • it deletes in the parent to child direction only – Neil McGuigan Nov 15 '17 at 21:12
  • You might find my answer here helpful! – Vérace Nov 15 '17 at 21:25
1

It is the other way around, if you delete a row in a, the corresponding row in b will be deleted to. If a is a child table of b you should declare the foreign key in a.

Given your updated scenario, I guess an AFTER DELETE trigger for each table a,b,c,d,... is the best choice.

However, I would consider using 1 log-table per table together with an insert, update and delete trigger. Let the triggers do all the work manipulating the log-table, and let the application handle just the tables.

4
  • the real application is a cable (as b) table and a log (as a) table. So when I delete a cable, I want the log record to disappear as well. – Rémi Desgrange Nov 15 '17 at 21:19
  • so, declare the foreign key in the log (as a). You should probably update your question with descriptive names instead of a and b. – Lennart Nov 15 '17 at 21:23
  • So now imagine that I have 36 table pointing to log table, do I need to create 36 other column ? This is not really a good design. It seems that this kind of things are not possible without trigger then ? – Rémi Desgrange Nov 15 '17 at 21:29
  • 2
    You use one log table for 36 tables? That's a bit unorthodox and I don't see how you can use referential integrity for that scenario. You really should update your question so that it reflects your situation. A minimalistic example with two tables and a log table should be sufficient. – Lennart Nov 15 '17 at 21:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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