MySQL - add FOREIGN KEY to existing table

In this article, we would like to show you how to add FOREIGN KEY to the existing table in MySQL.

Quick solution:

ALTER TABLE
    `table_name`
ADD CONSTRAINT 
    `constraint_name` FOREIGN KEY (`column_name`)
    REFERENCES `reference_table`(`reference_column`);

Practical example

To show how to add FOREIGN KEY to the existing table, we will use the following tables

Note:

At the end of this article you can find database preparation SQL queries.

Example

In this example, we will add fk_group_id foreign key to the group_id column from users table which is a reference to the id column from groups table.

Query:

ALTER TABLE `users`
ADD CONSTRAINT `fk_group_id` FOREIGN KEY (`group_id`) REFERENCES `groups`(`id`);

Output:

Database preparation

create_tables.sql file:

CREATE TABLE `groups` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`group_name` VARCHAR(50) NOT NULL,
	PRIMARY KEY (`id`)
);

CREATE TABLE `users` (
	`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
	`username` VARCHAR(50) NOT NULL,
	`group_id` INT(10) UNSIGNED,
	PRIMARY KEY (`id`)
);

insert_data.sql file:

INSERT INTO `groups`
    (`group_name`)
VALUES
    ('group1'),
	 ('group2');

INSERT INTO `users`
    (`username`, `group_id`)
VALUES
    ('user1', 1),
    ('user2', 1),
    ('user3', 2),
	('user4', 2);