Sql 简明教程

SQL - Foreign Key

The SQL Foreign Key

在 SQL 中， Foreign Key 是一个表中的列，与另一表中的主键匹配，使这两张表能够连接在一起。

In SQL, a Foreign Key is a column in one table that matches a Primary Key in another table, allowing the two tables to be connected together.

外键还保持两张表之间的引用完整性，使之不可能删除包含主键的表（保留两表之间的连接）。

A foreign key also maintains referential integrity between two tables, making it impossible to drop the table containing the primary key (preserving the connection between the tables).

我们考虑一个示例场景，假设我们有两个表，即 CUSTOMERS（ID、NAME、AGE、ADDRES、SALARY）和 ORDERS（ID、DATE、CUSTOMER_ID、AMOUNT）。此处，客户的 id 是 CUSTOMERS 表中的主键（ID）和 ORDERS（CUSTOMER_ID）表中的外键，请观察以下图表：

Let’s consider an example scenario, assume we have two tables namely CUSTOMERS (ID, NAME, AGE, ADDRES, SALARY) and ORDERS (ID, DATE, CUSTOMER_ID, AMOUNT). Here the id of the customer is primary key (ID) in the CUSTOMERS table and foreign key in the ORDERS (CUSTOMER_ID) table observe the following diagram −

foreign

Features of Foreign Key

以下是外键的特性：

Following is the of features of Foreign Key −

  1. A Foreign Key is used to reduce the redundancy (or duplicates) in the table.

  2. It helps to normalize (or organize the data in a database) the data in multiple tables.

Syntax

以下是 MySQL 数据库中关于在表的一列上添加外键约束的基本语法：

Following is the basic syntax to add Foreign Key constraints on a column of a table in MySQL database −

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...
    CONSTRAINT fk_name
	FOREIGN KEY (column_name)
	REFERENCES referenced_table(referenced_column)
);

Example

让我们使用 CUSTOMERS 和 ORDERS 创建两个表。以下查询创建一个名为 CUSTOMERS 的表：

Let us create two tables with the names CUSTOMERS and ORDERS. The following query creates a table with the name CUSTOMERS −

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25) ,
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Output

以下是上述 SQL 语句的输出：

Following is the output of the above SQL statement −

Query OK, 0 rows affected (0.02 sec)

现在，让我们创建一个名为 ORDERS 的表。这样做的同时，我们会在列 CUSTOMER_ID 上添加外键约束，该约束引用 CUSTOMERS 表中 ID 列，如下面语句所示：

Now, let us create the ORDERS table. While doing so, we add the foreign key constraint on column CUSTOMER_ID reference on column ID of the CUSTOMERS table as shown in the statement below −

CREATE TABLE ORDERS (
   ID INT NOT NULL,
   DATE DATETIME,
   CUSTOMER_ID INT,
   CONSTRAINT FK_CUSTOMER
   FOREIGN KEY(CUSTOMER_ID)
   REFERENCES CUSTOMERS(ID),
   AMOUNT DECIMAL,
   PRIMARY KEY (ID)
);

Output

以上语句会产生以下输出：

The above statement produces the following output −

Query OK, 0 rows affected (0.04 sec)

Verification

我们已经对 ORDERS 表中名为 CUSTOMER_ID 的列创建了一个外键约束，它引用了 CUSTOMERS 表中的名为 ID 的列；因此，你不能在删除表 2 (ORDERS) 之前删除表 1 (CUSTOMERS)。

We have created a Foreign Key Constraint on a column named CUSTOMER_ID in the ORDERS table that references the column named ID of the CUSTOMERS table; so you can’t drop table1 (CUSTOMERS) before dropping the table2 (ORDERS).

首先，让我们尝试不删除 ORDERS 表的情况下使用 DROP TABLE 语句删除 CUSTOMERS 表：

First of all, let’s drop the CUSTOMERS table without dropping the ORDERS table using the DROP TABLE statement −

DROP TABLE CUSTOMERS;

如果你验证以下错误消息，你会发现该消息指出，由于外键约束的引用，该表不可删除：

If you verify the error message below, you will observe that it says that the table can not be dropped because it is referenced by a FOREIGN KEY constraint −

ERROR 3730 (HY000): Cannot drop table 'customers' referenced by a foreign key constraint 'FK_CUSTOMER' on table 'orders'.

Foreign Key Constraint on an Existing Column

我们还可以在现有表的列上创建一个外键约束。当你忘记在创建表时在外键约束上添加一个列时，或者当你希望在表中即使存在一个外键列的情况下仍向另一个列添加此约束时，这样做非常有用。

We can also create a Foreign key constraint on a column of an existing table. This is useful when you forget to add a Foreign Key constraint on a column while creating a table, or when you want to add this constraint on another column even if one Foreign Key column exists in a table.

Syntax

使用 ALTER TABLE 语句，我们可以在 MySQL 数据库中现有表的列上添加外键约束，如下所示：

Using the ALTER TABLE statement we can add a Foreign Key constraint on an existing column in a table in MySQL database as shown below −

ALTER TABLE TABLE2
ADD CONSTRAINT[symbol]
FOREIGN KEY(column_name)
REFERENCES TABLE1(column_name);

在这里，FK_ORDERS 是外键约束的名称。指定约束的名称是可选的，但在删除约束时它派上用场。

Here, FK_ORDERS is the name of the foreign key constraint. It is optional to specify the name of a constraint but it comes in handy while dropping the constraint.

Example

假设已经在 SQL 数据库中创建了 CUSTOMERS 表和 ORDERS 表。现在，我们将在 ORDERS 表的 ID 列上添加一个外键约束。

Assume the CUSTOMERS and ORDERS tables have already been created in the SQL database. Now, we will add a Foreign Key Constraint on the ID column of the ORDERS table.

以下是向现有表的列上添加外键约束的 SQL 查询：

Following is the SQL query to add the foreign key constraint on an the column of an existing table −

ALTER TABLE ORDERS
ADD CONSTRAINT FK_ORDERS
FOREIGN KEY(ID)
REFERENCES CUSTOMERS(ID);

Output

以下是以上程序的输出 −

Following is the output of the above program −

Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

我们已经对 ORDERS 表中名为 CUSTOMER_ID 的列创建了一个外键约束，它引用了 CUSTOMERS 表的列名 ID。因此，你不能在删除 ORDERS 表 (ORDERS) 之前删除表 1 (CUSTOMERS)。

We have created a Foreign Key Constraint on a column named CUSTOMER_ID in the ORDERS table that references the column name ID of the CUSTOMERS table. So, you can’t drop table1 (CUSTOMERS) before dropping the table2 (ORDERS).

首先，让我们在不删除 ORDERS 表的情况下删除 CUSTOMERS 表，方法是执行以下语句：

First of all, let us drop the CUSTOMERS table without dropping the ORDERS table by executing the following statement −

DROP TABLE CUSTOMERS;

这会生成一条错误消息，指出该表不可删除，因为它被一个 FOREIGN KEY 约束所引用：

This generates an error message saying that the table can not be dropped because it is referenced by a FOREIGN KEY constraint −

ERROR 3730 (HY000): Cannot drop table 'customers' referenced by a foreign key constraint 'FK_CUSTOMER' on table 'orders'.

Dropping a FOREIGN KEY

你可以使用 ALTER TABLE 语句删除表中的外键，而无需删除整个表。

You can drop the foreign key from a table, without dropping that entire table, using the ALTER TABLE statement.

Syntax

以下是使用 ALTER TABLE 语句删除表列的外键约束的语法：

Following is the syntax to drop the FOREIGN key constraint from the column of the table using the ALTER TABLE statement−

ALTER TABLE table_name DROP FOREIGN KEY (constraint symbol);

其中，FK_NAME 是需要删除的外键约束的名称。

Where, FK_NAME is the name of the foreign key constraint you need to drop.

Example

删除表中列的外键约束的 SQL 查询为：

The SQL query to drop the foreign key constraint from the column of a table is as follows −

ALTER TABLE ORDERS DROP FOREIGN KEY FK_ORDERS;

Output

以下是上述 SQL 查询的输出：

Following is the output of the above SQL query −

Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

由于我们已经删除了 ORDERS 表中的外键约束，因此你现在可以直接删除 CUSTOMERS 表，而无需删除 ORDERS 表，如下所示：

Since we have dropped the Foreign key constraint from the ORDERS table, you can now directly drop the CUSTOMERS table without dropping the ORDERS table, as shown below −

DROP TABLE CUSTOMERS;

如果你验证上述 SQL 命令抛出的以下状态码，你会发现 CUSTOMERS 表已被删除。

If you verify the below status code thrown by the above SQL command, you observe that the CUSTOMERS table has dropped.

Query OK, 0 rows affected (0.02 sec)

Primary Key vs Foreign Key

即使主鍵和外鍵都引用同一列，它們在工作方式上仍有許多不同之處。它們列舉如下：

Even though both the primary key and foreign key refer to the same column, there are many differences to be observed in the way they work. They are listed below −

Primary Key

Foreign Key

The primary key is always unique.

The foreign key can be duplicated.

The primary key can not be NULL.

The Foreign can be NULL.

A table can contain only one Primary Key.

We can have more than one Foreign Key per table.