SQL - Foreign Key

SQL Foreign Key

What is a SQL Foreign Key?

A Foreign Key is a constraint that creates a link between two tables. It is a column (or a set of columns) in one table that refers to the Primary Key of another table. This link is used to enforce referential integrity, which means it prevents actions that would leave ‘orphan’ records with invalid links.

The table containing the foreign key is called the child table or referencing table. The table containing the primary key it references is called the parent table or referenced table.

Conceptual Example

Consider two tables: Customers and Orders. Each order must belong to a customer. The Orders table would have a customer_id column that is a foreign key referencing the customer_id primary key in the Customers table. This ensures two things:

• You cannot create an order for a customer_id that does not exist in the Customers table.
• You cannot (by default) delete a customer who still has orders in the Orders table.

Creating a Foreign Key

Let’s first create our parent table, Customers.

CREATE TABLE Customers (
    customer_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

During Table Creation (Recommended)

The best practice is to define foreign keys when you create the child table. It’s crucial to name your constraints for easier management.

CREATE TABLE Orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    customer_id INT NOT NULL,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY(customer_id)
        REFERENCES Customers(customer_id)
);

On an Existing Table

You can also add a foreign key constraint to an existing table using ALTER TABLE. This will only succeed if all existing values in the foreign key column already have a matching value in the parent table’s primary key.

ALTER TABLE Orders
ADD CONSTRAINT fk_orders_customers
    FOREIGN KEY(customer_id)
    REFERENCES Customers(customer_id);

Referential Actions: ON DELETE and ON UPDATE

Foreign keys can be configured to automatically handle what happens to the child rows when a referenced parent row is deleted or updated. These are powerful but must be used with care.

• NO ACTION / RESTRICT (Default): Prevents the deletion or update of the parent row if any child rows exist. The operation will fail with an error.
• CASCADE: If the parent row is deleted, all corresponding child rows are also automatically deleted. If the parent key is updated, the child key is updated to match.
• SET NULL: If the parent row is deleted or the key is updated, the foreign key column(s) in the child rows are set to NULL. This requires the foreign key column to be nullable.
• SET DEFAULT: Similar to SET NULL, but sets the foreign key column(s) to their default value.

Example: ON DELETE CASCADE

Here, if a customer is deleted, all their orders are automatically deleted as well.

CREATE TABLE Orders (
    -- ... other columns
    customer_id INT NOT NULL,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY(customer_id) REFERENCES Customers(customer_id)
        ON DELETE CASCADE
);

Dropping a Foreign Key

To remove a foreign key constraint, you use ALTER TABLE and specify the constraint’s name. This highlights why naming your constraints is a critical best practice.

ALTER TABLE Orders
DROP CONSTRAINT fk_orders_customers;

Primary Key vs. Foreign Key

Aspect	Primary Key	Foreign Key
Purpose	Uniquely identifies a record within its own table.	Links a record to a primary key in another table.
Uniqueness	Must be unique for every row.	Can have duplicate values (e.g., one customer can have many orders).
NULL Values	Cannot be NULL.	Can be NULL, unless a NOT NULL constraint is also applied.
Count per Table	Only one per table.	A table can have multiple foreign keys, linking to different parent tables.

Common Errors and Best Practices

• Error: ‘foreign key constraint fails’. This occurs when you try to insert a value into the foreign key column that doesn’t exist in the parent table’s primary key.
• Error: ‘Cannot delete or update a parent row’. This happens (by default) when you try to delete a parent row that is still referenced by child rows.
• Best Practice: Always name your constraints explicitly.
• Best Practice: Ensure the data types of the foreign key and the primary key it references are identical.
• Best Practice: Carefully choose your ON DELETE / ON UPDATE action. CASCADE is powerful but can lead to unintentional mass deletions if not handled carefully.