Внешние ключи mysql что это

Foreign key constraint naming is governed by the following rules:

The CONSTRAINT symbol value is used, if defined.

If the CONSTRAINT symbol clause is not defined, or a symbol is not included following the CONSTRAINT keyword, a constraint name name is generated automatically.

Prior to MySQL 8.0.16, if the CONSTRAINT symbol clause was not defined, or a symbol was not included following the CONSTRAINT keyword, both InnoDB and NDB storage engines would use the FOREIGN_KEY index_name if defined. In MySQL 8.0.16 and higher, the FOREIGN_KEY index_name is ignored.

The CONSTRAINT symbol value, if defined, must be unique in the database. A duplicate symbol results in an error similar to: ERROR 1005 (HY000): Can’t create table ‘test.fk1’ (errno: 121) .

NDB Cluster stores foreign names using the same lettercase with which they are created. Prior to version 8.0.20, when processing SELECT and other SQL statements, NDB compared the names of foreign keys in such statements with the names as stored in a case-sensitive fashion when lower_case_table_names was equal to 0. In NDB 8.0.20 and later, this value no longer has any effect on how such comparisons are made, and they are always done without regard to lettercase. (Bug #30512043)

Table and column identifiers in a FOREIGN KEY . REFERENCES clause can be quoted within backticks ( ` ). Alternatively, double quotation marks ( » ) can be used if the ANSI_QUOTES SQL mode is enabled. The lower_case_table_names system variable setting is also taken into account.

Conditions and Restrictions

Foreign key constraints are subject to the following conditions and restrictions:

Parent and child tables must use the same storage engine, and they cannot be defined as temporary tables.

Creating a foreign key constraint requires the REFERENCES privilege on the parent table.

Corresponding columns in the foreign key and the referenced key must have similar data types. The size and sign of fixed precision types such as INTEGER and DECIMAL must be the same . The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

MySQL supports foreign key references between one column and another within a table. (A column cannot have a foreign key reference to itself.) In these cases, a “ child table record ” refers to a dependent record within the same table.

MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later if you create another index that can be used to enforce the foreign key constraint. index_name , if given, is used as described previously.

InnoDB permits a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are the first columns in the same order. Hidden columns that InnoDB adds to an index are also considered (see Section 15.6.2.1, “Clustered and Secondary Indexes”).

NDB requires an explicit unique key (or primary key) on any column referenced as a foreign key. InnoDB does not, which is an extension of standard SQL.

Index prefixes on foreign key columns are not supported. Consequently, BLOB and TEXT columns cannot be included in a foreign key because indexes on those columns must always include a prefix length.

InnoDB does not currently support foreign keys for tables with user-defined partitioning. This includes both parent and child tables.

This restriction does not apply for NDB tables that are partitioned by KEY or LINEAR KEY (the only user partitioning types supported by the NDB storage engine); these may have foreign key references or be the targets of such references.

A table in a foreign key relationship cannot be altered to use another storage engine. To change the storage engine, you must drop any foreign key constraints first.

A foreign key constraint cannot reference a virtual generated column.

For information about how the MySQL implementation of foreign key constraints differs from the SQL standard, see Section 1.7.2.3, “FOREIGN KEY Constraint Differences”.

Referential Actions

When an UPDATE or DELETE operation affects a key value in the parent table that has matching rows in the child table, the result depends on the referential action specified by ON UPDATE and ON DELETE subclauses of the FOREIGN KEY clause. Referential actions include:

CASCADE : Delete or update the row from the parent table and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. Between two tables, do not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table.

If a FOREIGN KEY clause is defined on both tables in a foreign key relationship, making both tables a parent and child, an ON UPDATE CASCADE or ON DELETE CASCADE subclause defined for one FOREIGN KEY clause must be defined for the other in order for cascading operations to succeed. If an ON UPDATE CASCADE or ON DELETE CASCADE subclause is only defined for one FOREIGN KEY clause, cascading operations fail with an error.

Cascaded foreign key actions do not activate triggers.

SET NULL : Delete or update the row from the parent table and set the foreign key column or columns in the child table to NULL . Both ON DELETE SET NULL and ON UPDATE SET NULL clauses are supported.

If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL .

RESTRICT : Rejects the delete or update operation for the parent table. Specifying RESTRICT (or NO ACTION ) is the same as omitting the ON DELETE or ON UPDATE clause.

NO ACTION : A keyword from standard SQL. In MySQL, equivalent to RESTRICT . The MySQL Server rejects the delete or update operation for the parent table if there is a related foreign key value in the referenced table. Some database systems have deferred checks, and NO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT .

SET DEFAULT : This action is recognized by the MySQL parser, but both InnoDB and NDB reject table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.

For storage engines that support foreign keys, MySQL rejects any INSERT or UPDATE operation that attempts to create a foreign key value in a child table if there is no matching candidate key value in the parent table.

For an ON DELETE or ON UPDATE that is not specified, the default action is always NO ACTION .

As the default, an ON DELETE NO ACTION or ON UPDATE NO ACTION clause that is specified explicitly does not appear in SHOW CREATE TABLE output or in tables dumped with mysqldump . RESTRICT , which is an equivalent non-default keyword, appears in SHOW CREATE TABLE output and in tables dumped with mysqldump .

For NDB tables, ON UPDATE CASCADE is not supported where the reference is to the parent table’s primary key.

As of NDB 8.0.16: For NDB tables, ON DELETE CASCADE is not supported where the child table contains one or more columns of any of the TEXT or BLOB types. (Bug #89511, Bug #27484882)

InnoDB performs cascading operations using a depth-first search algorithm on the records of the index that corresponds to the foreign key constraint.

A foreign key constraint on a stored generated column cannot use CASCADE , SET NULL , or SET DEFAULT as ON UPDATE referential actions, nor can it use SET NULL or SET DEFAULT as ON DELETE referential actions.

A foreign key constraint on the base column of a stored generated column cannot use CASCADE , SET NULL , or SET DEFAULT as ON UPDATE or ON DELETE referential actions.

Foreign Key Constraint Examples

This simple example relates parent and child tables through a single-column foreign key:

This is a more complex example in which a product_order table has foreign keys for two other tables. One foreign key references a two-column index in the product table. The other references a single-column index in the customer table:

Adding Foreign Key Constraints

You can add a foreign key constraint to an existing table using the following ALTER TABLE syntax:

The foreign key can be self referential (referring to the same table). When you add a foreign key constraint to a table using ALTER TABLE , remember to first create an index on the column(s) referenced by the foreign key.

Dropping Foreign Key Constraints

You can drop a foreign key constraint using the following ALTER TABLE syntax:

If the FOREIGN KEY clause defined a CONSTRAINT name when you created the constraint, you can refer to that name to drop the foreign key constraint. Otherwise, a constraint name was generated internally, and you must use that value. To determine the foreign key constraint name, use SHOW CREATE TABLE :

Adding and dropping a foreign key in the same ALTER TABLE statement is supported for ALTER TABLE . ALGORITHM=INPLACE . It is not supported for ALTER TABLE . ALGORITHM=COPY .

Foreign Key Checks

In MySQL, InnoDB and NDB tables support checking of foreign key constraints. Foreign key checking is controlled by the foreign_key_checks variable, which is enabled by default. Typically, you leave this variable enabled during normal operation to enforce referential integrity. The foreign_key_checks variable has the same effect on NDB tables as it does for InnoDB tables.

The foreign_key_checks variable is dynamic and supports both global and session scopes. For information about using system variables, see Section 5.1.9, “Using System Variables”.

Disabling foreign key checking is useful when:

Dropping a table that is referenced by a foreign key constraint. A referenced table can only be dropped after foreign_key_checks is disabled. When you drop a table, constraints defined on the table are also dropped.

Reloading tables in different order than required by their foreign key relationships. For example, mysqldump produces correct definitions of tables in the dump file, including foreign key constraints for child tables. To make it easier to reload dump files for tables with foreign key relationships, mysqldump automatically includes a statement in the dump output that disables foreign_key_checks . This enables you to import the tables in any order in case the dump file contains tables that are not correctly ordered for foreign keys. Disabling foreign_key_checks also speeds up the import operation by avoiding foreign key checks.

Executing LOAD DATA operations, to avoid foreign key checking.

Performing an ALTER TABLE operation on a table that has a foreign key relationship.

When foreign_key_checks is disabled, foreign key constraints are ignored, with the following exceptions:

Recreating a table that was previously dropped returns an error if the table definition does not conform to the foreign key constraints that reference the table. The table must have the correct column names and types. It must also have indexes on the referenced keys. If these requirements are not satisfied, MySQL returns Error 1005 that refers to errno: 150 in the error message, which means that a foreign key constraint was not correctly formed.

Altering a table returns an error (errno: 150) if a foreign key definition is incorrectly formed for the altered table.

Dropping an index required by a foreign key constraint. The foreign key constraint must be removed before dropping the index.

Creating a foreign key constraint where a column references a nonmatching column type.

Disabling foreign_key_checks has these additional implications:

It is permitted to drop a database that contains tables with foreign keys that are referenced by tables outside the database.

It is permitted to drop a table with foreign keys referenced by other tables.

Enabling foreign_key_checks does not trigger a scan of table data, which means that rows added to a table while foreign_key_checks is disabled are not checked for consistency when foreign_key_checks is re-enabled.

Locking

MySQL extends metadata locks, as necessary, to tables that are related by a foreign key constraint. Extending metadata locks prevents conflicting DML and DDL operations from executing concurrently on related tables. This feature also enables updates to foreign key metadata when a parent table is modified. In earlier MySQL releases, foreign key metadata, which is owned by the child table, could not be updated safely.

If a table is locked explicitly with LOCK TABLES , any tables related by a foreign key constraint are opened and locked implicitly. For foreign key checks, a shared read-only lock ( LOCK TABLES READ ) is taken on related tables. For cascading updates, a shared-nothing write lock ( LOCK TABLES WRITE ) is taken on related tables that are involved in the operation.

Foreign Key Definitions and Metadata

To view a foreign key definition, use SHOW CREATE TABLE :

You can obtain information about foreign keys from the INFORMATION_SCHEMA.KEY_COLUMN_USAGE table. An example of a query against this table is shown here:

You can obtain information specific to InnoDB foreign keys from the INNODB_FOREIGN and INNODB_FOREIGN_COLS tables. Example queries are show here:

Foreign Key Errors

In the event of a foreign key error involving InnoDB tables (usually Error 150 in the MySQL Server), information about the latest foreign key error can be obtained by checking SHOW ENGINE INNODB STATUS output.

If a user has table-level privileges for all parent tables, ER_NO_REFERENCED_ROW_2 and ER_ROW_IS_REFERENCED_2 error messages for foreign key operations expose information about parent tables. If a user does not have table-level privileges for all parent tables, more generic error messages are displayed instead ( ER_NO_REFERENCED_ROW and ER_ROW_IS_REFERENCED ).

An exception is that, for stored programs defined to execute with DEFINER privileges, the user against which privileges are assessed is the user in the program DEFINER clause, not the invoking user. If that user has table-level parent table privileges, parent table information is still displayed. In this case, it is the responsibility of the stored program creator to hide the information by including appropriate condition handlers.

MySQL Foreign Key

Summary: in this tutorial, you will learn about MySQL foreign key and how to create, drop, and disable a foreign key constraint.

Introduction to MySQL foreign key

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.

Let’s take a look at the following customers and orders tables from the sample database.

In this diagram, each customer can have zero or many orders and each order belongs to one customer.

The relationship between customers table and orders table is one-to-many. And this relationship is established by the foreign key in the orders table specified by the customerNumber column.

The customerNumber column in the orders table links to the customerNumber primary key column in the customers table.

The customers table is called the parent table or referenced table, and the orders table is known as the child table or referencing table.

Typically, the foreign key columns of the child table often refer to the primary key columns of the parent table.

A table can have more than one foreign key where each foreign key references to a primary key of the different parent tables.

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 column must be NULL (see the SET NULL action example below).

For example, each row in the orders table has a customerNumber that exists in the customerNumber column of the customers table. Multiple rows in the orders table can have the same customerNumber .

Self-referencing foreign key

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.

See the following employees table from the sample database.

The reportTo column is a foreign key that refers to the employeeNumber column which is the primary key of the employees table.

This relationship allows the employees 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.

The foreign key on the column reportTo is known as a recursive or self-referencing foreign key.

MySQL FOREIGN KEY syntax

Here is the basic syntax of defining a foreign key constraint in the CREATE TABLE or ALTER TABLE statement:

First, specify the name of foreign key constraint that you want to create after the CONSTRAINT keyword. If you omit the constraint name, MySQL automatically generates a name for the foreign key constraint.

Second, specify a list of comma-separated foreign key columns after the FOREIGN KEY keywords. The foreign key name is also optional and is generated automatically if you skip it.

Third, specify the parent table followed by a list of comma-separated columns to which the foreign key columns reference.

Finally, specify how foreign key maintains the referential integrity between the child and parent tables by using the ON DELETE and ON UPDATE clauses. The reference_option determines action which MySQL will take when values in the parent key columns are deleted ( ON DELETE ) or updated ( ON UPDATE ).

MySQL has five reference options: CASCADE , SET NULL , NO ACTION , RESTRICT , and SET DEFAULT .

CASCADE : if a row from the parent table is deleted or updated, the values of the matching rows in the child table automatically deleted or updated.
SET NULL : if a row from the parent table is deleted or updated, the values of the foreign key column (or columns) in the child table are set to NULL .
RESTRICT : if a row from the parent table has a matching row in the child table, MySQL rejects deleting or updating rows in the parent table.
NO ACTION : is the same as RESTRICT .
SET DEFAULT : is recognized by the MySQL parser. However, this action is rejected by both InnoDB and NDB tables.

In fact, MySQL fully supports three actions: RESTRICT , CASCADE and SET NULL .

If you don’t specify the ON DELETE and ON UPDATE clause, the default action is RESTRICT .

MySQL FOREIGN KEY examples

Let’s create a new database called fkdemo for the demonstration.

RESTRICT & NO ACTION actions

Inside the fkdemo database, create two tables categories and products :

The categoryId in the products table is the foreign key column that refers to the categoryId column in the categories table.

Because we don’t specify any ON UPDATE and ON DELETE clauses, the default action is RESTRICT for both update and delete operation.

The following steps illustrate the RESTRICT action.

1) Insert two rows into the categories table:

2) Select data from the categories table:

3) Insert a new row into the products table:

It works because the categoryId 1 exists in the categories table.

4) Attempt to insert a new row into the products table with a categoryId value does not exist in the categories table:

MySQL issued the following error:

5) Update the value in the categoryId column in the categories table to 100 :

MySQL issued this error:

Because of the RESTRICT option, you cannot delete or update categoryId 1 since it is referenced by the productId 1 in the products table.

CASCADE action

These steps illustrate how ON UPDATE CASCADE and ON DELETE CASCADE actions work.

1) Drop the products table:

2) Create the products table with the ON UPDATE CASCADE and ON DELETE CASCADE options for the foreign key:

3) Insert four rows into the products table:

4) Select data from the products table:

5) Update categoryId 1 to 100 in the categories table:

6) Verify the update:

7) Get data from the products table:

As you can see, two rows with value 1 in the categoryId column of the products table were automatically updated to 100 because of the ON UPDATE CASCADE action.

8) Delete categoryId 2 from the categories table:

9) Verify the deletion:

10) Check the products table:

All products with categoryId 2 from the products table were automatically deleted because of the ON DELETE CASCADE action.

SET NULL action

These steps illustrate how the ON UPDATE SET NULL and ON DELETE SET NULL actions work.

1) Drop both categories and products tables:

2) Create the categories and products tables:

The foreign key in the products table changed to ON UPDATE SET NULL and ON DELETE SET NULL options.

3) Insert rows into the categories table:

4) Insert rows into the products table:

5) Update categoryId from 1 to 100 in the categories table:

6) Verify the update:

7) Select data from the products table:

The rows with the categoryId 1 in the products table were automatically set to NULL due to the ON UPDATE SET NULL action.

8) Delete the categoryId 2 from the categories table:

9) Check the products table:

The values in the categoryId column of the rows with categoryId 2 in the products table were automatically set to NULL due to the ON DELETE SET NULL action.

Drop MySQL foreign key constraints

To drop a foreign key constraint, you use the ALTER TABLE statement:

First, specify the name of the table from which you want to drop the foreign key after the ALTER TABLE keywords.
Second, specify the constraint name after the DROP FOREIGN KEY keywords.

Notice that constraint_name is the name of the foreign key constraint specified when you created or added the foreign key constraint to the table.

To obtain the generated constraint name of a table, you use the SHOW CREATE TABLE statement:

For example, to see the foreign keys of the products table, you use the following statement:

The following is the output of the statement:

As you can see clearly from the output, the table products table has one foreign key constraint: fk_category

And this statement drops the foreign key constraint of the products table:

To ensure that the foreign key constraint has been dropped, you can view the structure of the products table:

MySQL Foreign Key - after drop foreign key constraint

Disabling foreign key checks

Sometimes, it is very useful to disable foreign key checks e.g., when you import data from a CSV file into a table. If you don’t disable foreign key checks, you have to load data into a proper order i.e., you have to load data into parent tables first and then child tables, which can be tedious. However, if you disable the foreign key checks, you can load data into tables in any order.

To disable foreign key checks, you use the following statement:

And you can enable it by using the following statement:

In this tutorial, you have learned about the MySQL foreign key and how to create a foreign key constraint with various reference options.