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.

Rémi Desgrange
  • 182
  • 1
  • 9

1 Answers1

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.

Lennart - Slava Ukraini
  • 23,240
  • 3
  • 32
  • 69
  • 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 - Slava Ukraini 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 - Slava Ukraini Nov 15 '17 at 21:51