How to Create MySQL Table Relationships with Foreign Keys

One of the important concepts in relational database design is the ability to connect one table to another. In MySQL, these relationships between tables are created using Foreign Keys. With a Foreign Key, you can ensure data integrity and prevent orphan data (data that has no parent). This article covers how to create table relationships in MySQL with Foreign Keys in full.

What Is a Foreign Key?

A Foreign Key is a column or set of columns in one table that references the Primary Key of another table. A Foreign Key acts as a bridge between two tables and ensures that any value inserted into that column must already exist in the referenced table. This is called Referential Integrity.

As a simple example: the orders table has a customer_id column that references the customers table. With a Foreign Key, MySQL will automatically reject any attempt to insert an order for a customer that is not registered.

Requirements for Using Foreign Keys in MySQL

• Both tables must use the InnoDB storage engine.
• The Foreign Key column and the referenced column must have the same data type.
• The referenced column must have an index (usually the Primary Key).

Creating a Table with a Foreign Key

Here is an example of creating two related tables: the customers table as the parent table and the orders table as the child table.

-- Create the parent table
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- Create the child table with a Foreign Key
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    total_price DECIMAL(10, 2) NOT NULL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
) ENGINE=InnoDB;

In the query above, CONSTRAINT fk_orders_customers is the name of the Foreign Key constraint. Naming it makes it easier when you want to drop or modify the constraint later.

ON DELETE and ON UPDATE Options

MySQL provides several action options that take effect when parent data is modified or deleted:

• CASCADE: Changes/deletions in the parent table are automatically applied to the child table.
• SET NULL: The Foreign Key column in the child table is set to NULL if the parent data is deleted.
• RESTRICT: Prevents deletion/modification in the parent table if related data still exists in the child table.
• NO ACTION: Same as RESTRICT in MySQL.
• SET DEFAULT: Sets the column to its default value (rarely used in MySQL).

Adding a Foreign Key to an Existing Table

If the table already exists, you can add a Foreign Key using the ALTER TABLE command.

-- Add a Foreign Key to an existing table
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE RESTRICT
ON UPDATE CASCADE;

Viewing the List of Foreign Keys

To view all the Foreign Keys in a database, use the following query:

-- View all Foreign Keys in the active database
SELECT
    TABLE_NAME,
    CONSTRAINT_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM
    INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
    REFERENCED_TABLE_SCHEMA = DATABASE()
    AND REFERENCED_TABLE_NAME IS NOT NULL
ORDER BY TABLE_NAME;

Dropping a Foreign Key

To drop a Foreign Key, use the ALTER TABLE ... DROP FOREIGN KEY command along with the constraint name.

-- Drop a Foreign Key by constraint name
ALTER TABLE orders
DROP FOREIGN KEY fk_orders_customers;

Data Usage Example

Let's try inserting data and see how the Foreign Key works:

-- Insert customer data first
INSERT INTO customers (name, email) VALUES
('Andi Saputra', 'andi@email.com'),
('Budi Santoso', 'budi@email.com');

-- Insert orders for existing customers
INSERT INTO orders (customer_id, product_name, quantity, total_price) VALUES
(1, 'ASUS Laptop', 1, 8500000.00),
(2, 'Wireless Mouse', 2, 300000.00);

-- This will FAIL because customer_id 99 does not exist
-- INSERT INTO orders (customer_id, product_name, quantity, total_price)
-- VALUES (99, 'Keyboard', 1, 250000.00);

Temporarily Disabling Foreign Key Checks

There are times when you need to disable Foreign Key checks temporarily, for example during a bulk data import. Use the following commands carefully:

-- Disable Foreign Key checks
SET FOREIGN_KEY_CHECKS = 0;

-- Perform your data operations here...

-- Re-enable them
SET FOREIGN_KEY_CHECKS = 1;

Conclusion

Foreign Keys are an essential feature in MySQL for maintaining data integrity between tables. By understanding how to create and manage them and how to use the ON DELETE and ON UPDATE options, you can design a more solid and reliable database. Always use the InnoDB storage engine and make sure the referenced column is indexed so that the Foreign Key works properly. Start applying Foreign Keys in your projects to prevent inconsistent data from the very beginning.

See also: A MySQL Learning Guide for Beginners: From Basics to Advanced — a complete learning roadmap covering everything from table design and JOINs to indexing and database backups.