In FOREIGN KEYS in MySQL with examples. I discussed establishing referential integrity between multiple tables sharing related and associated data using a FOREIGN KEY. However, the included syntax examples were not completely covered. In particular, the actual definition of the referential action. In this blog post I will continue with this concept and cover those definitions, their meanings, and example use…
Photo by Fancycrave on Unsplash
Note: All data, names or naming found within the database presented in this post, are strictly used for practice, learning, instruction, and testing purposes. It by no means depicts actual data belonging to or being used by any party or organization.
OS and DB used:
- Xubuntu Linux 16.04.5 LTS (Xenial Xerus)
- MySQL 5.7.23
Let’s revisit this ALTER TABLE command used to implement the FOREIGN KEY into the target book table:
1 | mysql> ALTER TABLE book ADD CONSTRAINT FOREIGN KEY fk_bk_genre (genre_id) REFERENCES book_genre(genre_id) ON UPDATE CASCADE ON DELETE NO ACTION; |
The optional ON UPDATE and ON DELETE commands and their sub-clauses specify what, if any, referential action takes place between the parent-child relationship columns.
The available choices, applicable for both commands, are listed below. A brief description is also included for each. But, be sure and visit the ‘Referential Actions’ section in the documentation for exact verbiage:
CASCADE– Changes placed upon the parent table column are automatically reflected (and changed) on the child table column(s).SET NULL– Child table column(s) are set toNULLfor bothUPDATEorDELETEoperations. Be sure the child table column(s) do not have theNOT NULLcolumn constraint attribute.RESTRICT– Rejects theDELETEorUPDATEon any child table column(s). If no referential action clause is specified in theFOREIGN KEYdefinition, this action is the default.NO ACTION– In MySQL means the same asRESTRICT. This wording comes from Standard SQL.SET DEFAULT– This syntax is recognized by MySQL, yet it is not parsed.
(To reiterate, if no referential action is defined, the default is always RESTRICT.)
Let’s look at an example for the ON UPDATE CASCADE action where we can visibly note the automatic changes cascaded to the child table column(s). As previously mentioned, with CASCADE, any changes made on the parent table column are automatically carried out on the corresponding child table column. (Both UPDATE and DELETE are supported in this behavior).
Here is the current data in both target tables:
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 | mysql> SELECT * FROM book; +-------+------------------+----------+ | bk_id | title | genre_id | +-------+------------------+----------+ | 1 | No Upper Limit | 202 | | 2 | The Lost Helm | 101 | | 3 | Golden Band | 303 | | 4 | 10th Grade Blues | 404 | | 5 | God Captain | 101 | | 6 | Blunt Spurs | 505 | | 9 | loose noose | 505 | +-------+------------------+----------+ 7 rows in set (0.00 sec) mysql> SELECT * FROM book_genre; +----------+-------------+ | genre_id | genre_type | +----------+-------------+ | 101 | Fantasy | | 202 | Action | | 303 | Romance | | 404 | Young Adult | | 505 | Western | +----------+-------------+ 5 rows in set (0.00 sec) |
I’ll UPDATE the ‘Fantasy’ genre_id and then review the changes in both tables.
1 2 3 4 5 | mysql> UPDATE book_genre -> SET genre_id = 102 -> WHERE genre_type = 'Fantasy'; Query OK, 1 row affected (0.18 sec) Rows matched: 1 Changed: 1 Warnings: 0 |
Rechecking the data values, we can see the change made by the previous UPDATE command on table book_genre, have also been updated in table book:
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 | mysql> SELECT * FROM book; +-------+------------------+----------+ | bk_id | title | genre_id | +-------+------------------+----------+ | 1 | No Upper Limit | 202 | | 2 | The Lost Helm | 102 | --Now is 102 where before was 101 | 3 | Golden Band | 303 | | 4 | 10th Grade Blues | 404 | | 5 | God Captain | 102 | --Now is 102 where before was 101 | 6 | Blunt Spurs | 505 | | 9 | loose noose | 505 | +-------+------------------+----------+ 7 rows in set (0.00 sec) mysql> SELECT * FROM book_genre; +----------+-------------+ | genre_id | genre_type | +----------+-------------+ | 102 | Fantasy | | 202 | Action | | 303 | Romance | | 404 | Young Adult | | 505 | Western | +----------+-------------+ 5 rows in set (0.00 sec) |
I think it is extremely important to understand that CASCADE will make any changes to the corresponding child table rows. (Including any DELETE‘s, which is a sub-clause option: E.g., ON DELETE CASCADE).
Speaking of DELETE, I’ll visit the ON DELETE NO ACTION referential action to gain understanding on that sub-clause specification.
With the below DELETE command, I will attempt to remove the 'Western' genre_type.
1 2 3 | mysql> DELETE FROM book_genre -> WHERE genre_id = 505; ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`practice`.`book`, CONSTRAINT `book_ibfk_1` FOREIGN KEY (`genre_id`) REFERENCES `book_genre` (`genre_id`) ON DELETE NO ACTION ON UPDATE CASCADE) |
You can see the DELETE command was not successful because of the NO ACTION sub-clause definition.
Potentially, there may come a time when you do not want ‘changes’ to CASCADE to the child table column(s). Also, in the event that just the thought of a DELETE makes you cringe, there is another configuration to explore. Setting the child row column(s) to NULL.
I originally implemented the NOT NULL Constraint on the table columns used in this blog post. So, in order to demonstrate that functionality, I need to perform some minor ‘housekeeping and cleanup’.
First, DROP the FOREIGN KEY constraint so I can MODIFY the necessary column:
1 2 3 | mysql> ALTER TABLE book DROP FOREIGN KEY book_ibfk_1; Query OK, 0 rows affected (0.19 sec) Records: 0 Duplicates: 0 Warnings: 0 |
Then MODIFY the genre_id column so NULL values are accepted:
1 2 3 | mysql> ALTER TABLE book MODIFY COLUMN genre_id INT(11); Query OK, 0 rows affected (0.89 sec) Records: 0 Duplicates: 0 Warnings: 0 |
I’ll review the book table description and see the reflected changes, where column genre_id now allows NULL values:
1 2 3 4 5 6 7 8 9 10 11 | mysql> SHOW CREATE TABLE book\G *************************** 1. row *************************** Table: book Create Table: CREATE TABLE `book` ( `bk_id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL, `genre_id` int(11) DEFAULT NULL, PRIMARY KEY (`bk_id`), KEY `fk_bk_genre` (`genre_id`) ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 1 row in set (0.00 sec) |
To Finish up, re-define the FOREIGN KEY using SET NULL in the referential action definition for the ON DELETE clause:
1 2 3 | mysql> ALTER TABLE book ADD CONSTRAINT FOREIGN KEY fk_bk_genre (genre_id) REFERENCES book_genre(genre_id) ON UPDATE CASCADE ON DELETE SET NULL; Query OK, 7 rows affected (0.81 sec) Records: 7 Duplicates: 0 Warnings: 0 |
Let’s issue a DELETE command and see how this works on the child table column(s):
1 2 3 | mysql> DELETE FROM book_genre -> WHERE genre_id = 505; Query OK, 1 row affected (0.05 sec) |
And the data in table book:
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> SELECT * FROM book; +-------+------------------+----------+ | bk_id | title | genre_id | +-------+------------------+----------+ | 1 | No Upper Limit | 202 | | 2 | The Lost Helm | 102 | | 3 | Golden Band | 303 | | 4 | 10th Grade Blues | 404 | | 5 | God Captain | 102 | | 6 | Blunt Spurs | NULL | | 9 | loose noose | NULL | +-------+------------------+----------+ 7 rows in set (0.00 sec) |
You can see the reflected changes above. Wherever the genre_id previously had a value of 505, has been changed and set to NULL.
Referential actions are powerful. They have the potential to either make or ruin your day, dependent upon configuration. I encourage you to explore them and learn their behavior.
Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!
Explore the official MySQL 5.7 Online Manual for more information.
A Call To Action!
Thank you for taking the time to read this post. I truly hope you discovered something interesting and enlightening. Please share your findings here, with someone else you know who would get the same value out of it as well.
Visit the Portfolio-Projects page to see blog post/technical writing I have completed for clients.
Have I mentioned how much I love a cup of coffee?!?!
To receive email notifications (Never Spam) from this blog (“Digital Owl’s Prose”) for the latest blog posts as they are published, please subscribe (of your own volition) by clicking the ‘Click To Subscribe!’ button in the sidebar on the homepage! (Feel free at any time to review the Digital Owl’s Prose Privacy Policy Page for any questions you may have about: email updates, opt-in, opt-out, contact forms, etc…)
Be sure and visit the “Best Of” page for a collection of my best blog posts.
Josh Otwell has a passion to study and grow as a SQL Developer and blogger. Other favorite activities find him with his nose buried in a good book, article, or the Linux command line. Among those, he shares a love of tabletop RPG games, reading fantasy novels, and spending time with his wife and two daughters.
Disclaimer: The examples presented in this post are hypothetical ideas of how to achieve similar types of results. They are not the utmost best solution(s). The majority, if not all, of the examples provided, are performed on a personal development/learning workstation-environment and should not be considered production quality or ready. Your particular goals and needs may vary. Use those practices that best benefit your needs and goals. Opinions are my own.
2 thoughts on “FOREIGN KEY Referential Actions in MySQL – with examples.”