Solved

Best practices for deleting records in multiple tables

Posted on 2008-10-06
5
327 Views
Last Modified: 2010-05-18
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.

/**********************************

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;

Open in new window

0
Comment
Question by:Simon336697
  • 3
  • 2
5 Comments
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 22655561
Without cascade delete, you must delete the child rows first:

delete from currenttasks_ctk_ibfk_1
   where fk_id_pro_ctk = '10';

delete from projects_pro where id_pro = '10'

0
 
LVL 42

Expert Comment

by:dqmq
ID: 22655624
But I use Cacade Delete all the time and disagree with the unilateral advice you've received.   I don't use it for lookup tables, I think carefully about intersection tables (and then generally only use it on one side), but for relationships like these, I usually have no problem with it:

One Car has many Inspections
One Person has many Phones
One Project has many Tasks

It's really a question about what business rules you are depending on your database design to enforce.  If Cascade Delete does not compromise any of them, then go for it.
0
 
LVL 42

Expert Comment

by:dqmq
ID: 22655630
BTW, if Cascade Delete is disabled to enforce business rules, then database cleanup is a poor reason to enable it.  Talk about asking for trouble!

0
 
LVL 1

Author Comment

by:Simon336697
ID: 22655717
Hi dqmq, mate thank you so much for that.
That makes perfect sense mate.
Really appreciate it.
0
 
LVL 1

Author Closing Comment

by:Simon336697
ID: 31503665
Thank you dqmq champion :>)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now