In a relational database such as MySQL, a foreign key is a column or a set of columns in a table that refers to the primary key of another table. It establishes a relationship between two tables, known as the parent (or referenced) table and the child (or referring) table.
For example, suppose we have two tables: Order and Customer. The Order table has a column named CustomerID, which is a foreign key that refers to the primary key column of the Customer table. This means that each order is associated with a single customer, and that customer is identified by their unique CustomerID in the Customer table.
The use of foreign keys helps maintain data integrity by ensuring that only valid data can be inserted into the child table. It does so in two ways:
- First, a foreign key constraint specifies that the value of the foreign key column in the child table must exist in the primary key column of the parent table. In other words, it ensures that a child table cannot reference a nonexistent parent table row. For example, if the Order table’s foreign key value references a CustomerID of 12345, then there must be a corresponding row in the Customer table with a CustomerID of 12345. If not, an error will occur when trying to insert or update data in the child table.
- Second, a foreign key constraint can also specify what happens when a row in the parent table is deleted or updated. This is known as the referential action. For example, if the parent row is deleted, the referential action specifies what to do with the associated child rows. MySQL supports several referential actions, such as CASCADE (delete or update the child rows), SET NULL (set the foreign key values in the child rows to NULL), and RESTRICT (prevent the deletion or update of the parent row).
Therefore, by using foreign keys, we can maintain the integrity of the data in the relational database, ensuring that the data is accurate, consistent and in accordance with the rules of the business logic.