Delete data that contains foreign key

Question

I usually work with foreign key for good practices and to maintain data integrity but I always face the following "problem" when trying to delete some data:

I have a scenario with a main group table: groups

And I have several other tables that relate to groups, for example:

user_group (relationship table between users and groups)

course_group (relationship table between courses and groups)

When deleting a group I always need to delete the reference to that group in the related tables before ... but currently I have several relationship tables and my exclusion code ends up getting quite large.

My question is: is there any function or library that I could use so that this work could be done more automatically? A function that may check for me all these references and already exclude. If anyone has any idea how to make it faster and with the smallest possible number of code thanks. Note: I am currently working with PHP

Answer 1

You want on delete cascade as an option in the foreign key constraint in the children table.

Say that table groups looks like:

create table groups (
    id int primary key,
    ... -- other columns here
);

You would then declare child table user_groups as:

create table user_groups (
    ... -- some columns here
    group_id int,
    foreign key (group_id) references groups(id) on delete cascade
)