for some of the records in the database, I could delete entries in table2 which were automatically deleted in table1. But for some cases, that didn't happen, even though they are identical (same tables, same foreign key reference, different values)
I get following error:
SQL Error [23503]: ERROR: update or delete on table "table2" violates foreign key constraint "table1_user_id_fk" on table "table1"
Detail: Key (id)=(1) is still referenced from table "table1".
The code I used:
First to remove constraint
alter table table1
drop constraint table1;
Add on delete cascade constraint
alter table table1
add constraint table1_user_id_fk
foreign key (fk_id)
references table2 (id)
on delete cascade;
When I perform a deletion like this
delete from table2 where col1 = 'someval';
It throws the error stated above.
Right now I can't wrap my head around it, because for different vals of col1, it does delete the record in table1 automatically, and for some it throws a constraint problem