{"id":2173,"date":"2013-05-08T20:11:44","date_gmt":"2013-05-09T04:11:44","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=2173"},"modified":"2023-12-27T22:06:31","modified_gmt":"2023-12-28T05:06:31","slug":"mysql-foreign-key","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-foreign-key\/","title":{"rendered":"MySQL Foreign Key"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about MySQL foreign key<\/strong> and how to create, drop, and disable a foreign key constraint.<\/p>\n\n\n\n

Introduction to MySQL foreign key<\/h2>\n\n\n\n

A foreign key is a column or group of columns in a table that links to a column or group of columns in another table. The foreign key places constraints on data in the related tables, which allows MySQL to maintain referential integrity.<\/p>\n\n\n\n

Let’s take a look at the following customers<\/code> and orders<\/code> tables from the sample database<\/a>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

In this diagram, each customer can have zero or many orders and each order belongs to one customer.<\/p>\n\n\n\n

The relationship between customers<\/code> table and orders<\/code> table is one-to-many<\/strong>. This relationship is established via the foreign key in the orders<\/code> table specified by the customerNumber<\/code> column.<\/p>\n\n\n\n

The customerNumber<\/code> column in the orders<\/code> table links to the customerNumber<\/code> primary key column in the customers<\/code> table.<\/p>\n\n\n\n

The customers<\/code> table is called the parent table<\/em> or referenced table<\/em>, and the orders<\/code> table is known as the child table<\/em> or referencing table<\/em>.<\/p>\n\n\n\n

Typically, the foreign key columns of the child table often refer to the primary key<\/a> columns of the parent table.<\/p>\n\n\n\n

A table can have more than one foreign key where each foreign key references a primary key of the different parent tables.<\/p>\n\n\n\n

Once a foreign key constraint is in place, the foreign key columns from the child table must have the corresponding row in the parent key columns of the parent table, or values in these foreign key columns must be NULL<\/code> (see the SET NULL<\/code> action example below).<\/p>\n\n\n\n

For example, each row in the orders<\/code> table has a customerNumber<\/code> that exists in the customerNumber<\/code> column of the customers<\/code> table. Multiple rows in the orders<\/code> table can have the same customerNumber<\/code>.<\/p>\n\n\n\n

Self-referencing foreign key<\/h3>\n\n\n\n

Sometimes, the child and parent tables may refer to the same table. In this case, the foreign key references back to the primary key within the same table.<\/p>\n\n\n\n

See the following employees<\/code> table from the sample database<\/a>.<\/p>\n\n\n\n

\"\"<\/figure>\n\n\n\n

The reportTo<\/code> column is a foreign key that refers to the employeeNumber<\/code> column which is the primary key of the employees<\/code> table.<\/p>\n\n\n\n

This relationship allows the employees<\/code> table to store the reporting structure between employees and managers. Each employee reports to zero or one employee and an employee can have zero or many subordinates.<\/p>\n\n\n\n

The foreign key on the column reportTo<\/code> is known as a recursive<\/em> or self-referencing<\/em> foreign key.<\/p>\n\n\n\n

MySQL FOREIGN KEY syntax<\/h2>\n\n\n\n

Here is the basic syntax of defining a foreign key constraint in the CREATE TABLE<\/a><\/code> or ALTER TABLE<\/a><\/code> statement:<\/p>\n\n\n

[CONSTRAINT constraint_name]\nFOREIGN KEY [foreign_key_name] (column_name, ...)\nREFERENCES parent_table(colunm_name,...)\n[ON DELETE<\/span> reference_option]\n[ON<\/span> UPDATE<\/span> reference_option]<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax:<\/p>\n\n\n\n
First, specify the name of the foreign key constraint that you want to create after the CONSTRAINT<\/code> keyword. If you omit the constraint name, MySQL automatically generates a name for the foreign key constraint.<\/p>\n\n\n\n
Second, specify a list of comma-separated foreign key columns after the FOREIGN KEY<\/code> keywords. The foreign key name is also optional and is generated automatically if you skip it.<\/p>\n\n\n\n
Third, specify the parent table followed by a list of comma-separated columns to which the foreign key columns reference.<\/p>\n\n\n\n
Finally, specify how the foreign key maintains the referential integrity between the child and parent tables by using the ON DELETE<\/code> and ON UPDATE<\/code> clauses. The reference_option<\/code> determines the action that MySQL will take when values in the parent key columns are deleted (ON DELETE<\/code>) or updated (ON UPDATE<\/code>).<\/p>\n\n\n\n
MySQL has five reference options: CASCADE<\/code>, SET NULL<\/code>, NO ACTION<\/code>, RESTRICT<\/code>, and SET DEFAULT<\/code>.<\/p>\n\n\n\n