Postgresql – Cascade delete on foreign key

postgresql

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.

Best Answer

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.

Related Question