Languages
[Edit]
EN

MySQL - Create table with FOREIGN KEY

0 points
Created by:
Dirask Community
6930

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:

MySQL - create a table with PRIMARY KEY
MySQL - create a table with PRIMARY KEY
MySQL - create a table with FOREIGN KEY
MySQL - create a table with FOREIGN KEY

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 - Problems

MySQL - Create table with FOREIGN KEY
Native Advertising
🚀
Get your tech brand or product in front of software developers.
For more information Contact us
Dirask - we help you to
solve coding problems.
Ask question.

❤️💻 🙂

Join