了解 SQL 约束

介绍

设计数据库时,可能有时要限制在某些列中允许的数据,例如,如果您正在创建一个包含云层信息的表,则可能需要将每个建筑物的高度的列禁止负值。

关系数据库管理系统(RDBMS)允许您控制使用 constraints 将哪些数据添加到表中. 限制是适用于一个或多个列或整个表的特殊规则,该规则限制了可以对表中的数据进行哪些更改,无论是通过输入,更新或删除声明。

本文将详细审查什么是限制,以及它们如何在RDBMS中使用,它还将通过SQL标准中定义的五个限制,并解释它们的各自功能。

什么是限制?

在 SQL 中,限制是适用于列或表的任何规则,限制可以输入哪些数据. 每当您尝试执行改变表中的数据的操作(如输入,更新或删除)时,RDBMS 将测试该数据是否违反任何现有限制,并在这种情况下返回错误。

数据库管理员通常会依赖限制来确保数据库遵循一组定义的 _business 规则。在数据库的背景下,商业规则是企业或其他组织遵循的任何策略或程序,其数据也必须遵守。

数据完整性是一个广泛的术语,通常用于描述数据库中数据的总体准确性、一致性和合理性,基于其特定用例。数据库中的表往往是密切相关的,一个表中的列取决于另一个表的值。

假设您正在设计一个数据库,其中包含两个表:一个用于列出学校的当前学生,另一个用于列出该学校的篮球队的成员。

用户在首次创建表时定义限制,或者他们可以稍后使用ALTER TABLE语句添加限制,只要它不与表中已经存在的任何数据发生冲突。

SQL 标准正式定义了五个限制:

• PRIMARY KEY
• FOREIGN KEY
• UNIQUE
• CHECK
• NOT NULL

<$>[注] **注:许多RDBMS中包含DEFAULT关键字,它用于定义一个非NULL列的默认值,如果在插入行时没有指定任何值。这些数据库管理系统中的某些文档将DEFAULT作为限制,因为他们的SQL实现使用类似于UNIQUE或CHECK等限制的DEFAULT语法。

现在你对限制是如何使用的一般理解,让我们仔细看看这五个限制。

首要钥匙

PRIMARY KEY限制要求该列中的每个条目都是唯一的,而不是NULL,并允许您使用该列来识别表中的每个单独行

在关系模型中,一个 key 是表中的一个列或一组列,其中每个值被保证是独一无二的,并且不包含任何NULL值。

这是关系数据库的一个重要方面:使用主密钥,用户不需要知道他们的数据在机器上物理存储在何处,他们的DBMS可以跟踪每个记录,并根据具体情况返回它们,这反过来意味着记录没有定义的逻辑顺序,用户可以以任何顺序或通过任何过滤器返回数据。

在 SQL 中,您可以使用PRIMARY KEY限制来创建一个主要密钥,这基本上是UNIQUE和NOT NULL限制的组合。定义一个主要密钥后,DBMS 会自动创建与其相关的 index。索引是一种数据库结构,有助于更快地从表中获取数据。类似于教科书中的索引,查询只需要检查从索引列的条目才能找到相关的值。

一个表只能有一个主要密钥,但像常规密钥一样,一个主要密钥可以包含多个列,其中一个主要密钥的定义特征是,它们只使用最少的属性组合,以便在表中单独识别每个行。

• studentID:用来保留每个学生的唯一识别号码
• firstName:用来保留每个学生的姓名
• lastName:用来保留每个学生的姓名

学校的一些学生可能会共享姓名,使FirstName列成为主要密钥的糟糕选择。对于LastName列也是如此。

由学生ID和第一名或最后名列组成的主要密钥可能起作用,但由于每个学生的识别号码已经被称为独一无二,包括主密钥中的任何一个名称列将是多余的,因此,在这种情况下,可以识别每个行的最小属性集合,从而成为表的主要密钥的良好选择,只是学生ID列本身。

如果密钥由可观察的应用数据(即代表真实世界实体、事件或属性的数据)组成,则称为自然密钥。如果密钥是内部生成的,并且不代表数据库之外的任何东西,则称为替代密钥或合成密钥。

《外國鑰匙》

对外密钥的约束要求给定列中的每个条目必须已经存在于另一个表的特定列中。

如果你有两个表,你想彼此关联,你可以这样做的一种方式是通过定义一个外部密钥与外部密钥的约束。 foreign key 是一个表(孩子表)中的一个列,其值来自另一个表(父母)中的一个密钥。

下图凸显了两个表之间的这种关系:一个用于记录公司员工的信息,另一个用于跟踪公司的销售额。

如果您尝试将记录添加到子表中,而输入到外部密钥列中的值不存在于母表的主要密钥中,插入声明将无效,这有助于保持关系级别的完整性,因为两个表中的行将始终正确相关。

通常情况下,表的外部密钥是母级表的主要密钥,但并非总是如此. 在大多数 RDBMS 中,母级表中的任何具有Unique或PRIMARY KEY限制的列都可以被子级表的外部密钥引用。

独一无二

限制独一无二禁止将任何重复值添加到给定列中。

正如其名称所暗示的那样,一个UNIQUE限制要求给定的列中的每个输入都具有独特的值,任何尝试添加在列中已经出现的值都会导致错误。

「UNIQUE」限制有助于在表之间强制执行 _one-to-one 关系. 如前所述,您可以使用外部密钥在两个表之间建立关系,但可以在表之间存在多种类型的关系:

• one-to-one: 如果母级表中的行与母级表中的一个和只有一个行有关,则说两张表具有一对一的关系
• one-to-many:在多对任何关系中,母级表中的一个行可以与母级表中的多个行有关,但母级表中的每个行只能与母级表中的一个行有关
• many-to-many:如果母级表中的行可以与母级表中的多个行有关,反之亦然,则说两行中只有母级表中的一个行

通过将独一无二约束添加到已应用外钥约束的列中,您可以确保母表中的每个条目在孩子中只出现一次,从而在两个表之间建立一个对一关系。

请注意,您可以在表层和列层定义UNIQUE限制。当在表层定义时,UNIQUE限制可能适用于多个列。

检查

一个检查约束定义了对一个列的要求,称为 predicate,每个输入的值都必须满足。

CHECK约束序列以一种可以评估为TRUE、FALSE或潜在地为UNKNOWN的表达式来编写。如果您尝试在一个具有CHECK约束的列中输入一个值,并且该值会导致序列评估为TRUE或UNKNOWN(对于NULL值来说是这样的),则该操作将成功。

CHECK预测通常依赖于数学比较运算符(如<,>,<=,或>=)来限制允许进入给定列的数据范围,例如,CHECK限制的一个常见用途是防止某些列在负值没有意义的情况下持有负值,如下面的示例。

此CREATE TABLE声明创建了一个名为productInfo的表,其中包含每个产品名称、标识号码和价格的列,因为一个产品有负价是没有意义的,所以这个声明会对价格列施加一个CHECK限制,以确保它只包含正值:

1CREATE TABLE productInfo (
2productID int,
3name varchar(30),
4price decimal(4,2)
5CHECK (price > 0)
6);

並非每個「CHECK」序列都必須使用數學比較運算器。 通常,您可以使用任何能在「CHECK」序列中評估為「TRUE」、「FALSE」或「UNKNOWN」的 SQL 運算器,包括「LIKE」、「BETWEEN」、「IS NOT NULL」等。 有些 SQL 實施,但並非所有,甚至允許您在「CHECK」序列中包含子查詢。 但是,請注意,大多數實施並不允許您在序列中引用另一個表。

不是零

限制NOT NULL禁止将任何NULL值添加到给定列中。

在大多数 SQL 实现中,如果您添加数据行,但不为特定列指定值,数据库系统将默认地将缺失的数据表示为NULL。在 SQL 中,NULL是一个用于表示未知、缺失或其他未指定值的特殊关键字,但NULL不是一个值本身,而是一个未知值的 _state。

为了说明这种差异,想象一个用于追踪客户的表在一个人才机构,每个客户端的第一名和最后名称的列。如果一个客户端通过一个单词 - 如Cher,Usher或Beyoncé - 数据库管理员可能只输入单词在第一名列,导致DBMS在姓名列中输入NULL。

正如其名称所暗示的那样,NOT NULL限制阻止给定列中的任何值成为NULL。这意味着对于任何具有NOT NULL限制的列,您必须在插入新行时为其指定值,否则INSERT操作将失败。

结论

限制是任何想要设计数据库具有高数据完整性和安全性的必不可少的工具. 通过限制列中输入的数据,您可以确保表之间的关系得到正确维护,并且数据库遵守定义其目的的业务规则。

有关如何创建和管理 SQL 限制的详细信息,您可以查看我们的指南 如何在 SQL 中使用限制。