Best practices for deleting records in multiple tables

Hi everyone hope you are all well.
Guys im struggling with the concept of the best practices in MySQL to delete records from multiple tables in the one go.

I posted a previous question whereby some of you nice gurus said a way of doing this is to use
ON DELETE CASCADE
but they dont use this method in applications, they only use it for database cleanup.

My question is........... if this is only used for database cleanup, then if you have to remove records from multiple tables, then if you dont use the ON DELETE CASCADE, then what do you use if you have FOREIGN KEYS?

Basically, I want to be able to:
# Delete a project even if it has associated tasks in the currenttasks_ctk table, but NOT have those tasks orphaned.
# Delete a task in the currenttasks_ctk table and not delete the project from the projects_pro table.

For example, I built the below 2 tables, and the only reason I added a foreign key to the currenttasks_ctk table was so that if I added a new task, then the record will only be allowed if it is associated with a project. I also dont want orphaned tasks records (that is, dont have an associated project).  If my design is flawed, I would love your help, and would love to know what you guys normally do in cases like this?

Do you guys suggest that I should modify my table design somehow?

Currently,

# I CAN remove tasks from the currenttasks_ctk table without removing the project this task is associated with.
# I CANNOT remove project records, and im using the following for example.
delete from projects_pro where id_pro = '10';

The error i get is:

Cannot delete or update a parent row: a foreign key constraint fails (`tasksdb/currenttasks_ctk`, CONSTRAINT `currenttasks_ctk_ibfk_1` FOREIGN KEY (`fk_id_pro_ctk`) REFERENCES `projects_pro` (`id_pro`))

I have been advised that using the ON CASCADE DELETE is not recommended, but dont know what else to do in the situation where you use foreign keys.
If you guys use foreign keys, what do you guys do if you need to delete records if you dont use ON CASCADE?


Any help on this greatly appreciated.
Start Free Trial

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:

/**********************************
Create the projects_pro table:
***********************************/
 
create table projects_pro (
id_pro int(4) not null auto_increment,
name_pro varchar(65) not null,
primary key (id_pro),
unique (name_pro)
)
engine=innodb;
 
/**********************************
Create the currenttasks_ctk table:
***********************************/
 
create table currenttasks_ctk (
id_ctk int(4) not null auto_increment,
name_ctk varchar(65) not null,
fk_id_pro_ctk int(4) not null,
description_ctk text,
foreign key (fk_id_pro_ctk) references projects_pro (id_pro),
primary key (id_ctk, fk_id_pro_ctk),
unique (name_ctk)
)
engine=innodb;