SQL 外键

在SQL中， 外键 是一张表中的一列，它与另一张表中的主键匹配，从而使这两张表可以连接在一起。

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

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

让我们考虑一个示例场景，假设我们有两张表，分别是CUSTOMERS（ID，NAME，AGE，ADDRES，SALARY）和ORDERS（ID，DATE，CUSTOMER_ID，AMOUNT）。在这里，CUSTOMERS表中的客户ID是主键（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列；因此，在删除表2（ORDERS）之前，您不能删除表1（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是外键约束的名称。指定约束的名称是可选的，但在删除约束时非常有用。

示例

假设在SQL数据库中已经创建了CUSTOMERS和ORDERS表。现在，我们将在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。因此，在删除表2（ORDERS）之前，无法删除表1（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语句从表的列中删除FOREIGN键约束的语法

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)

主键 vs 外键

尽管主键和外键都引用同一列，但它们的工作方式存在许多差异。以下是它们的主要区别：