MySQL Add Foreign Key

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.

 

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!

mm

About Ubiq

Ubiq is a dashboard & reporting platform that makes it easy to get insights from data. Build dashboards, charts & reports for your business in minutes. Try it for free today!