Foreign key

Content sourced from Wikipedia, licensed under CC BY-SA 3.0.

A foreign key is a column or set of columns in one table that links to the primary key (or another candidate key) in another table. It creates a relationship between two tables and helps ensure data stays connected and accurate.

How it works
- The values in the foreign key must match a value in the parent table’s key (or be NULL, if allowed). This is called referential integrity.
- The table with the foreign key is the child table; the table whose key is referenced is the parent table.
- A foreign key usually points to a single, unique key in the parent table (often the primary key).

Common concepts
- Parent table: the table whose key is referenced.
- Child table: the table that contains the foreign key.
- Candidate key: a set of columns that could uniquely identify a row; the primary key is an example of a candidate key.
- One-to-many: most foreign keys create a one-to-many relationship (one row in the parent can relate to many rows in the child).
- Self-referencing: a table can have a foreign key that references its own key (e.g., an employee table where each row may reference a manager who is also in the same table).

What happens when data changes
- ON DELETE / ON UPDATE rules control what happens to child rows if the parent row is deleted or changed. Common options:
- CASCADE: delete or update the related child rows automatically.
- RESTRICT: prevent the delete or update if there are related child rows.
- NO ACTION: check for violations after attempting the change; may fail if references exist.
- SET NULL: set the foreign key values in the child to NULL.
- SET DEFAULT: set the foreign key values in the child to a default value.
- These rules help avoid leaving orphaned rows that reference non-existent data.

Simple examples
- Customers and Orders:
- Customer(ID) is the primary key.
- Order has CustomerID as a foreign key referencing Customer.ID.
- Each order must reference a real customer, or be NULL if allowed. If a customer is deleted, you choose a rule (e.g., cascade delete or restrict).

- Suppliers and Invoices:
- Supplier(SupplierID) is the primary key.
- Invoice has SupplierID as a foreign key referencing Supplier.SupplierID.
- Invoices must link to an existing supplier; changes to suppliers propagate to related invoices according to the chosen rule.

Why foreign keys matter
- They reflect real-world relationships in the database.
- They help prevent inconsistent data, such as an order referencing a non-existent customer.
- They enable safer updates and deletions through defined rules and cascading effects.

In short, foreign keys connect two tables, enforce valid references, and help maintain clean, interconnected data across the database.