Firebird 和 InterBase 中的数据库约束

_NOTICE: 本文档是阿列克谢·科维亚津和谢尔盖·沃斯特里科夫所著《InterBase 世界》一书中的一章。

本章专门讨论 InterBase 和 Firebird 数据库的约束。数据库约束是定义表之间相互关系并可以检查和修改数据库中数据的规则。这些规则以特殊的数据库对象形式实现。使用约束的主要优势在于能够在数据库层面实现数据检查以及应用程序的部分业务逻辑，即集中并简化它，从而使数据库应用程序的开发更加容易和可靠。

初学开发者常常忽视使用数据库约束，认为它们妨碍创造性工作。然而，实际上这种观点源于对数据库设计的理论和实践了解不足。

与此同时，最有经验的设计师敢于放弃使用某些类型的约束，因为他们的应用程序在速度上因此受益。专家设计师的经验使他们能够很好地理解服务器的工作，并精确预测其在复杂情况下的行为，因此对于 InterBase 初学者程序员来说，最好不要效仿经验丰富的同事的类似做法。

在本书中我们不讨论数据库设计，因此有关此问题的更多信息，请参阅书末的文献列表。这里我们只回顾 InterBase 数据库中所有类型的约束，并考虑它们的应用示例。

数据库中的约束类型

InterBase 数据库中有以下类型的约束：

PRIMARY KEY；
UNIQUE KEY；
FOREIGN KEY

- 可以启用自动触发器 - ON UPDATE 和 ON DELETE；

CHECK

在前面的章节中，我们提到过其中一些约束，因为这对于材料的逻辑呈现是必要的，但现在我们将更详细地考虑它们的语法、应用和实现。数据库约束有两种类型–基于单个字段的和基于表中多个字段的。两种类型约束的语法如下所示。

= [CONSTRAINT constraint]

[ …]

= {UNIQUE | PRIMARY KEY

| CHECK ( )

| REFERENCES other_table [( other_col [, other_col …])]

[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

}

基于多个字段的约束语法如下：

= [CONSTRAINT constraint]

[< tconstraint> …]

= {{PRIMARY KEY | UNIQUE} ( col [, col …])

| FOREIGN KEY ( col [, col …]) REFERENCES other_table[( other_col [, other_col …])]

[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

| CHECK ( )}

基于单个字段和基于多个字段的约束语法之间的差异是显而易见的–在后者中，我们可以指定包含在约束中的多个字段。在基于单个字段的约束情况下，所有描述的选项仅与当前字段相关。当然，这两种类型的约束有不同的应用方式：基于单个字段的约束只是简单地添加到所需字段的定义中，而基于多个字段的约束则在表的总体定义中逗号后指定。详细示例在本章的以下部分给出。

典型约束示例

实际上，基于单个字段的约束是基于多个字段的约束的特例。

下面给出了使用这两种不同方法创建主键约束的示例。让我们创建一个只包含一个字段的表，并为其设置主键约束。

以下是使用基于单个字段的约束语法创建主键的示例：

CREATE TABLE test1( ID_PK INTEGER CONSTRAINT pktest NOT NULL PRIMARY KEY); 在此示例中，为 ID_PK 字段创建了一个名为 pktest 的主键。结果，我们得到了一行相当紧凑的描述。我们可以使用基于多个字段的约束语法达到相同目的：CREATE TABLE test2( ID_PK INTEGER NOT NULL, CONSTRAINT pktst PRIMARY KEY (ID_PK));

创建约束

让我们更详细地考虑约束的创建。在通用约束语法描述中，首先是 [CONSTRAINT constraint] 选项。如您所见，此选项放在方括号中，即它是可选的。

使用此选项，您可以为创建的约束设置名称，无论是应用基于单个字段的约束语法还是基于多个字段的约束语法。如果您没有为约束指定名称，InterBase 将自动生成它。尽管如此，最好为创建的约束设置名称，以提高数据库模式的可读性并简化后续的约束管理。

设置约束名称后，应定义其类型。让我们按照通用约束语法描述中指出的顺序考虑不同类型的约束。

主键和唯一键

主键是数据库约束的主要类型之一。它们用于表中记录的单值标识。假设我们在数据库中存储人员列表。很可能会有两个（或更多）具有相同姓氏、名字和父名的人。我们如何区分一个人与另一个人（当然，问题是如何根据数据库中存储的信息区分一个人与另一个人）？

在这种情况下，“人”由表中的一条记录表示，因此我们可以提出一个更一般的问题–我们如何区分（任何）表中的一条记录与同一表中的另一条记录。为此，使用约束–主键。主键表示表中的一个或几个字段，其组合对每条记录是唯一的。一个表的主键没有重复值。

唯一键执行相同的功能–它们也用于表中记录的单值标识。主键和唯一键之间的区别在于表中只能有一个主键，而唯一键可以有多个。需要注意的是，主键和唯一键都可以用作外键的引用基础（见下文）。

主键和唯一键概念的正式描述，以及其他重要定义，可以在书末的“术语表”附录中找到。基于唯一字段创建主键和唯一键的语法如下：

< pkukconstraint > = [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE}

主键和唯一键的示例：

CREATE TABLE pkuk( pk NUMERIC(15,0) NOT NULL PRIMARY KEY, /*主键*/

uk1 VARCHAR(50) NOT NULL UNIQUE,/*唯一键*/

uk2 INTEGER NOT NULL UNIQUE /\* 另一个唯一键 */);

基于多个字段创建主键和唯一键的语法：

= [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE} ( col [, col …])

这种语法允许基于字段组合创建键。以下是基于多个字段创建主键和唯一键的示例：

CREATE TABLE pkuk2( Number1 INTEGER NOT NULL, Name1 VARCHAR(50) NOT NULL, Kol INTEGER NOT NULL, Stoim NUMERIC(15,4) NOT NULL, CONSTRAINT pkt PRIMARY KEY (Number1, Name1), /*基于两个字段的主键 pkt*/ CONSTRAINT ukt1 UNIQUE (kol, Stoim)); /*基于两个字段的唯一键 ukt1*/

请注意，包含在主键和唯一键中的所有字段都应声明为 NOT NULL，因为这些键不能有未定义的值。除了在创建表时创建主键和唯一键约束外，还有能力向已存在的表添加约束。在这种情况下，使用 DDL：ALTER TABLE 语句。向现有表添加主键或唯一键约束的语法与上述类似：

ALTER TABLE tablename ADD [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE} ( col [, col …])

让我们通过一个使用ALTER TABLE创建主键和唯一键的示例来理解：

CREATE TABLE pkalter( ID1 INTEGER NOT NULL, ID2 INTEGER NOT NULL, UID VARCHAR(24));

然后我们添加键。先添加主键：

ALTER TABLE pkalter ADD CONSTRAINT pkal1 PRIMARY KEY (id1, id2);

然后添加唯一键：ALTER TABLE pkalter ADD CONSTRAINT ukal UNIQUE (uid);

需要指出的是，只有该表的所有者或系统管理员SYSDBA（关于所有者和SYSDBA用户的更多详细信息，请参见“InterBase中的安全性：用户、其功能和权限”一章–第4部分）才能执行向表中添加（以及删除）主键和唯一键的操作。

外键

InterBase数据库中另一个常用的约束是外键约束。这是一个非常强大的工具，用于提供数据库中的引用完整性，它不仅允许监督数据库中正确引用的存在，还可以自动控制这些引用！

创建外键的意义如下：如果两个表用于存储相互关联的信息，则有必要保证这种关联始终是正确的。例如，文档“运单”包含总标题（日期、运单号等）和一组详细记录（货物描述、数量等）。

为了存储此类文档，在数据库中创建了两个表–一个用于存储运单标题，第二个用于存储运单内容–关于货物及其数量的记录。这样的表称为主表和从表，或主表和明细表。

根据常识，运单的内容不能在没有其标题的情况下存在。换句话说，如果我们没有创建运单的标题，就不能插入关于货物的记录；如果存在关于货物的记录，就不能删除标题记录。为了实现这种行为，标题表和明细表通过外键约束连接。

让我们通过包含运单信息的表的示例来理解设置外键约束的意义。为此，我们将创建两个表来存储运单–TITLE表用于存储标题，INVENTORY表用于存储运单中包含的货物信息。

CREATE TABLE TITLE( ID_TITLE INTEGER NOT NULL Primary Key, DateNakl DATE, NumNakl INTEGER, NoteNakl VARCHAR(255));

请注意，我们立即在标题表中基于ID_TITLE字段定义了主键。TITLE表的其余字段包含关于运单标题的普通信息–日期、编号、备注。

现在让我们定义用于存储运单中包含的货物信息的表：

CREATE TABLE INVENTORY( ID_INVENTORY INTEGER NOT NULL PRIMARY KEY, FK_TITLE INTEGER NOT NULL, ProductName VARCHAR (255), Kolvo DOUBLE PRECISION, Positio INTEGER);

让我们看看INVENTORY表中包含哪些字段。首先，是ID_INVENTORY–该表的主键。然后是整数字段FK_TITLE，用作对运单标题表中ID_TITLE标题标识符的引用。接下来是ProductName、Kolvo和Positio字段，描述货物的描述、数量以及在运单中的位置。FK_TITLE字段对我们的示例最重要。如果我们想输出某个运单的货物信息，应使用以下查询，其中mas_ID_TITLE参数定义标题标识符：

SELECT * FROM INVENTORY I1 WHERE I1.FK_TITLE=?mas_ID_TITLE

实际上，在上述情况下，没有任何机制阻止向INVENTORY表中填充引用TITLE表中不存在记录的记录。此外，也没有任何机制阻止删除已存在运单的标题，因为这样货物记录可能变成“无主”的。服务器不会禁止执行所有这些插入和删除操作。因此，数据库中数据完整性的控制完全由客户端应用程序负责。然而，您知道多个应用程序（可能由不同的程序员开发）可以同时使用一个数据库，这可能导致不同的数据解释和错误。因此，必须设置显式约束，使得只有具有正确运单标题引用的货物记录才能放入INVENTORY表。这实际上就是一个外键约束，它允许只将那些存在于另一个表中的值插入到包含在约束中的字段中。

可以使用外键创建此类约束。对于给定的示例，我们必须为FK_TITLE字段设置外键约束，并将其与TITLE中的ID_TITLE主键绑定。我们可以通过以下命令向已存在的表添加外键：

ALTER TABLE INVENTORY ADD CONSTRAINT fktitle1 FOREIGN KEY(FK_TITLE) REFERENCES TITLE(ID_TITLE)

通常在添加外键时，会出现错误–对象正在使用中。问题在于，要创建外键，我们必须以独占模式打开数据库–确保同时没有其他用户。此外，我们不应引用正在修改的表–这可能导致“对象正在使用中”错误。

这里INVENTORY是设置外键约束的表的名称；fktitle1是外键的名称；FK_TITLE–构成外键的字段；TITLE是提供值（引用基础）给外键的表的名称；ID_TITLE–TITLE表中作为外键引用基础的主键或唯一键字段。外键约束的完整语法（支持基于多个字段创建约束）如下所示：

= [CONSTRAINT constraint] FOREIGN KEY ( col [, col …]) REFERENCES other_table [( other_col [, other_col …])] [ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}] [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

如您所见，定义中包含大量选项。首先，让我们考虑外键的基本定义，这是实际数据库中最常用的，然后我们将分析可能的选项。

外键约束的声明形式最常用于指定构成约束的字段集（col [, col …]），以及包含外键可能值列表的other_table，其字段为[(other_col [, other_col …])。

以下是在创建表时此类定义的示例：

CREATE TABLE Inventory2( … FK_TABLE INTEGER NOT NULL CONSTRAINT fkinv REFERENCES TITLE(ID_TITLE) …);

请注意，在此定义中省略了FOREIGN KEY关键字，并且隐含使用唯一的字段FK_TITLE作为外键。在创建表的同时创建外键的更完整形式如下例所示：

CREATE TABLE Inventory2( … FK_TABLE INTEGER NOT NULL, CONSTRAINT fkinv FOREIGN KEY (FK_TABLE) REFERENCES TITLE(ID_TITLE) …);

在外键字段中使用NULL

在基于其创建外键的字段中，允许使用NULL字段。此可能性是为了允许相互引用而添加的。例如，如果有两个表通过外键相互引用。如果我们不允许这些外键中存在空引用（即NULL），则无法向连接的表添加任何记录：要向第一个表添加记录，需要在第二个表中已有记录，反之亦然。

使用NULL作为空引用允许创建两个交叉引用表的相互引用，也允许在关系表中存储层次结构–此时根节点引用“空”记录（即仅包含NULL）。

使用外键扩展引用完整性支持的功能

通常，外键约束的声明式变体已经足够，服务器只会监视确保不可能向带有外键的表中插入不正确的值–或者，如果尝试这样做，则会出现错误。但InterBase允许在执行外键的修改/删除操作时执行一组自动操作。为此，使用以下一组外键选项：

[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}] [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

这些选项允许在更新或删除外键值时定义不同的操作。

例如，我们可以设置当删除主表中的主键时，从表中所有具有相同外键的记录也应被删除。在这种情况下，我们必须按以下方式定义外键：

ALTER TABLE INVENTORY ADD CONSTRAINT fkautodel FOREIGN KEY (FK_TITLE) REFERENCES TITLE(ID_TITLE) ON DELETE CASCADE

实际上，为了实现这些操作，存在一个系统触发器，它执行特定的操作。在表1.2中描述了不同选项的操作（请注意，NO ACTION|CASCADE|SET DEFAULT|SET NULL选项不能在同一句ON XXX中同时使用）。

表1.2


事件	操作
NO ACTION	CASCADE	SET DEFAULT	SET NULL
ON DELETE	删除外键时不执行任何操作–这是默认行为	删除时同时删除从表中的所有相关记录	修改时将外键字段设置为默认值	修改时将外键字段设置为NULL
ON UPDATE	修改时不执行任何操作–这是默认行为	修改记录时同时修改从表中的所有相关记录	删除时将外键字段设置为默认值	删除时将外键字段设置为NULL

如果我们不指定任何选项或指定NO ACTION，则需自行处理外键的更改（在更改主键的情况下），并且在删除主键时，应事先删除从表中的记录。使用CASCADE选项时务必非常小心：粗心使用可能导致大量相关记录被删除。

CHECK约束

数据库中一个最有用的约束是检查约束。其功能非常简单–检查插入表中的值是否满足某个条件，并根据该条件的执行结果来决定插入数据或拒绝。其语法相当简单：

= [CONSTRAINT constraint] CHECK ( )}

其中constraint是约束的名称；是搜索条件，插入/更新的值可以作为其中的参数。如果搜索条件满足，则允许插入/更新该值；如果不满足，则出现错误。最简单的检查示例：

create table checktst( ID integer CHECK(ID>0));

此检查确定插入/更新的ID字段值是否大于零，并根据结果允许插入/更新新值或报告错误（参见“InterBase存储过程语言的扩展功能”一章（第1部分））。

还有更复杂的检查变体。搜索条件的完整语法如下：

= {

{ | ()}

| [NOT] BETWEEN AND

| [NOT] LIKE [ESCAPE ]

| [NOT] IN ( [ , …] | )

| IS [NOT] NULL

| {[NOT] {= | < | >} | >= | <=}

{ALL | SOME | ANY} ()

| EXISTS ( )

| SINGULAR ( )

| [NOT] CONTAINING

| [NOT] STARTING [WITH]

| ()

| NOT

| OR

| AND }

因此，CHECK为检查插入/更新的值提供了大量选项。使用CHECK时，应记住以下限制：

CHECK的数据仅取自当前记录。不应从同一表的其他记录中获取CHECK表达式中的数据–它们可能被其他用户修改
一个字段只能有一个CHECK约束
如果字段定义使用了具有CHECK域约束的域，则不能在表中具体字段级别重新定义它。必须说明的是，CHECK是通过系统触发器实现的，因此在使用非常长的条件时需更加谨慎，这些条件可能会严重减慢记录的插入和更新过程。

删除约束

很多时候，我们会出于各种不同的原因删除各种约束。要删除约束，应使用以下形式的ALTER TABLE语句：ALTER TABLE tablename DROP CONSTRAINT constraintname

constraintname是要删除的约束的名称。如果在创建约束时指定了某个名称，则应使用该名称；但如果没有指定，则必须打开任何InterBase管理工具，搜索所有与之相关的约束，并找出InterBase为所需约束生成的系统名称。

应注意，只有表的所有者或SYSDBA系统管理员才能删除约束。