EN
MySQL - create table with FOREIGN KEY
0 points
In this article, we would like to show you how to create a table with FOREIGN KEY in MySQL.
Quick solution:
xxxxxxxxxx1
CREATE TABLE `table1_name` (2
`column1` DATA_TYPE,3
`column2` DATA_TYPE,4
`columnN` DATA_TYPE,5
PRIMARY KEY (`column_name`),6
FOREIGN KEY (`column_name`) REFERENCES `table2_name`(`reference_column`)7
);Note:
Go to the official documentation to see available
DATA_TYPES.
In this example, we will create two tables:
groupstable with two columns:id- withPRIMARY KEYconstraintgroup_name
and users table with three columns:
id- withPRIMARY KEYconstraintusernamegroup_id- which will be ourFOREIGN KEYwithgroups.idcolumn reference
Query:
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
FOREIGN KEY (`group_id`) REFERENCES `groups`(`id`)13
);Result:
As we see in the second picture, the group_id column from users table has FOREIGN KEY constraint which refers to the id column from groups table with PRIMARY KEY constraint.