Databases are the last layer of defense against corrupt data and the more restrictive you can make them the better. No matter how much validation you may have missed in your code having a strong and restrictive database schema will protect your data. One of the best approaches to building a restrictive schema is using foreign keys which specify how fields from one table relate to the fields of another table. There are a few options here and make it possible for you to specify anything from automatically removing rows when a row they’re referencing is removed to recursively updating rows when their references have changed.
The MySQL docs give a nice overview of how foreign keys work but they’re light on examples and since I tend to learn best from examples I wanted to share them along with a brief description. Hopefully others find these examples useful as well. Each of the examples creates two tables, test_parent and test_child, with test_child having a different foreign key option on a field referencing the test_parent. I also insert the same data into each one to start and then do a few follow up queries describing what happens in each scenario. Also note that there is both an “ON DELETE” and an “ON UPDATE” option which, as expected, controls the respective behaviors.
Note that in this case we can’t even make parent_id NOT NULL in the test_child table create - MySQL rejects that statement.
No example here since in MySQL this works exactly the same as the RESTRICT option above.
This is not a valid option in MySQL using the INNODB engine.