MySQL Add Foreign Key

Last updated on July 29th, 2020 at 04:12 am

Foreign Key helps establish database relationships and maintain referential integrity. They help link one or more columns in one table to another table. Here’s how to add foreign key in MySQL.

Table of Contents

How to Add Foreign Key in MySQL

Here are the steps to add foreign key in MySQL. You can add foreign key constraint using CREATE TABLE or ALTER TABLE statements in SQL.

Here’s the syntax to create foreign key in MySQL.

Using ALTER TABLE

ALTER TABLE table_name 
ADD CONSTRAINT constraint_name 
FOREIGN KEY (foreign_key_name,...) 
REFERENCES parent_table(column_name,...);

In the above query, table_name is the the table where you want to add foreign key. constraint_name is the name of the foreign key constraint. foreign_key_name, … is the list of foreign key columns.

parent_table is the table to which your foreign_key references, followed by list of column names in that table

Bonus Read : MySQL Alter Table Column

Using CREATE TABLE

CREATE TABLE table_name(
    column_name column_description,
    CONSTRAINT constraint_name
    FOREIGN KEY (foreign_key_name,...) 
        REFERENCES parent_table(column_name,...)
)

In the above query, table_name is the table where you want to add foreign key. constraint_name is the name of the foreign key constraint. foreign_key_name, … is the list of foreign key columns.

parent_table is the table to which your foreign_key references, followed by list of column names in that table.

Please note, in ALTER TABLE you need to use ADD CONSTRAINT while in CREATE TABLE you need to use only CONSTRAINT keyword.

Bonus Read : MySQL DROP FOREIGN KEY Constraint

MySQL ADD FOREIGN KEY Examples

Let’s say you have the following tables.

Let’s create 2 tables (categories and orders) and add foreign key constraint to orders, referencing id column in categories table.

mysql> create table categories(id int auto_increment primary key,name varchar(255));

mysql> create table orders(id int auto_increment primary key,category_id int,
 CONSTRAINT fk_cat 
 FOREIGN KEY (category_id) 
 REFERENCES categories(id));

mysql> describe orders;
+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| id          | int(11) | NO   | PRI | NULL    | auto_increment |
| category_id | int(11) | YES  | MUL | NULL    |                |
+-------------+---------+------+-----+---------+----------------+

Bonus Read : MySQL DROP UNIQUE CONSTRAINT

Let’s take a look at the same example using ALTER TABLE statement.

mysql> create table orders(id int auto_increment primary key,category_id int);

mysql> create table categories(id int auto_increment primary key,name varchar(255));

mysql> ALTER TABLE orders
     ADD CONSTRAINT fk_cat
     FOREIGN KEY (category_id)
     REFERENCES categories(id);

mysql> describe orders;
+-------------+---------+------+-----+---------+----------------+
| Field       | Type    | Null | Key | Default | Extra          |
+-------------+---------+------+-----+---------+----------------+
| id          | int(11) | NO   | PRI | NULL    | auto_increment |
| category_id | int(11) | YES  | MUL | NULL    |                |
+-------------+---------+------+-----+---------+----------------+

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

How to Fix Incorrect String Value in MySQL

How to Add Total Row in MySQL

MySQL Select Top N Rows