From the course: Learning phpMyAdmin

Understanding foreign key constraints

From the course: Learning phpMyAdmin

Start my 1-month free trial

Understanding foreign key constraints

“

- In a relational database like MySQL, foreign keys are used to link records in different tables. Foreign key constraints control what happens when records are inserted, updated, or deleted. In the database we've been using in this course, the primary key in the makes table is used as a foreign key in the cars table. After importing the SQL file from the exercise files, the first and seventh rows of the cars table became associated with make id 5 or Chrysler. If you delete either of these rows, for example when the car is sold, it has no effect on the makes table. Make id 5 is still associated with Chrysler. But if you delete make id 5 from the makes table, all cars that use that as the foreign key become orphaned. You can no longer search them by make because there's no way of knowing what make id 5 stands for. Foreign key constraints avoid this problem by detecting related records in another table and taking preventative action such as canceling the operation to prevent records from becoming orphaned. By doing so foreign key constraints maintain referential integrity. In other words, they prevent inconsistencies between related tables. Using foreign keys establishes a parent-child relationship between tables. The parent is the table that contains a primary key, which is used as a foreign key in the child table. So in the example database, makes is the parent table, and cars is the child. You always set up the foreign key constraints in the child table. In our example, that means the cars table. To establish foreign key constraints, both columns must be indexed. In the makes table, make id is indexed as the primary key. In the cars table, make id has an ordinary index. In MySQL, both tables must use the InnoDB storage engine. This has been the default since MySQL 5.5 In MariaDB, the storage engine can either be XtraDB or InnoDB. ExtraDB is an enhanced fork of InnoDB. And at the time of recording this video, XtraDB was aliased as InnoDB. Although MyISAM tables support foreign keys, the MyISAM storage engine doesn't support foreign key constraints. When setting up foreign key constraints, you need to specify what happens when you update or delete a record in the parent table that has dependent records in the child table. RESTRICT rejects the update or delete operation. CASCADE updates or deletes the row in the parent table and automatically updates or deletes the matching rows in the child table. SET NULL updates or deletes the row in the parent table and sets the foreign key in matching rows of the child table to NULL. For this to work, the foreign key column in the child table must accept NULL values. Technically speaking, there's a fourth option: NO ACTION. In some database systems, this allows the foreign key checks to be delayed. In MySQL, checks are performed immediately, so it has the same effect as RESTRICT. In addition, foreign key constraints reject any insert or update operation that attempts to use a foreign key a value that doesn't exist in the parent table. For example, the highest value of make id in the makes table is currently 17. A foreign key constraint would stop you from inserting 18 as a value in the make id column of the cars table without first adding a new record to the parent table. phpMyAdmin makes it easy to set up foreign key constraints as you'll see in the rest of this chapter. If your web service supports InnoDB, you should definitely use foreign key constraints to preserve the integrity of your data.

Contents