Referential integrity

Given our three messageboard tables from before, consider this situation: a member, number 29391, posted a message to a messageboard. They have a row to themselves in the members table, and also a row in the messages table for their message. What happens if the member's account is deleted - does their message get deleted also? This is referential integrity: the messages table points to a row in the members table, but does that row exist?

When referential integrity is automatically enforced, the DBMS automatically ensures that the system is never left in an inconsistent stage, i.e. the messages table will never refer to a member that has been deleted.

If you use a foreign key in a table without actually declaring it as a foreign key, referential integrity cannot be enforced - the DBMS needs to be told where the key is referenced so that it can keep the system consistent properly.

 

Want to learn PHP 7?

Hacking with PHP has been fully updated for PHP 7, and is now available as a downloadable PDF. Get over 1200 pages of hands-on PHP learning today!

If this was helpful, please take a moment to tell others about Hacking with PHP by tweeting about it!

Next chapter: Indexes >>

Previous chapter: Keys

Jump to:

 

Home: Table of Contents

Copyright ©2015 Paul Hudson. Follow me: @twostraws.