How to enforce the relationships between tables

Description

• Referential integrity means that the relationships between tables are maintained correctly. That means that a table with a foreign key doesn’t have rows with foreign key values that don’t have matching primary key values in the related table.
• If referential integrity isn’t enforced and a row is deleted from the primary key table that has related rows in the foreign key table, the rows in the foreign key table are said to be orphaned.
• Prior to version 5.5, MySQL used MyISAM tables by default. These tables do not support enforcing referential integrity. As a result, when using MyISAM tables, the PHP code of the web application typically enforces referential integrity.
• With 5.5 and later, MySQL uses InnoDB tables by default. InnoDB tables provide for declarative referential integrity (DRI). To use declarative referential integrity, you define foreign key constraints.
• When you define foreign key constraints, you can specify how referential integrity is enforced when a row is deleted from the primary key table. The options are to return an error or to delete the related rows in the foreign key table.
• The advantage of DRI is that the database makes sure that referential integrity is enforced even if the application that’s using the database attempts to violate refer-ential integrity.