Using FOREIGN KEY‘s in database schema design assist in storing consistent, normalized, and sound data. Oftentimes, many tables wind up with many FOREIGN KEY constraints. However, keeping up with this (potential) maze of relationships doesn’t have to be a nightmare. Want to learn more? Keep reading…
OS and DB used:
- OpenSuse Leap 15.1
- MySQL 8.0.18
Self-Promotion:
If you enjoy the content written here, by all means, share this blog and your favorite post(s) with others who may benefit from or like it as well. Since coffee is my favorite drink, you can even buy me one if you would like!
For the examples in this post, I’m using the open-source Sakila database, which mimics a DVD rental store:
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 26 27 28 29 | mysql> SHOW TABLES; +----------------------------+ | Tables_in_sakila | +----------------------------+ | actor | | actor_info | | address | | category | | city | | country | | customer | | customer_list | | film | | film_actor | | film_category | | film_list | | film_text | | inventory | | language | | nicer_but_slower_film_list | | payment | | rental | | sales_by_film_category | | sales_by_store | | staff | | staff_list | | store | +----------------------------+ 23 rows in set (0.01 sec) |
Suppose I want to remove the ‘staff’ table. Using the DROP command, it is fairly simple:
1 2 | mysql> DROP TABLE staff; ERROR 3730 (HY000): Cannot drop table 'staff' referenced by a foreign key constraint 'fk_payment_staff' on table 'payment'. |
Oh…
Bummer…
The error is self-explanatory for those familiar with FOREIGN KEY constraints and the referential integrity they provide. If this is a foreign concept to you, read this post and another good one here for better understanding.
Wouldn’t it be nice to know of these constraints ahead of time? Of course, you can always do this:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | mysql> SHOW CREATE TABLE payment\G *************************** 1. row *************************** Table: payment Create Table: CREATE TABLE `payment` ( `payment_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT, `customer_id` smallint(5) unsigned NOT NULL, `staff_id` tinyint(3) unsigned NOT NULL, `rental_id` int(11) DEFAULT NULL, `amount` decimal(5,2) NOT NULL, `payment_date` datetime NOT NULL, `last_update` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`payment_id`), KEY `idx_fk_staff_id` (`staff_id`), KEY `idx_fk_customer_id` (`customer_id`), KEY `fk_payment_rental` (`rental_id`), CONSTRAINT `fk_payment_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT `fk_payment_rental` FOREIGN KEY (`rental_id`) REFERENCES `rental` (`rental_id`) ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT `fk_payment_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) |
Even better, how valuable would it be to know of all the FOREIGN KEY constraints in-place for the entire database schema?
Although SHOW CREATE TABLE output presents any present constraint definitions – among other information – did you know that MySQL provides a table where all of this information is stored and available at your fingertips?
The REFERENTIAL_CONSTRAINTS table located in the information_schema database is that such table.
To access it, simply move into the information_schema database:
1 | mysql> USE information_schema; |
Here is the description of the REFERENTIAL_CONSTRAINTS table:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | mysql> DESC REFERENTIAL_CONSTRAINTS; +---------------------------+-----------------------------------------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------------------+-----------------------------------------------------------------+------+-----+---------+-------+ | CONSTRAINT_CATALOG | varchar(64) | NO | | NULL | | | CONSTRAINT_SCHEMA | varchar(64) | NO | | NULL | | | CONSTRAINT_NAME | varchar(64) | YES | | NULL | | | UNIQUE_CONSTRAINT_CATALOG | varchar(64) | NO | | NULL | | | UNIQUE_CONSTRAINT_SCHEMA | varchar(64) | NO | | NULL | | | UNIQUE_CONSTRAINT_NAME | varchar(64) | YES | | NULL | | | MATCH_OPTION | enum('NONE','PARTIAL','FULL') | NO | | NULL | | | UPDATE_RULE | enum('NO ACTION','RESTRICT','CASCADE','SET NULL','SET DEFAULT') | NO | | NULL | | | DELETE_RULE | enum('NO ACTION','RESTRICT','CASCADE','SET NULL','SET DEFAULT') | NO | | NULL | | | TABLE_NAME | varchar(64) | NO | | NULL | | | REFERENCED_TABLE_NAME | varchar(64) | NO | | NULL | | +---------------------------+-----------------------------------------------------------------+------+-----+---------+-------+ 11 rows in set (0.00 sec) |
You can query and explore – provided your user has the proper permissions – REFERENTIAL_CONSTRAINTS like any other table. This next query returns the current FOREIGN KEY constraints for the entire sakila database:
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 26 27 28 29 30 | mysql> SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME -> FROM REFERENTIAL_CONSTRAINTS -> WHERE CONSTRAINT_SCHEMA = 'sakila'; +---------------------------+---------------+-----------------------+ | CONSTRAINT_NAME | TABLE_NAME | REFERENCED_TABLE_NAME | +---------------------------+---------------+-----------------------+ | fk_address_city | address | city | | fk_city_country | city | country | | fk_customer_address | customer | address | | fk_customer_store | customer | store | | fk_film_actor_actor | film_actor | actor | | fk_film_actor_film | film_actor | film | | fk_film_category_category | film_category | category | | fk_film_category_film | film_category | film | | fk_film_language | film | language | | fk_film_language_original | film | language | | fk_inventory_film | inventory | film | | fk_inventory_store | inventory | store | | fk_payment_customer | payment | customer | | fk_payment_rental | payment | rental | | fk_payment_staff | payment | staff | | fk_rental_customer | rental | customer | | fk_rental_inventory | rental | inventory | | fk_rental_staff | rental | staff | | fk_staff_address | staff | address | | fk_staff_store | staff | store | | fk_store_address | store | address | | fk_store_staff | store | staff | +---------------------------+---------------+-----------------------+ 22 rows in set (0.00 sec) |
The TABLE_NAME column is a fantastic column you can filter in the WHERE clause predicate, targeting a specific table. Here’s a query on the ‘payment’ table:
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> SELECT CONSTRAINT_NAME, TABLE_NAME, REFERENCED_TABLE_NAME -> FROM REFERENTIAL_CONSTRAINTS -> WHERE CONSTRAINT_SCHEMA = 'sakila' -> AND TABLE_NAME = 'payment'; +---------------------+------------+-----------------------+ | CONSTRAINT_NAME | TABLE_NAME | REFERENCED_TABLE_NAME | +---------------------+------------+-----------------------+ | fk_payment_customer | payment | customer | | fk_payment_rental | payment | rental | | fk_payment_staff | payment | staff | +---------------------+------------+-----------------------+ 3 rows in set (0.00 sec |
Conveniently, the REFERENCED_TABLE_NAME column provides the parent table name in the FOREIGN KEY relationship.
As the need arises and you want to know about all the current FOREIGN KEY relationships, query the REFERENTIAL_CONSTRAINTS table for an overview. Visit the online information_schema REFERENTIAL_CONSTRAINTS documentation for more information.
SQL CRUD Basics Series
Are you up to speed on SQL CRUD operations? Not sure just what they are? Don’t fret one bit. I have the perfect series of blog posts just for you. Visit the list below in order for information and simple examples of SQL CRUD…
- SQL CRUD Basics Part 1 – Create
- SQL CRUD Basics Part 2 – Read
- SQL CRUD Basics Part 3 – Update
- SQL CRUD Basics Part 4 – Delete
Thanks for reading!!!
Like what you have read? See anything incorrect? Please comment below and thanks for reading!!!
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, is 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 “Using the REFERENTIAL_CONSTRAINTS table in MySQL – Foreign Key Awareness”