What is a foreign key?

A foreign key is a field (or set of fields) in one database table that references the primary key of another table. It establishes a link between the tables, ensuring data integrity.

Main purposes of a foreign key:

• Referential integrity: Ensures that the values in the foreign key match existing values in the related table. For example, you cannot create an order record with a non-existent customer.
• Creating relationships between tables: Allows linking data from different tables.

Example:

Table users with primary key id. Table orders with foreign key user_id, which references users.id.

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(255)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

In this example, the foreign key user_id in the orders table ensures that each order is linked to a valid user from the users table.

Actions on delete/update of the related record in the parent table can be configured:

• CASCADE: Automatically deletes/updates dependent records.
• SET NULL: Sets the foreign key value to NULL in dependent records.
• RESTRICT (default): Prevents deletion/update of the parent record if dependent records exist.
• NO ACTION: Similar to RESTRICT, but can be deferred until the end of the transaction.