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!
