Call Us: +1-888-227-1645

Relational Deletes in MySql and Foreign Keys

Eric Meyer

3 min read

Managing relational tables in a database is a topic that all developers will eventually bump into. There are quite a few ways of managing relational data, and the best solution sometimes can be hard to find. As a developer you have to look at the scope of the application, the types of tools you have on hand, and where you want the responsibility for the referential integrity to reside. The most often used ways of maintaining referential integrity are as follows:

One pitfall I want to mention here is one that some inexperienced coder might fall into, and thats the concept of looping over what should be a JOIN query to produce the same results. Take the example of a table of customers, with a one-to-many relationship to the orders via a relational table to info stored in an orders table. (Each customer has multiple orders.) If the deletion of a customer record mandated the removal of all of their orders from the system, a coder may be temped to retrieve the customer’s id, then query the relational table for all orders that belong to the customer. Once that query is complete the developer will use a loop and a delete query in the application logic to perform the delete. It sounds crazy, but I’ve fixed a ton of applications that suffer from this type of coding.

There are several problems with the above example. The most important issue deals with integrity. If any part of the above fails or is interrupted you will end up with orphaned records in the database. Since the type of coding that produces orphans, rarely provides for their cleanup, you will tend to build a backlog of dead records in your table. That's no good. To a lesser extent, there are serious performance issues to deal with if you are running a loop of deletes against a table.

An overlooked solution to the above issue is that you can use a JOIN in a DELETE just as you can a SELECT. Using the customer / order relation described above, one could do the following:

DELETE orders.*, ordersRelativetoCustomers.*
FROM ordersRelativetoCustomers
LEFT JOIN orders
ON ordersRelativetoCustomers.orderId = orders.orderId
WHERE ordersRelativetoCustomers.customerId= ’12’

Using the code above you can delete multiple orders from a single customer without relying on a application logic loop.

Let's build something amazing together

Give us a ring and let us know how we can help you reach your goals. Or if you'd like, start a chat. We're usually available 9-5 EST. We try to respond to every inquiry within one business day.

Phone number
+1-888-227-1645

Technologies and services we work with:

Laravel Laravel
WordPress WordPress
React ReactJS
EmberJS EmberJS
woocommerce WooCommerce
next.js NextJS
gatsby Gatsby
Shopify Shopify
VueJs VueJS
contentful Contentful
next.js JAMStack
gatsby Laravel Jigsaw
WPEngine WP Engine
Laravel Livewire Laravel Livewire
Netlify Netlify