Let’s go back to our database of runners as an example.

Imagine having to remove a runner from the database. For privacy reasons, when a runner is removed from the database, we have to remove all of their race records too. Let’s say that Calvin Morris is leaving the club and we have to remove his account and records.

The first step could be to figure out what his runner_id is.

With the code above, we could confirm, yes, Calvin Morris is an existing member, and his runner_id is “4.” So we can then move forward in removing his record from the runner table. Right? Try the DELETE command as below.

Did this command work? If not, what happened?

In MySQL Workbench, you might see an error code as below. If the message is too long to view in the field space, you could right click, copy the message and view it elsewhere.

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (test_database_3.race, CONSTRAINT race_ibfk_1 FOREIGN KEY (runner_id) REFERENCES runner (runner_id))

You could see that MySQL is not letting you just delete an account that has it’s primary key being used as a foreign key in a different table, in this case in the race table. This is a good thing, and it’s a feature in MySQL to support the integrity and consistency of the database. Yet obviously, removing an account is something that you should be able to do.

Before we move on, let’s briefly cover this situation and how we work through it. There are several methods to remove the account in situations such as this.

The first method is to turn off this feature of MySQL in checking foreign keys. This could be done by the commands below.

SET FOREIGN_KEY_CHECKS=0;
DELETE FROM runner WHERE runner_id = ‘4’;
SET FOREIGN_KEY_CHECKS=1;

This “works”, but you’re correct if this doesn’t feel like it’s the best way to do things. You’re forcing a command by turning off a safety feature that exists for a reason. Also, if something goes amiss afterwards in the next steps, you could end up with a mess.

Another method to work through this is to define ON DELETE CASCADE for a table. This will make it so that when a primary key is deleted, the references of that primary key being used as a foreign key will also be deleted.

For the above code to work, you would have had to specified this feature when you were creating the table like below. Or you could use the ALTER TABLE, if you already have a table that you don’t want to recreate again. ALTER TABLE is used when you wish to change parameters and properties of the table after it’s creation. In this specific case of adding in ON DELETE CASCADE, we would have to drop the constraint on the foreign key, and add a new constraint with ON DELETE CASCADE. We could see that is another way to make it “work”, but alter a table that already in use could be quite risky and not always the best decision. Additionally, adding DELETE CASCADE, could be the right decision, but it’s also a critical and impactful decision that needs a thorough judgement call before committing to. You may not want to relinquish the detailed control over the database unless you are 100% certain about the consequences. For instance, maybe in some cases your stakeholders will want to keep the records of races for members that left the club?

So for our example, we will approach this in the third method, which is delete the race records of the account first, then delete the runner account. This way, there is will be do dependency or constraints, so we will be able to the delete the account with no issues.

Take a look at the code above. First you could notice that we’ve started a TRANSACTION, and at the end, used COMMIT. This way, all the code in between will be considered as a unified group and the all or none will be committed. You could also notice that there currently isn’t ay code in between, but we’ve started to use comments within SQL code. The syntax we use for comments in SQL code is “—” and anything after “—” will be ignored code-wise.

Now what do you think of the code above? At first glance, maybe things look ok? It basically put into sequence when we’ve done step by step, right? But when you think about it, a major issue in the code above is that we’ve confirmed the existence of the account for “Calvin Morris” and obtained the runner_id in the first command, and we are assuming that we already know that the runner_id was “4.”

(FYI, It’s going to get pretty exciting!)

We are now going to bring in a few powerful concepts in MySQL. Procedures, parameters, variables and conditionals.

Take a careful look at the code structure above. It starts with declaring a delimiter. This is a technique where we define a delimiter other than the default “;” so that we could define, in this case, a procedure. Usually in MySQL, developers tend to use “//” (or sometimes “$$”) as it’s most probably not used in code. You could see that the procedure is concluded by the “//” and the delimiter is set back to “;”. This technique makes MySQL understand everything before the “//” as one command, but within the procedure “;”s are still used as delimiters during execution.

You could also see that we are creating a procedure. This procedure will take IN a parameter named “runner_to_delete” which will be a varchar(256). You could imagine that we will be providing it the name of the runner’s account that we wish to remove the account and also the race results. This is also evident with how the procedure is CALLed as in the example at the bottom. Also, you could see that the procedure has a BEGIN and an END.

Now let’s read through the code above. There’s a couple points of syntax to take note. Firstly, check how the parameter runner_to_delete is being used. Next note how we now have a variable “@runner_id_to_delete.” So we are now able to take in the runner’s name as a parameter and get the runner_id as a variable. Lastly, you could see that we’ve grouped the three commands as a transaction and we COMMIT after the three commands are done.

Hope it’s clear that we are getting quite fancy, but let’s go a couple steps further with maturing this code.

Finally, let’s read through the code above. You could first notice that we have DECLARE EXIT HANDLER FOR SQLEXCEPTION ROLLBACK. As you could expect, this rolls back all the changes made if the procedure hits an error. There are a couple more nice touches to this code. The first is that the commands that actually make a changes to the tables are nested in an IF THEN statement. It only executes if the runner_id was found for the runner name that was given as a parameter. Additionally, if the runner_id was NULL, or not found, then it rolls back any changes made. In this case, there won’t be any changes as the only other command that was done before the IF THEN statement is a SELECT, but it’s still a nice touch to have code for each case and close out the TRANSACTION.

Alright! Now please try out some of the code covered above and feel free to experiment and change the code around. Try out different procedures and calling the procedures with different parameters.

There are a couple things to note when trying out the code.

The first is that once you delete records from tables while experimenting with code (obviously) you’ll have to either INSERT them again or ROLLBACK the changes. Second is that once we create a procedure, you cannot create the same procedure again. So you have to remove (DROP) the procedure if you would like to create a procedure with the same name again.

DROP PROCEDURE delete_account;

If you would like to edit an existing procedure, you could also use the MySQL Workbench feature, “Alter Stored Procedure.” You could go to the “Navigator” pane, double click on “Stored Procedures”, the select “Alter Stored Procedure.”

This will bring up the editor to directly alter the procedure. Once you’ve made your edits, you could either chose “Apply” or “Revert.”

There are two videos for this topic, here and then here.