What is a foreign key?#

A foreign key is a bridge between two tables: a column whose value must equal the primary key of a row in another table. For example, in the orders table the customer_id column is a foreign key and points to the id column in the customers table.

What does it do?#

  • It shows which customer each order belongs to.
  • It stops an order from being saved for a customer who does not exist.
  • When a customer is deleted, it decides what happens to their orders.

Delete rules#

When defining it, you can specify the delete behavior:

  • Restrict: the customer cannot be deleted while they still have orders.
  • Cascade: when the customer is deleted, their orders are deleted too.
  • Set null: the referencing column is emptied.

Example#

If you delete customer number 7 but their orders remain, your reports fill up with "orphan" orders. A foreign key prevents exactly this kind of damage.

Practical tip#

Some developers skip foreign keys for speed or flexibility and check data integrity in application code. That is possible, but the risk of errors rises. For important data, a foreign key in the database itself is the simplest insurance.