How do foreign keys work?

Foreign keys are a fundamental concept in relational databases that establish a link between two tables. Here’s how they work:

Definition

A foreign key is a column (or a set of columns) in one table that refers to the primary key in another table. This relationship helps maintain referential integrity between the two tables.

Purpose

Establish Relationships: Foreign keys create a connection between related data in different tables. For example, a category_id in an inventory_items table might reference the category_id in a categories table.
Data Integrity: They ensure that the value in the foreign key column matches a value in the referenced primary key column. This prevents orphaned records (records that reference non-existent entries).

Example

Consider two tables: categories and inventory_items.

categories Table:

CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    category_name VARCHAR(100)
);

inventory_items Table:

CREATE TABLE inventory_items (
    item_id INT PRIMARY KEY,
    item_name VARCHAR(100),
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

How It Works

When you insert a record into inventory_items, the category_id must match an existing category_id in the categories table.
If you try to insert a record with a category_id that doesn’t exist in categories, the database will reject the operation, maintaining data integrity.

Cascading Actions

Foreign keys can also have cascading actions, such as:

ON DELETE CASCADE: If a referenced record in the parent table is deleted, all related records in the child table are also deleted.
ON UPDATE CASCADE: If the primary key value in the parent table is updated, the corresponding foreign key values in the child table are updated automatically.

In summary, foreign keys are crucial for linking tables and ensuring that relationships between data are maintained correctly. If you have further questions or need more examples, feel free to ask!