如何在SQL中创建外键约束

如果您拥有一家企业，您一定会遇到数据在业务中的价值和需求。拥有存储和操作数据库的手段为业务增加了更多的价值。

数据库按照特定的约定进行组织，并允许您以连接的方式结构化数据，这使得关系数据库成为自20世纪70年代以来的一种数据管理形式。在今天的市场上，关系数据库因其在操作数据时的能力而受到青睐。

虽然市场上有很多可用的关系数据库，但mysql已经成为领先的数据库，根据statista的数据，截至2022年1月，在全球排名第二。

在sql服务器中，约束是预定义的规则和限制，强制应用于单个或多个列；它们与列中的值绑定，并有助于维护指定列数据的完整性、准确性和可靠性。

简单来说，只有符合约束规则的数据才能成功插入到列中。如果数据不符合条件，则插入操作会被终止。

本文假设您已经接触过关系数据库，特别是mysql，并希望加强对该领域的知识。最后，我将分享一些与外键约束交互的技巧。

主键约束 – 回顾

在sql中，表包含一个或多个包含键值的列，精确指向系统中的每一行。表的主键（pk）列具有强制执行表的实体完整性的作用。主键约束确保数据的唯一性，通常在标识列上定义。

在为表指定主键约束后，数据库引擎会自动为每个主要列生成唯一索引，从而实施数据的唯一性。主键在查询中使用时提供了快速的数据访问。

如果在多个列上定义了主键约束，则称为复合主键。在这种情况下，每个主键列都可以包含重复的值。然而，主键中所有列的组合值必须是唯一的。

一个很好的例子是，假设您有一个包含列`id`、`names`和`age`的表。当您将其主键约束定义在`id`和`names`的组合上时，可以有重复的`id`或`names`值的实例。但是，每个组合必须是唯一的，以避免重复行。因此，您可以有符合条件的记录`id=1`和`name=walter`，`age=22`和`id=1`，`name=henry`和`age=27`，但是您不能再有其他记录`id=1`和`name=walter`，因为组合不唯一。

以下是一些需要知道的重要方面：

1. 一个表只能包含一个主键约束。
2. 主键不能超过16个列，最大长度为900个字符。
3. 由主键生成的索引可以增加表中的索引。然而，一个表上的聚集索引数量不能超过1个，非聚集索引数量限制为999个。
4. 当未指定聚集和非聚集的键约束时，自动采用聚集。
5. 在主键约束中声明的所有列都应定义为非空。如果不是这种情况，约束中链接的所有列的可空性都会自动设置为非空。
6. 当主键定义在公共语言运行时（clr）用户定义的列类型上时，类型实现必须支持二进制排序。

外键约束 – 一览

外键（fk）是一个列或多个列的组合，用于创建和绑定两个表之间的链接，并管理要存储在外键表中的数据。

外键引用是在两个表之间创建链接的情况，当另一个表的主键列引用了不同表中的列或列时。

在外键引用场景中，当一个表的主键列或列被另一个表中的列引用时，就会创建两个表之间的连接。

在实际的使用情况中，您可以有一个名为sales.salesorderheader的表，它有一个外键链接到另一个表sales.person，因为销售人员和销售订单之间存在逻辑关系。

在这里，salesorderheader中的salespersonid与salesperson表的主键列相匹配。这个salesperson表的外键是salesorderheader中的salespersonid列。

这种关系定义了一个规则：如果salespersonid 在salesperson表中不存在，则不能将其放入salesorderheader表中。

一个表可以引用多达253个其他列和表作为外键，也称为外部引用。自2016年起，sql server将在单个表中引用的表和列的数量从253增加到10000。然而，这个增加是有一些限制的：

1. 超过253个外键引用仅适用于delete dml操作。merge和update不支持。
2. 具有对自身的外键引用的表最多可以有253个外键引用。
3. 对于列存储索引、内存优化表和分区外键表，外键引用限制为253个。

外键的好处是什么？

如前所述，外键约束在保护关系数据库的完整性和数据一致性方面起着重要作用。以下是外键约束至关重要的原因。

1. 引用完整性 – 外键约束保证每个子表记录对应一个主表记录，确保两个表之间的数据一致性。
2. 防止孤立记录 – 如果删除父表，外键约束确保关联的子表也会被删除，防止孤立记录的发生，从而保持数据一致性。
3. 提高性能 – 外键约束通过允许数据库管理系统优化基于表关系的查询来提高查询性能。

外键约束的索引

外键约束不会自动创建与主键约束类似的索引。您可以为外键约束手动创建索引，原因如下。

• 在查询中，外键列通常用于连接相关表的条件，通过匹配与约束相关联的列来合并相关表中的数据。索引帮助数据库在外部表中查找相关数据。
• 如果更改主键约束，则会将其与相关表中的外键约束一起检查。

创建索引并不是强制性的。您仍然可以在不指定主键和外键约束的情况下从两个表中组合数据。然而，添加外键约束可以优化表并将它们组合在一个满足使用键的查询中的查询中。如果更改主键约束，则会将其与相关的外键约束一起检查。

在sql中创建外键约束的提示

您已经花了很多时间进行推测，回答了为什么的问题。现在让我们将注意力转移到创建外键约束的策略上，回答如何的问题。

表中的“外键”字段指的是另一个表的“主键”。具有主键的表是您的父表。而具有外键的表称为子表。让我们深入了解。

在创建表时创建外键

在创建表时，您还可以创建一个外键约束来维护引用完整性。下面是如何实现：

create table orders (
    order_id int primary key,
    customer_id int,
    order_date date,
    foreign key (customer_id) references customers(customer_id)
);

上述代码创建了一个名为“orders”的表，其中包含主整数键“order_id”，另一个整数“customer_id”和日期“order_date”。在此情况下，外键约束被添加到“customer_id”列，并引用了“customers”表中的“customer_id”。

在创建表后创建外键

假设您已经创建了一个表并希望添加外键约束，请在代码中使用“alter table”语句。请参阅下面的代码片段。

alter table orders
add foreign key (customer_id) references customers(customer_id);

在这种情况下，您已经在“orders”表中的“customer_id”列中添加了一个外键约束，该约束引用了“customers”表中的“customer_id”列。

在不检查现有数据的情况下创建外键

当你向表中添加外键约束时，数据库会自动检查现有数据以确保与约束的一致性。然而，如果你知道数据是一致的，并且想要添加一个不进行一致性检查的约束，下面是如何操作。

alter table orders
add constraint fk_orders_customers
foreign key (customer_id)
references customers(customer_id)
not validate;

not validate命令告诉数据库不要检查现有数据。这种特定情况在特定的实例中非常有用。例如，当你有大量数据并希望完成验证过程时。

通过delete/update创建外键

在创建外键约束时，你可以指定在引用的行被更新或删除时所采取的操作。在这种情况下，你可以使用级联参照完整性约束来指定要采取的操作。它们包括：

#1. no action

与许多其他数据库一样，'no action'规则是创建外键约束时的默认行为。这意味着在引用的行被删除或更新时不采取任何操作。

如果违反了外键约束，数据库引擎会引发错误。然而，这并不推荐，因为它可能导致参照完整性问题，需要强制执行外键约束。下面是一个示例：

alter table orders
add constraint fk_orders_customers
foreign key (customer_id)
references customers(customer_id)
on delete no action
on update no action;

#2. cascade

当创建外键约束时，'cascade'规则是'on delete'和'on update'操作的另一个选项。当使用这个规则时，意味着当父表中的行被更新或删除时，引用的行也会相应地被更新或删除。这种技术在维护参照完整性时非常有用。下面是一个示例：

alter table orders
add constraint fk_orders_customers
foreign key (customer_id)
references customers(customer_id)
on delete cascade
on update cascade;

使用这个规则时要小心，因为如果不小心使用会导致不良后果。你应该避免意外删除过多的数据或创建循环引用。因此，只有在必要时并且谨慎使用这个选项。

关于使用cascade有一些规则：

• 如果时间戳列是外键或被引用键的一部分，你不能指定cascade。
• 如果表中有instead of delete触发器，你不能指定on delete cascade。
• 如果表中有instead of update触发器，你不能指定on update cascade。

#3. set null

当你在父表中删除或更新相应的行时，构成外键的所有值都设置为null。这个约束规则要求外键列可为空以执行，并且不能为具有instead of update触发器的表指定。下面是一个示例：

在这种情况下，如果“customers”表中的相应行被删除或更新，您已将“orders”表中的外键列“customer_id”设置为空。

#4. set default

在这里，您设置了所有使外键默认的值，前提是父表中的引用行已被更新或删除。

如果所有外键列都具有默认定义，则执行此约束。如果某列可为空，则其默认值设置为null。请注意，此选项不能用于具有instead of update触发器的表。以下是一个示例：

在上面的例子中，当“customers”表中的相应行被删除或更新时，您将“orders”表中的“customer_id”设置为其默认值。

最后的话

在本指南中，您复习了主键约束并深入研究了外键约束。您还遇到了创建外键约束的几种技术。虽然有很多创建外键约束的方法，但本文揭示了其中的方法。

希望您已经掌握了新的技巧；您并不局限于将它们结合在一起。例如，cascade、set null、set default和no action约束方法可以在具有引用关系的表上组合使用。

如果您的表遇到no action，则返回其他约束规则。在其他情况下，delete操作可能会触发这些规则的组合，并且no action规则将作为最后一个运行。

接下来，请查看sql备忘录。