MySQL Foreign Keys Tutorial and Examples
In this article, we introduced what foreign keys are, the rules for foreign keys, and how to use them in MySQL.
In relational databases, foreign keys are used to define a constrained relationship between two entities. Foreign keys are useful for ensuring data integrity.
What is foreign keys?
Foreign keys are used to refer to other tables relative to the primary key. The foreign key associates the rows of the child table with the rows of the parent table through one or more columns of the child table corresponding to the primary key or unique key value of the parent table.
Let’s take a look at the tables country and city from Sakila sample database. Here is their relationship diagram:
country and city relationship
Here is some data from the country table:
Here is some data from the city table:
From this we can see that the country table and the city table is a one-to-many relationship. A country can have multiple cities, and a city can only be located in one country.
If a country already has a city, then you cannot easily delete the country from the country table, otherwise it will cause the city data to be incomplete. You also can’t set a non-existent for a city country_id , otherwise the city data will be wrong.
Foreign key constraints ensure that the data is complete and correct.
Foreign Key Syntax
Let’s look at the foreign key constraints defined by the city table:
Note some of it:
A foreign key is defined here:
After the CONSTRAINT keyword fk_city_country is the name of the foreign key. It is optional.
Following the FOREIGN KEY keyword is the column name that is the foreign key.
Following the REFERENCES keyword are the referenced tables and columns.
ON DELETE and ON UPDATE specify the constraint strategy to be taken when deleting or updating data in the referenced table. You can use one of the following 3 strategies:
- CASCADE : If a row in the referenced table is deleted or updated, the value of the matching row in child table is automatically deleted or updated.
- SET NULL : If a row in the referenced table is deleted or updated, the value of the matching row in child table is set to NULL .
- RESTRICT : A MySQL error is raised when attempting to delete or update a row in the referenced table if a row in the referenced table has a matching row in child table. This is the default policy.
Usually, the table to which the foreign key belongs is called the child table, and the table referenced by the foreign key is called the parent table.
Add Foreign Key Syntax
If the foreign key is not defined when the table is created, you can also add the foreign key later by using the following syntax:
- Use the ALTER TABLE statement to modify the definition of the table.
- Use to ADD [CONSTRAINT foreign_key_name] clause add a constraint named foreign_key_name . [CONSTRAINT foreign_key_name] is optional.
- A foreign key is defined using FOREIGN KEY (column)) REFERENCES parent_table_name (column) .
Delete Foreign Key Syntax
To drop a foreign key on a table, one of the following two syntaxes can be used:
- Use the ALTER TABLE statement to modify the definition of the table.
- Specify the foreign key name after DROP FOREIGN KEY , that is, the constraint name.
- Specify the constraint name after DROP CONSTRAINT . It can remove any constraint by name, not just foreign keys.
FOREIGN KEY Examples
The following example will create user and user_hobby two tables in the testdb database. Where foreign keys are used in the user_hobby table to reference the user table. Let’s create the user table first, and the user_hobby table will be created according to the respective situation in the following examples. Please follow the steps below:
Log in to the MySQL database as the root user:
Enter the password of the root user.
Note: You can also log in as any other user with appropriate database privileges.
Select the testdb database using the following statement:
If you haven’t created the database yet, run the following statement first:
Insert two rows in to the user table:
So far, we have created the user table.
CASCADE strategy
If ON DELETE and ON UPDATE use the CASCADE strategy:
- When a row in the parent table is deleted, the matching row in the child table is also deleted.
- When the key value of the row in the parent table is updated, the fields of the matching row in the child table are also updated.
Use the following SQL to create the user_hobby table with a foreign key using the CASCADE strategy.
Insert rows into two tables:
At this point the rows in the user_hobby table as following:
Let’s take a look at the associated operation of the child table caused by the UPDATE and DELETE operation on the parent table:
UPDATE operate on the parent table
We modify the value of the key user_id in the parent table user from 1 to 100 :
At this point the rows in the user_hobby table:
We found that the column values of those rows in the table user_hobby that match user_id = 1 in user table are also modified to 100 .
DELETE operate on the parent table
At this point the data in the user_hobby table:
We found that those rows in the user_hobby table that matched user_id = 100 in user table were deleted.
RESTRICT strategy
If the ON DELETE and ON UPDATE use the RESTRICT strategy:
- MySQL prohibits deletion of rows in parent tables that match child tables.
- MySQL prohibits deleting the value of the key of the row in the parent table that matches the child table.
Use the following SQL to create the user_hobby table with foreign keys using the RESTRICT strategy.
Insert data into two tables:
At this point the rows in the user_hobby table:
Let’s see the result of the UPDATE and DELETE operation on the parent table:
UPDATE operate on the parent table
The MySQL server returned the following error:
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`testdb`.`user_hobby`, CONSTRAINT `fk_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT)
DELETE operate on the parent table
The MySQL server returned the following error:
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`testdb`.`user_hobby`, CONSTRAINT `fk_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT)
SET NULL policy
If the ON DELETE and ON UPDATE use the SET NULL strategy:
- When a row of the parent table is deleted, the value of the column of the matching row in the child table is set to NULL .
- When the key value of a row in the parent table is updated, the column value of the matching row in the child table is set to NULL .
Use the following SQL to create the user_hobby table with foreign keys using the SET NULL strategy.
Insert data into two tables:
Let’s take a look at the associated operation of the child table caused by the UPDATE and DELETE operation on the parent table:
UPDATE operate on the parent table
At this point the data in the user_hobby table:
After updated the parent table rows, these rows related in the child table are set to NULL .
DELETE operate on the parent table
Since the above example modifies the data of the table, we reinitialize the data of the two tables:
At this point the data in the user_hobby table:
After deleted the parent table rows, these rows related in the child table are set to NULL .
Self-referencing foreign keys
Sometimes, the child table and the parent table may be the same table. A foreign key in such a table is called a self-referential foreign key.
Typically, self-referential foreign keys are defined in tables that represent tree-like data structures. For example, a table representing categories:
In this table, the parent_category_id column is a foreign key. It references the category_id column of the category table.
This table implements an infinite hierarchy of classification trees. A category can have multiple subcategories, and a subcategory can have 0 or 1 parent category;
Enable or Disable Foreign Key Constraints
To disable foreign key constraints, use the following statement:
To enable foreign key constraints, use the following statement:
Disabling foreign key constraints is useful when bulk importing data.
Conclusion
In this article, we introduced what foreign keys are, the rules for foreign keys, and how to use them in MySQL. Here are the main points of this article:
SQL FOREIGN KEY Constraint
The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
A FOREIGN KEY is a field (or collection of fields) in one table, that refers to the PRIMARY KEY in another table.
The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.
Look at the following two tables:
Persons Table
| PersonID | LastName | FirstName | Age |
|---|---|---|---|
| 1 | Hansen | Ola | 30 |
| 2 | Svendson | Tove | 23 |
| 3 | Pettersen | Kari | 20 |
Orders Table
| OrderID | OrderNumber | PersonID |
|---|---|---|
| 1 | 77895 | 3 |
| 2 | 44678 | 3 |
| 3 | 22456 | 2 |
| 4 | 24562 | 1 |
Notice that the "PersonID" column in the "Orders" table points to the "PersonID" column in the "Persons" table.
The "PersonID" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.
The "PersonID" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.
The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the parent table.
SQL FOREIGN KEY on CREATE TABLE
The following SQL creates a FOREIGN KEY on the "PersonID" column when the "Orders" table is created:
SQL Server / Oracle / MS Access:
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
SQL FOREIGN KEY on ALTER TABLE
To create a FOREIGN KEY constraint on the "PersonID" column when the "Orders" table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
Create Foreign Keys SQL Server
Внешний ключ — это способ обеспечения ссылочной целостности в вашей базе данных SQL Server. Внешний ключ означает, что значения в одной таблице также должны отображаться в другой таблице.
Указанная таблица называется родительской таблицей, а таблица с внешним ключом называется дочерней таблицей. Внешний ключ в дочерней таблице обычно ссылается на первичный ключ в родительской таблице.
Внешний ключ может быть создан с использованием оператора CREATE TABLE или оператора ALTER TABLE.
Создание внешнего ключа — использование оператора CREATE TABLE
Синтаксис
Синтаксис создания внешнего ключа с помощью оператора CREATE TABLE в SQL Server (Transact-SQL):
CONSTRAINT fk_name
FOREIGN KEY (child_col1, child_col2, . child_col_n)
REFERENCES parent_table (parent_col1, parent_col2, . parent_col_n)
[ ON DELETE < NO ACTION | CASCADE | SET NULL | SET DEFAULT >]
[ ON UPDATE < NO ACTION | CASCADE | SET NULL | SET DEFAULT >]
);
child_table — имя дочерней таблицы, которую вы хотите создать.
column1 , column2 — столбцы, которые вы хотите создать в таблице. Каждый столбец должен иметь тип данных. Столбец должен быть определен как NULL или NOT NULL, и если это значение остается пустым, база данных принимает значение NULL как значение по умолчанию.
fk_name — имя ограничения внешнего ключа, которое вы хотите создать.
child_col1 , child_col2 , . child_col_n — столбцы в child_table , которые будут ссылаться на первичный ключ в parent_table (родительской таблице).
parent_table — имя родительской таблицы, первичный ключ которой будет использоваться в child_table .
parent_col1 , parent_col2 , . parent_col3 — столбцы, которые составляют первичный ключ в родительской таблице. Внешний ключ будет обеспечивать связь между этими данными и столбцами child_col1 , child_col2 , . child_col_n в child_table .
ON DELETE — необязательный. Он определяет, что делать с дочерними данными при удалении родительских данных. У вас есть опции NO ACTION, CASCADE, SET NULL или SET DEFAULT.
ON UPDATE — необязательный. Он указывает, что делать с дочерними данными при обновлении родительских данных. У вас есть опции NO ACTION, CASCADE, SET NULL или SET DEFAULT.
NO ACTION — используется в сочетании с ON DELETE или ON UPDATE. Это означает, что никакие действия не выполняются с дочерними данными при удалении или обновлении родительских данных.
CASCADE — используется в сочетании с ON DELETE или ON UPDATE. Это означает, что дочерние данные либо удаляются, либо обновляются, когда родительские данные удаляются или обновляются.
SET NULL — используется в сочетании с ON DELETE или ON UPDATE. Это означает, что дочерние данные установлены в NULL, когда родительские данные удаляются или обновляются.
SET DEFAULT — используется в сочетании с ON DELETE или ON UPDATE. Это означает, что дочерние данные устанавливаются в значения по умолчанию, когда родительские данные удаляются или обновляются.
Пример
Рассмотрим пример создания внешнего ключа в SQL Server (Transact-SQL) с помощью оператора CREATE TABLE. Например:
Как создать внешний ключ sql
Structured Query Language or SQL is a standard Database language that is used to create, maintain and retrieve the data from relational databases like MySQL, Oracle, SQL Server, PostGre, etc.
In order to create a table with Foreign Key, we must have to know few Key Constraints:
Super Key :
An attribute, or set of attributes, that uniquely identifies a tuple within a relation.
Ex: Consider a customer database then,
The customer_id of the relation customer is sufficient to distinguish one tuple from another. Thus, customer_id is a super key. Similarly, the combination of customer_id and customer_name is a super key for the relation customer. Here the customer_name is not a super key, because several people may have the same name.
Candidate Key :
A super key such that no proper subset is a super key within the relation.
Ex: The combination of customer_name and customer_street is sufficient to distinguish the members of the customer relation. Then both, and are candidate keys. Although customer_id and customer_name together can distinguish customer tuples, their combination does not form a candidate key , since the customer_id alone is a candidate key.
Primary Key :
The candidate key is selected to identify tuples uniquely within the relation. Out of all the available candidate keys, a database designer can identify a primary key. The candidate keys that are not selected as the primary key are called alternate keys.
Ex: For the customer relation, we can choose customer_id as the primary key.
Foreign Key :
Foreign keys represent the relationships between tables. A foreign key is a column (or a group of columns) whose values are derived from the primary key of some other table. The table in which a foreign key is defined is called a Foreign table or Details table. The table that defines the primary key and is referenced by the foreign key is called the Primary table or Master table.
We can add a foreign key to a relation in the following ways
Method-1 :
Syntax :
Consider The following customer relationship
| Customer_id | Customer_name | Customer_Address |
|---|---|---|
| 101 | Geek 1 | Chennai |
| 102 | Geek 2 | Delhi |
| 103 | Geek 3 | Bombay |
| 104 | Geek 4 | Pune |
| 105 | Geek 5 | Nashik |
In order to create the following table, we use the following command
Now Consider the Sales relation,
| Customer_id | Item_Id | Payment_Mode |
|---|---|---|
| 101 | 1334151 | COD |
| 101 | 16652 | Debit Card |
| 104 | 1009 | Paypal |
| 102 | 14543 | COD |
So in this relation, Customer_id is a foreign key that is obtained from the above customer relation. We can create the table using the following command.
Method -2 :
Syntax :
Now in order to create the same sales table using customer table we can run the following command