How to add a foreign key constraint in MySQL?

What is a foreign key in MySQL?

A foreign key is a field in a table that matches with a field in another table. A foreign key places constraints on data in the related tables, which enables MySQL to maintain referential integrity.

How to add a foreign key constraint to a table in MySQL?

You can add a foreign key constraint to a table in MySQL during creating and altering the table. So, here is the syntax to add to the CREATE TABLE and ALTER TABLE statements to add a foreign key constraint:

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name, ...)
    REFERENCES table_name (col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

Example of adding foreign key constraint:

CREATE TABLE parent (
    id INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (
    id INT,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(id)
        ON DELETE CASCADE
) ENGINE=INNODB;

When you don’t need the foreign key constraint anymore, delete it using ALTER TABLE statement:

ALTER TABLE table_name 
DROP FOREIGN KEY constraint_name;

In TablePlus, you can add and alter the foreign key constraint via the GUI.

From the table view, click on the structure tab at the bottom or use shortcut key Cmd + Shift + ] to switch to the structure editor. From here, it will list out all the columns of the table and you can choose one or more columns to add to the foreign key for the table.

To add a foreign key constraint:

Select the column you want to add
Click on the arrow icon at the foreign key section on the row.
Choose “Create a foreign key on column_name””

Add foreign key constraint in MySQL

It also lists the other tables available on the database so that the user can choose a referenced table and referenced column or columns, with other options ON UPDATE and ON DELETE.

After you finish, press Cmd + S to commit changes, and it’s done.

Need a good GUI Tool for MySQL? TablePlus is a modern, native tool with an elegant UI that allows you to simultaneously manage multiple databases such as MySQL, PostgreSQL, SQLite, Microsoft SQL Server and more. 