MySQL - create table with FOREIGN KEY

In this article, we would like to show you how to create a table with FOREIGN KEY in MySQL.

Quick solution:

CREATE TABLE `table1_name` (
    `column1` DATA_TYPE,
    `column2` DATA_TYPE,
    `columnN` DATA_TYPE,
    PRIMARY KEY (`column_name`),
    FOREIGN KEY (`column_name`) REFERENCES `table2_name`(`reference_column`)
);

Note:

Go to the official documentation to see available DATA_TYPES.

Practical example

In this example, we will create two tables:

• groups table with two columns:
  • id- with PRIMARY KEY constraint
  • group_name

and users table with three columns:

• id - with PRIMARY KEY constraint
• username
• group_id - which will be our FOREIGN KEY with groups.id column reference

Query:

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`),
	FOREIGN KEY (`group_id`) REFERENCES `groups`(`id`)
);

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.

References

• MySQL Data Types