Does mysql have foreign keys?

Mysql has foreign keys, which are mainly used to establish the relationship between the master table and the slave table. It can establish a connection for the data in the two tables and constrain the consistency and integrity of the data in the two tables. When a record is deleted from the main table, the corresponding record from the slave table must also be changed accordingly. A table can have one or more foreign keys. The foreign key can be a null value. If it is not a null value, the value of each foreign key must be equal to a certain value of the primary key in the main table, and the number and corresponding data type of the columns in the foreign key must be the same as those in the primary key of the main table. same.

The operating system of this tutorial: Windows 10 system, mysql version 8.0, Dell G3 computer.

mysql has foreign keys.

MySQL Foreign Key (FOREIGN KEY)

A foreign key is a field in a specified table that matches another field in another table. Foreign keys set constraints on data in related tables, which allows MySQL to maintain referential integrity.

Foreign keys are used to establish the association between the master table and the slave table, establish a connection for the data in the two tables, and constrain the consistency and integrity of the data in the two tables.

For two tables with an associated relationship, the table where the primary key in the associated field is located is the main table (parent table), and the table where the foreign key is located is the secondary table (child table).

When a record is deleted from the main table, the corresponding record from the table must also be changed accordingly. A table can have one or more foreign keys, and the foreign key can be null. If it is not null, the value of each foreign key must be equal to a certain value of the primary key in the main table.

Let’s take a look at the following database diagram from the sample database.

We have two tables: customers and orders. Each customer has zero or more orders, and each order can only belong to one customer. The relationship between the customers table and the orders table is one-to-many, with orders established by the foreign key in the table specified by the customerNumber field. The customerNumber field in the customers table is related to the customerNumber primary key field in the orders table.

The customers table is called the parent table or reference table, and the orders table is called the child table or reference table.

The foreign key can be a column or a group of columns. Columns in the child table usually reference primary key columns in the parent table.

A table can have multiple foreign keys, and each foreign key in a child table can reference a different parent table.

The rows in the child table must contain values ​​that exist in the parent table, for example, each order record in the orders table must have a value that exists in the customers table customerNumber. Therefore, multiple orders can reference the same customer, and this relationship is called one (customer) to many (orders) or one-to-many.

Sometimes, the child table and the parent table are the same. The foreign key refers to the primary key of the table, for example, the following employees table:

The reportTo column is a foreign key that references employeeNumber as the column of the primary key of the employees table to reflect the number of employees. A reporting structure between employees, that is, each employee reports to another employee, and employees can have zero or more direct reports. We have a tutorial on self-joins that can help you query data against such tables.

reportTo foreign key is also known as recursive or self-referential foreign key.

Foreign keys enforce referential integrity, helping you automatically maintain the consistency and integrity of your data. For example, you cannot create an order for a customer that does not exist.

Additionally, you can set up a cascade on the delete operation of the customerNumber foreign key so that when a customer is deleted in the customers table, all orders associated with the customer are also deleted. This saves time and effort using multiple DELETE statements or DELETE JOIN statements.

Same as delete, you can also define a cascade for the customerNumber foreign key on the update operation to perform cross-table updates without using multiple UPDATE statements or UPDATE JOIN statements.

Note: In MySQL, the InnoDB storage engine supports foreign keys, so you must create an InnoDB table to use foreign key constraints.

When mysql defines a foreign key, the following rules need to be followed:

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.

The 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 main 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 a foreign key for the table

MySQL creates a foreign key syntax

The following syntax explains how to define a foreign key in a subtable in the CREATE TABLE statement.

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

Let’s learn the syntax in more detail:

CONSTRAINT子句允许您为外键约束定义约束名称。如果省略它，MySQL将自动生成一个名称。

FOREIGN KEY子句指定子表中引用父表中主键列的列。你可以把一个外键名称放在FOREIGN KEY子句之后，或者让MySQL为你创建一个名字。请注意，MySQL会自动使用foreign_key_name名称创建索引。

REFERENCES子句指定子表中的列所引用的父表及列。在规定的子表和父表的列数FOREIGN KEY和REFERENCES必须相同。

ON DELETE子句允许您定义删除父表中的记录时子表中记录的内容。如果省略ON DELETE子句并删除父表中包含子表中记录的记录，MySQL将拒绝删除。此外，MySQL还为您提供操作，以便您可以使用其他选项，例如ON DELETE CASCADE ，要求MySQL删除子表中的记录，当父表中的记录被删除时，记录将引用父表中的记录。如果您不希望删除子表中的相关记录，请改用ON DELETE SET NULL操作。MySQL会将子表中的外键列值设置为NULL删除父表中的记录时，条件是子表中的外键列必须接受NULL值。请注意，如果您使用ON DELETE NO ACTION或ON DELETE RESTRICT操作，MySQL将拒绝删除。

ON UPDATE子句使您可以指定更新父表中的行时子表中的行会发生什么。您可以省略ON UPDATE子句，以便在更新父表中的行时让MySQL拒绝对子表中行的任何更新。ON UPDATE CASCADE操作允许您执行跨表更新，并且当更新父表ON UPDATE SET NULL中的行时，操作会将子表中的行中的值重置为值NULL。ON UPDATE NO ACTION或UPDATE RESTRICT行动拒绝任何更新。

MySQL创建表外键示例

下面的示例创建一个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 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表。

The above is the detailed content of Does mysql have foreign keys?. For more information, please follow other related articles on the PHP Chinese website!