Using the REFERENTIAL_CONSTRAINTS table in MySQL — Foreign Key Awareness

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:

Suppose I want to remove the ‘staff’ table. Using the DROP command, it is fairly simple:

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:

Even better, how valuable would it be to know of all the FOREIGN KEY constraints in place for the entire database schema?