SQL - 外键

---

---

SQL 外键

在 SQL 中，外键是表中的一列，它与另一表中的主键匹配，从而允许将这两个表连接在一起。

外键还维护两个表之间的参照完整性，使得无法删除包含主键的表（保留表之间的连接）。

外键可以引用数据库中任何表的唯一字段。具有主键的表称为父表，具有外键的表称为子表。

让我们考虑一个示例场景，假设我们有两个表，名为 CUSTOMERS (ID, NAME, AGE, ADDRES, SALARY) 和 ORDERS (ID, DATE, CUSTOMER_ID, AMOUNT)。这里客户的 ID 是 CUSTOMERS 表中的主键 (ID)，也是 ORDERS (CUSTOMER_ID) 表中的外键，请观察下图：

外键的特性

以下是外键的特性：

• 外键用于减少表中的冗余（或重复）。

• 它有助于规范化（或组织数据库中的数据）多个表中的数据。

语法

以下是为 MySQL 数据库中表的列添加外键约束的基本语法：

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

示例

让我们创建两个名为 CUSTOMERS 和 ORDERS 的表。以下查询创建一个名为 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)
);

输出

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

Query OK, 0 rows affected (0.02 sec)

现在，让我们创建 ORDERS 表。在此过程中，我们在 CUSTOMER_ID 列上添加外键约束，引用 CUSTOMERS 表的 ID 列，如下面的语句所示：

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)
);

输出

上述语句产生以下输出：

Query OK, 0 rows affected (0.04 sec)

验证

我们在 ORDERS 表的 CUSTOMER_ID 列上创建了一个外键约束，该约束引用了 CUSTOMERS 表的 ID 列；因此，在删除 table2 (ORDERS) 之前，不能删除 table1 (CUSTOMERS)。

首先，让我们使用 DROP TABLE 语句删除 CUSTOMERS 表而不删除 ORDERS 表：

DROP TABLE CUSTOMERS; 

如果您验证下面的错误消息，您会发现它指出该表无法删除，因为它被外键约束引用。

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

现有列上的外键约束

我们还可以为现有表的列创建外键约束。当您在创建表时忘记为列添加外键约束，或者即使表中存在一个外键列，也希望在此列上添加此约束时，这很有用。

语法

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

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

这里，FK_ORDERS 是外键约束的名称。指定约束名称是可选的，但在删除约束时非常方便。

示例

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

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

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

输出

以下是上述程序的输出：

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

验证

我们在 ORDERS 表的 CUSTOMER_ID 列上创建了一个外键约束，该约束引用了 CUSTOMERS 表的 ID 列。因此，在删除 table2 (ORDERS) 之前，不能删除 table1 (CUSTOMERS)。

首先，让我们通过执行以下语句删除 CUSTOMERS 表而不删除 ORDERS 表：

DROP TABLE CUSTOMERS;

这会生成一条错误消息，指出该表无法删除，因为它被外键约束引用。

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

删除外键

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

语法

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

ALTER TABLE table_name DROP FOREIGN KEY (constraint symbol);

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

示例

删除表列外键约束的 SQL 查询如下：

ALTER TABLE ORDERS DROP FOREIGN KEY FK_ORDERS;

输出

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

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

验证

由于我们已从 ORDERS 表中删除了外键约束，因此您现在可以直接删除 CUSTOMERS 表而不删除 ORDERS 表，如下所示：

DROP TABLE CUSTOMERS;

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

Query OK, 0 rows affected (0.02 sec)

主键与外键

即使主键和外键都引用同一列，它们的工作方式也存在许多差异。这些差异列在下面：