Oftentimes, we use multiple tables in a database to meet normalization best practices. Among the normal forms, we also must ensure some type of integrity for related data spread out between multiple tables. A FOREIGN KEY, along with specific constraints, can be used for this…
Photo by Katy Belcher 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
In this blog post, we will look at using FOREIGN KEY‘s to ensure sound data practices and referential integrity amongst tables housing associated data . I’ll simulate this relationship using a mock ‘book’ and accompanying ‘genre’ table.
Let’s see a detailed description and table composition for the target tables using SHOW CREATE TABLE syntax queries:
1 2 3 4 5 6 7 8 9 10 | 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) NOT NULL, PRIMARY KEY (`bk_id`) ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 1 row in set (0.00 sec) |
1 2 3 4 5 6 7 8 | mysql> SHOW CREATE TABLE book_genre\G *************************** 1. row *************************** Table: book_genre Create Table: CREATE TABLE `book_genre` ( `genre_id` int(11) NOT NULL, `genre_type` varchar(35) COLLATE utf8mb4_unicode_ci DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 1 row in set (0.00 sec) |
(Are you familiar with the SHOW command? Have a look at Example uses of MySQL SHOW syntax. I wrote on uses of this handy command.)
I’ll run these 2 exploratory queries to get a sense of the present data:
1 2 3 4 5 6 7 8 9 10 11 | 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 | +-------+------------------+----------+ 5 rows in set (0.00 sec) |
1 2 3 4 5 6 7 8 9 10 | mysql> SELECT * FROM book_genre; +----------+-------------+ | genre_id | genre_type | +----------+-------------+ | 101 | Fantasy | | 202 | Action | | 303 | Romance | | 404 | Young Adult | +----------+-------------+ 4 rows in set (0.00 sec) |
Suppose we make this INSERT on table book:
1 2 3 | mysql> INSERT INTO book(title, genre_id) -> VALUES ('Blunt Spurs', 505); Query OK, 1 row affected (0.02 sec) |
While no error occurred, and everything appears okay from the outside, there is an underlying issue. The book_genre table has no genre_id value of 505. This is not ideal and poses challenges in keeping the data consistent and maintaining integrity across multiple tables. One way to prevent introducing this type of inconsistency in our data, is to use a FOREIGN KEY.
Adding or including a FOREIGN KEY on a table column in MySQL is relatively straight-forward. However, there are a few things to keep in mind you will see as I move forward. First thing to note, the FOREIGN KEY must be in the child table. For this example, that is table book.
Since the book table already exists, I’ll use the ALTER TABLE command to add the FOREIGN KEY:
1 2 | 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; ERROR 1215 (HY000): Cannot add foreign key constraint |
Seeking Additional and Helpful Information From the Community
Admittedly, I did not understand the error message when running this command and consulted the documentation for clues/meaning. I concluded that the reason for it was due to the referenced column in the parent table, not being unique. As the blog post progresses, I fix this by making the genre_id (referenced) column in table genre (parent table) the PRIMARY KEY. Which does make that column unique. Also, in the documentation (linked below) this passage is of interest:
Foreign key relationships involve a parent table that holds the central data values, and a child table with identical values pointing back to its parent.
In addition, the documentation mentions that when adding in a FOREIGN KEY to an existing table with ALTER TABLE, be sure and create the necessary indexes first.
Any corrections, information, insight, or additional resources provided by readers and the community are greatly appreciated.
Supporting sources:
- From the official MySQL Documentation: 8.3.2 Primary Key Optimization
- From the official MySQL Documentation: 13.1.18.6 Using FOREIGN KEY Constraints
As I mentioned above, in this MySQL statement I make the genre_id the PRIMARY KEY for table book_genre:
1 2 3 | mysql> ALTER TABLE book_genre ADD CONSTRAINT PRIMARY KEY pk_genre_id (genre_id); Query OK, 0 rows affected (0.65 sec) Records: 0 Duplicates: 0 Warnings: 0 |
Let’s quickly review that table description again now to see the PRIMARY key is in place:
1 2 3 4 5 6 7 8 9 | mysql> SHOW CREATE TABLE book_genre\G *************************** 1. row *************************** Table: book_genre Create Table: CREATE TABLE `book_genre` ( `genre_id` int(11) NOT NULL, `genre_type` varchar(35) COLLATE utf8mb4_unicode_ci DEFAULT NULL, PRIMARY KEY (`genre_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci 1 row in set (0.02 sec) |
Now to add the FOREIGN KEY to table book:
1 2 | 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; ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`practice`.`#sql-613_a0`, CONSTRAINT `#sql-613_a0_ibfk_1` FOREIGN KEY (`genre_id`) REFERENCES `book_genre` (`genre_id`) ON DELETE NO ACTION ON UPDATE CASCADE) |
Prior to covering what the above error means, let’s revisit the data in table book, for therein lies the issue:
1 2 3 4 5 6 7 8 9 10 11 12 | 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 | +-------+------------------+----------+ 6 rows in set (0.00 sec) |
Recall, there is no genre_id column value of 505 in table book_genre. Lo and behold, the problem has been discovered. (See quoted passage from above via the documentation concerning identical data between the parent and child tables).
To remedy the error, I’ll INSERT that 505 value with a corresponding genre_type:
1 2 3 | mysql> INSERT INTO book_genre(genre_id, genre_type) -> VALUES (505, 'Western'); Query OK, 1 row affected (0.04 sec) |
Finally, we can create the FOREIGN KEY in table book:
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 NO ACTION; Query OK, 6 rows affected (0.68 sec) Records: 6 Duplicates: 0 Warnings: 0 |
With the relationship established between the PRIMARY KEY of table book_genre and the FOREIGN KEY in table book, we now have a means to ensure referential integrity along with a solid column to use for linking matching rows using JOIN‘s.
Here is an example query, returning those books in the 'Fantasy' genre:
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> SELECT b.title, bk.genre_type -> FROM book AS b -> INNER JOIN -> book_genre AS bk -> USING(genre_id) -> WHERE bk.genre_type = 'Fantasy'; +---------------+------------+ | title | genre_type | +---------------+------------+ | The Lost Helm | Fantasy | | God Captain | Fantasy | +---------------+------------+ 2 rows in set (0.04 sec) |
In addition to a candidate column for joining, the FOREIGN KEY relationship prohibits those INSERT‘s with no matching value in the parent table. As in this example:
1 2 3 | mysql> INSERT INTO book(title, genre_id) -> VALUES('Broken Scope', 797); ERROR 1452 (23000): Cannot add or update a child 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) |
The above INSERT failed since the supplied genre_id of 797 is non-existent in table book_genre.
It should be noted that a FOREIGN KEY can be established in the initial CREATE TABLE command as well.
Additional Reading and Resources
- From the official documentation: 13.1.18.6 Using FOREIGN KEY Constraints
- SQL FOREIGN KEY Constraint
- MySQL Foreign Key
My hope is that this blog post has provided you with enough information and more importantly, curiosity, to explore more on FOREIGN KEY‘s. However, we are not yet finished. In a forthcoming blog post, I will cover the Referential Actions (e.g., ON UPDATE CASCADE) specified in the examples provided in this blog post. Be sure and visit that post when it is published. I look forward to any comments you have below and many 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.
Thanks! I finally get it.
Hey thank you for the comment and I am very pleased you found something here that helped you out. Thanks!