EN
MySQL - add FOREIGN KEY to existing table
0 points
In this article, we would like to show you how to add FOREIGN KEY to the existing table in MySQL.
Quick solution:
xxxxxxxxxx1
ALTER TABLE2
`table_name`3
ADD CONSTRAINT 4
`constraint_name` FOREIGN KEY (`column_name`)5
REFERENCES `reference_table`(`reference_column`);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.
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:
xxxxxxxxxx1
ALTER TABLE `users`2
ADD CONSTRAINT `fk_group_id` FOREIGN KEY (`group_id`) REFERENCES `groups`(`id`);Output:
create_tables.sql file:
xxxxxxxxxx1
CREATE TABLE `groups` (2
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,3
`group_name` VARCHAR(50) NOT NULL,4
PRIMARY KEY (`id`)5
);6
7
CREATE TABLE `users` (8
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,9
`username` VARCHAR(50) NOT NULL,10
`group_id` INT(10) UNSIGNED,11
PRIMARY KEY (`id`)12
);insert_data.sql file:
xxxxxxxxxx1
INSERT INTO `groups`2
(`group_name`)3
VALUES4
('group1'),5
('group2');6
7
INSERT INTO `users`8
(`username`, `group_id`)9
VALUES10
('user1', 1),11
('user2', 1),12
('user3', 2),13
('user4', 2);