MySQL FOREIGN KEY

A FOREIGN KEY is a column in one table that refers to the PRIMARY KEY of another table. It creates a link between two tables and ensures that the relationship between them remains valid. This concept is known as referential integrity.

Why FOREIGN KEY is Needed

Consider an online store with two tables: customers and orders. An order must belong to a real customer. A FOREIGN KEY on the orders table prevents an order from being created for a customer who does not exist.

Key Terminology

• Parent Table — The table that holds the primary key (e.g., customers)
• Child Table — The table that holds the foreign key (e.g., orders)
• Referenced Column — The primary key column in the parent table
• Referencing Column — The foreign key column in the child table

Syntax

CREATE TABLE child_table (
    column_name datatype,
    CONSTRAINT fk_name FOREIGN KEY (column_name)
    REFERENCES parent_table (parent_column)
);

Example: Customers and Orders

Parent Table: customers

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

Child Table: orders

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    CONSTRAINT fk_customer FOREIGN KEY (customer_id)
    REFERENCES customers (customer_id)
);

Valid Insert

INSERT INTO customers VALUES (1, 'Sunita Verma');
INSERT INTO orders VALUES (1001, '2024-05-10', 1);  -- customer 1 exists

Invalid Insert (Foreign Key Violation)

INSERT INTO orders VALUES (1002, '2024-05-11', 99);  -- customer 99 does not exist

MySQL returns:

ERROR 1452: Cannot add or update a child row: a foreign key constraint fails

ON DELETE and ON UPDATE Actions

When a record in the parent table is deleted or updated, MySQL can take specific actions on the child table. These are defined using ON DELETE and ON UPDATE clauses.

Example with CASCADE

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    order_date DATE,
    customer_id INT,
    CONSTRAINT fk_customer FOREIGN KEY (customer_id)
    REFERENCES customers (customer_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

Now, if customer 1 is deleted from customers, all their orders are automatically deleted from orders as well.

Adding FOREIGN KEY to an Existing Table

ALTER TABLE orders
ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id)
REFERENCES customers (customer_id);

Removing a FOREIGN KEY

ALTER TABLE orders
DROP FOREIGN KEY fk_customer;

Viewing FOREIGN KEYS

SELECT CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME,
       REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'your_database_name'
AND REFERENCED_TABLE_NAME IS NOT NULL;

Key Points

• A FOREIGN KEY links a column in one table to the primary key of another table.
• It enforces referential integrity — child records cannot reference non-existent parent records.
• ON DELETE CASCADE and ON UPDATE CASCADE automate changes across related tables.
• The parent table must be created before the child table when using foreign keys.
• Foreign keys help maintain consistent and reliable relationships between tables.