what is foreign key in mysql

In MySQL, a foreign key is one or more columns used to establish and strengthen the link between data in two tables. It indicates that a field in one table is referenced by a field in another table. Foreign keys place restrictions on the data in related tables, allowing MySQL to maintain referential integrity.

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

The foreign key is relative to the primary key.

Primary key (primary key) An attribute or attribute group that uniquely identifies a row in the table. A table can only have one primary key, but can have multiple candidate indexes. Primary keys often form referential integrity constraints with foreign keys to prevent data inconsistencies. The primary key can ensure that the record is unique and the primary key field is not empty. The database management system automatically generates a unique index for the primary key, so the primary key is also a special index.

Foreign key (foreign key) is one or more columns used to establish and strengthen the link between two table data. A foreign key means that a field in one table is referenced by a field in another table. Foreign keys place restrictions on the data in related tables, allowing MySQL to maintain referential integrity.

Foreign key constraints are mainly used to maintain data consistency between two tables. In short, the foreign key of a table is the primary key of another table, and the foreign key connects the two tables. Under normal circumstances, to delete the primary key in a table, you must first ensure that there are no identical foreign keys in other tables (that is, the primary key in the table does not have a foreign key associated with it).

When defining foreign keys, you need to comply with the following rules:

• The main table must already exist in the database, or be the table currently being created . If it is the latter case, the master table and the slave table are the same table. Such a table is called a self-referential table, and this structure is called self-referential integrity.

• A primary key must be defined for the main table.

• The primary key cannot contain null values, but null values ​​are allowed in foreign keys. That is, as long as every non-null value of the foreign key appears in the specified primary key, the contents of the foreign key are correct.

• Specify the column name or a combination of column names after the table name of the main table. This column or combination of columns must be the primary key or candidate key of the primary table.

• The number of columns in the foreign key must be the same as the number of columns in the primary key of the main table.

• The data type of the column in the foreign key must be the same as the data type of the corresponding column in the primary key of the main table.

Create foreign key

MySQL creates foreign key syntax

The following syntax explains how to CREATE TABLE Define foreign keys in the subtable in the statement.

CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name (columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action

Let’s look at the above syntax in more detail:

• CONSTRAINT clause allows you to define the constraint name for a foreign key constraint. If you omit it, MySQL will automatically generate a name.
• FOREIGN KEYThe clause specifies the columns in the child table that reference the primary key columns in the parent table. You can place a foreign key name after the FOREIGN KEY clause, or let MySQL create one for you. Note that MySQL automatically creates an index with the name foreign_key_name. The
• REFERENCES clause specifies references to columns in the parent table and its child tables. The number of columns in the child and parent tables specified in FOREIGN KEY and REFERENCES must be the same. The
• ON DELETE clause allows you to define how records in the child table perform operations when records in the parent table are deleted. If you omit the ON DELETE clause and delete records in the parent table, MySQL will refuse to delete the associated data in the child table. In addition, MySQL also provides some operations so that you can use additional options, such as ON DELETE CASCADE, when deleting records in the parent table, MySQL can delete records in the child table that reference records in the parent table. If you do not wish to delete related records in the child table, use the ON DELETE SET NULL operation instead. When a record in the parent table is deleted, MySQL will set the foreign key column value in the child table to NULL, provided that the foreign key column in the child table must accept the NULL value . Note that MySQL will reject the delete if the ON DELETE NO ACTION or ON DELETE RESTRICT action is used.
• ON UPDATE clause allows you to specify what happens to rows in the child table when rows in the parent table are updated. You can omit the ON UPDATE clause to have MySQL reject any updates to rows in the child table when rows in the parent table are updated. The ON UPDATE CASCADE operation allows you to perform a crosstab update, and when a row in the parent table is updated, the ON UPDATE SET NULL operation resets the value in the row in the child table to NULL value. ON UPDATE NO ACTION or UPDATE RESTRICT action rejects any update.

MySQL example of creating a table foreign key

以下示例创建一个dbdemo数据库和两个表：categories和products。每个类别都有一个或多个产品，每个产品只属于一个类别。 products表中的cat_id字段被定义为具有UPDATE ON CASCADE和DELETE ON RESTRICT操作的外键。

CREATE DATABASE IF NOT EXISTS dbdemo;

USE dbdemo;

CREATE TABLE categories(
   cat_id int not null auto_increment primary key,
   cat_name varchar(255) not null,
   cat_description text
) ENGINE=InnoDB;

CREATE TABLE products(
   prd_id int not null auto_increment primary key,
   prd_name varchar(355) not null,
   prd_price decimal,
   cat_id int not null,
   FOREIGN KEY fk_cat(cat_id)
   REFERENCES categories(cat_id)
   ON UPDATE CASCADE
   ON DELETE RESTRICT
)ENGINE=InnoDB;

添加外键

MySQL添加外键语法

要将外键添加到现有表中，请使用ALTER TABLE语句与上述外键定义语法：

ALTER table_name
ADD CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name(columns)
REFERENCES parent_table(columns)
ON DELETE action
ON UPDATE action;

MySQL添加外键示例

现在，我们添加一个名为vendors的新表，并更改products表以包含供应商ID字段：

USE dbdemo;

CREATE TABLE vendors(
    vdr_id int not null auto_increment primary key,
    vdr_name varchar(255)
)ENGINE=InnoDB;

ALTER TABLE products 
ADD COLUMN vdr_id int not null AFTER cat_id;

要在products表中添加外键，请使用以下语句：

ALTER TABLE products
ADD FOREIGN KEY fk_vendor(vdr_id)
REFERENCES vendors(vdr_id)
ON DELETE NO ACTION
ON UPDATE CASCADE;

现在，products表有两个外键，一个是引用categories表，另一个是引用vendors表。

删除MySQL外键

您还可以使用ALTER TABLE语句将外键删除，如下语句：

ALTER TABLE table_name 
DROP FOREIGN KEY constraint_name;

在上面的声明中：

• 首先，指定要从中删除外键的表名称。
• 其次，将约束名称放在DROP FOREIGN KEY子句之后。

请注意，constraint_name是在创建或添加外键到表时指定的约束的名称。 如果省略它，MySQL会为您生成约束名称。

要获取生成的表的约束名称，请使用SHOW CREATE TABLE语句，如下所示：

SHOW CREATE TABLE table_name;

例如，要查看products表的外键，请使用以下语句：

SHOW CREATE TABLE products;

以下是语句的输出：

CREATE TABLE products (
  prd_id int(11) NOT NULL AUTO_INCREMENT,
  prd_name varchar(355) NOT NULL,
  prd_price decimal(10,0) DEFAULT NULL,
  cat_id int(11) NOT NULL,
  vdr_id int(11) NOT NULL,
  PRIMARY KEY (prd_id),
  KEY fk_cat (cat_id),
  KEY fk_vendor(vdr_id),

  CONSTRAINT products_ibfk_2 
  FOREIGN KEY (vdr_id) 
  REFERENCES vendors (vdr_id) 
  ON DELETE NO ACTION 
  ON UPDATE CASCADE,

  CONSTRAINT products_ibfk_1 
  FOREIGN KEY (cat_id) 
  REFERENCES categories (cat_id) 
  ON UPDATE CASCADE
) ENGINE=InnoDB;

products表有两个外键约束：products_ibfk_1和products_ibfk_2。

可以使用以下语句删除products表的外键：

ALTER TABLE products 
DROP FOREIGN KEY products_ibfk_1;

ALTER TABLE products 
DROP FOREIGN KEY products_ibfk_2;

MySQL禁用外键检查

有时，因为某种原因需要禁用外键检查(例如将CSV文件中的数据导入表中)非常有用。 如果不禁用外键检查，则必须以正确的顺序加载数据，即必须首先将数据加载到父表中，然后再将数据加载导入到子表中，这可能是乏味的。 但是，如果禁用外键检查，则可以按任何顺序加载导入数据。

除非禁用外键检查，否则不能删除由外键约束引用的表。删除表时，还会删除为表定义的任何约束。

要禁用外键检查，请使用以下语句：

SET foreign_key_checks = 0;

当然，可以使用以下语句启用它：

SET foreign_key_checks = 1;

【相关推荐：mysql视频教程】

The above is the detailed content of what is foreign key in mysql. For more information, please follow other related articles on the PHP Chinese website!