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.