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 表。这样做时,我们在 CUSTOMERS 表的列 ID 上的列 CUSTOMER_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(订单)之前删除table1(客户)。

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

DROP TABLE CUSTOMERS; 

如果您验证下面的错误消息,您会发现它表明该表无法删除,因为它被 FOREIGN KEY 约束引用 -

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。因此,您不能在删除 table2 (ORDERS) 之前删除 table1 (CUSTOMERS)。

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

DROP TABLE CUSTOMERS;

这会生成一条错误消息,指出该表无法删除,因为它被 FOREIGN KEY 约束引用 -

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)

主键与外键

尽管主键和外键都引用同一列,但它们的工作方式仍存在许多差异。它们列在下面 -

首要的关键 外键
主键始终是唯一的。 外键可以重复。
主键不能为 NULL。 外部可以为 NULL。
一张表只能包含一个主键。 每个表可以有多个外键。