数据库断言的实现

引言

大多数关系数据库管理系统 (RDBMS) 都不实现断言。但是，可以通过使用所用 RDBMS 可用的机制来模拟它们。本文提供了一些关于如何执行此操作的示例。

背景

根据以下内容，断言是 SQL-92 中定义的约束

4.10.4  Assertions
 
An assertion is a named constraint that may relate to the content
of individual rows of a table, to the entire contents of a table,
or to a state required to exist among a number of tables.

An assertion is described by an assertion descriptor. In addi-
tion to the components of every constraint descriptor an assertion
descriptor includes:

-  the <search condition>.

An assertion is satisfied if and only if the specified <search
condition> is not false.

(来源: SQL-92, http://www.andrew.cmu.edu/user/shadow/sql/sql1992.txt)

断言类似于检查约束，但与检查约束不同的是，它们不是在表或列级别定义的，而是在模式级别定义的。(即，断言本身就是数据库对象，而不是在 CREATE TABLE 或 ALTER TABLE 语句中定义的。)

(您有时会遇到的另一种定义是，使用断言可以定义表之间的约束，但这有点误导，因为根据 SQL 标准，检查约束也可以做到这一点。)

CREATE ASSERTION 的 SQL 语法是

CREATE ASSERTION <constraint name> CHECK (<search condition>)    

下一节将介绍一些实现。

实现

在第一个示例中，我们想看一个存储客户及其合同的数据库。在 MS T-SQL 中，这将是

CREATE TABLE Client(
    ID int not null,
    Name varchar(255) not null,
CONSTRAINT pk_Client PRIMARY KEY (ID))
GO
 
CREATE TABLE Contract(
    ID int not null,
    ClientID int not null,
    Title varchar(255) not null,
    ValidFrom datetime not null DEFAULT GETDATE(),
    ValidTo datetime null,
CONSTRAINT chk_ContractDate CHECK (ISNULL(ValidTo, ValidFrom) >= ValidFrom),
CONSTRAINT fk_ClientID FOREIGN KEY (ClientID) REFERENCES Client(ID),
CONSTRAINT pk_Contract PRIMARY KEY (ID))
GO

要创建的断言是每个客户不允许超过一个有效合同。如果 T-SQL 支持断言，这将转化为类似

CREATE ASSERTION asrt_OneValidContract CHECK (1 <= ALL(
SELECT COUNT(ClientID) 
FROM Contract
WHERE getdate() BETWEEN ValidFrom and ISNULL(ValidTo, GETDATE())
GROUP BY ClientID))
GO

由于不支持断言，我们需要考虑替代解决方案。我们最接近的是定义一个显示所有违规记录的视图。这是我们将使用的第一个方法。

带检查选项的可更新视图 

将断言语句重写为视图，我们得到

CREATE VIEW chk_AssertionOneValidContract
AS
SELECT ClientID FROM Contract
WHERE getdate() BETWEEN ValidFrom and ISNULL(ValidTo, GETDATE())
GROUP BY ClientID HAVING COUNT(*) > 1
GO

该视图显示了拥有多个有效合同的违规客户。(由于断言检查有效记录，因此上述视图并非完全相同，因为它显示的是违规记录。)

接下来，我们需要一种机制来确保只有那些搜索条件不为 FALSE 的记录 (即不在视图中显示的记录) 才能被存储。一种解决方案是引入一个触发器，该触发器会检查此视图的所有修改，并回滚违规行。但是，有一个更简单的解决方案，那就是使用带检查选项的可更新视图。

可更新视图是允许通过视图修改底层表的视图。这意味着可以通过视图维护数据 (即在底层表中插入、更新、删除行)。在 MS SQL 中，只要满足某些条件，所有视图都是可更新的。(有关更多详细信息，请参阅 http://technet.microsoft.com/en-us/library/ms187956.aspx )

WITH CHECK OPTION 确保在通过视图维护数据时，会检查更新和插入，以确保更新或插入完成后，受影响的行仍然可以通过视图看到。否则，更新或插入将被拒绝，这正是我们想要的。(对于删除，MS SQL Server 不进行检查，因为删除的本质就是让行消失。)

使用带检查选项的可更新视图，如下所示，可确保每个客户不超过一个有效合同

CREATE VIEW vw_AssertionOneValidContract
AS
    SELECT ID, ClientID, Title, ValidFrom, ValidTo
FROM Contract
WHERE ID NOT IN (SELECT ID FROM chk_AssertionOneValidContract)
WITH CHECK OPTION 
GO

该视图返回所有不在显示违规记录的视图中的记录。以下语句显示了 WITH CHECK OPTION 的工作方式

--First insert some client rows
INSERT INTO Client (ID, Name) VALUES (1, 'Tom Inc.')
INSERT INTO Client (ID, Name) VALUES (2, 'Jones Inc.')
GO
(2 row(s) affected)
 
--Then insert first contract for client #1
INSERT INTO vw_AssertionOneValidContract (ID, ClientID, Title)
VALUES (1, 1,'Contract 1')
GO
(1 row(s) affected)
 
--Now second which fails as there already is a valid contract
INSERT INTO vw_AssertionOneValidContract (ID, ClientID, Title)
VALUES (2, 1,'Contract 2')
GO
 
Msg 550, Level 16, State 1, Line 1
The attempted insert or update failed because the target view either 
specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION 
and one or more rows resulting from the operation 
did not qualify under the CHECK OPTION constraint.
The statement has been terminated.
 
--Insert contract for client #2
INSERT INTO vw_AssertionOneValidContract (ID, ClientID, Title)
VALUES (2, 2,'Contract 2')
GO
(1 row(s) affected)
 
--Set first contract to invalid
UPDATE vw_AssertionOneValidContract
SET ValidTo = GETDATE()
WHERE ID = 1
GO
(1 row(s) affected)
 
--Insert third contract which is now accepted as the first contract is invalid
INSERT INTO vw_AssertionOneValidContract (ID, ClientID, Title)
VALUES (3, 1,'Contract 3')
GO
(1 row(s) affected)
 
--Now try to move contract which is rejected 
-- as the second client already has a valid contract
UPDATE vw_AssertionOneValidContract
SET ClientID = 2
WHERE ID = 3
GO
 
Msg 550, Level 16, State 1, Line 1
The attempted insert or update failed because the target view 
either specifies WITH CHECK OPTION or spans a view that specifies 
WITH CHECK OPTION and one or more rows resulting from the operation 
did not qualify under the CHECK OPTION constraint.
The statement has been terminated.

可以看出，该视图能够按预期工作，并且只允许不为客户分配多个有效合同的修改。不幸的是，使用带检查选项的可更新视图并非总是可以用来模拟断言。它仅限于断言仅取决于单个表修改且不需要检查 DELETE 语句的情况。如果修改涉及多个表，或者 DELETE 语句会影响断言，那么带检查选项的可更新视图就不再足够。

如果示例更改为合同可以分配给多个客户 (实际上是声明一个 m:n 关系)，并且断言更改为每个客户必须至少有一个合同，那么我们将需要找到一个不同的解决方案。这将在下一节中展示。

延迟检查

更改后的示例的表将如下所示

CREATE TABLE Contract(
    ID int not null,
    Title varchar(255) not null,
    ValidFrom datetime not null default getdate(),
    ValidTo datetime null,
CONSTRAINT chk_ContractDate CHECK (ISNULL(ValidTo, ValidFrom) >= ValidFrom),
CONSTRAINT pk_Contract PRIMARY KEY (ID))
GO
 
CREATE TABLE Client(
    ID int not null,
    Name varchar(255) not null,
CONSTRAINT pk_Client PRIMARY KEY (ID))
GO
 
CREATE TABLE ClientContract(
    ClientID int NOT NULL,
    ContractID int NOT NULL,
CONSTRAINT fk_ClientContract_ContractID FOREIGN KEY (ContractID) REFERENCES Contract(ID) 
    ON UPDATE CASCADE,
CONSTRAINT fk_ClientContract_ClientID FOREIGN KEY (ClientID) REFERENCES Client(ID)
    ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT pk_ClientContract PRIMARY KEY (ClientID, ContractID))
GO

我们没有一个引用客户表的合同表，而是有一个映射表 ClientContract，它将一个表中的合同与另一个表中的客户链接起来。该表允许 m:n 关系，因此一个合同可以分配给多个客户。

一种可以强制执行断言的方法是在每次修改后检查记录是否违反了断言。然而，这只适用于现有的客户记录，而不适用于新记录，因为对于新记录存在内置的循环依赖：当插入一条新客户记录时，必须已经存在合同 (因为断言要求每个客户至少有一个合同)。但是，由于外键约束，在相关客户记录创建之前，无法将合同链接到客户。

为了解决这个问题，我们需要一种机制来延迟检查，直到所有行都设置好，即，将约束检查延迟到数据一致且可以提交为止。虽然在任何 RDBMS 中都无法将触发器延迟到事务提交，但在某些 RDBMS (但不是 MS SQL Server) 中可以延迟约束的检查。

为了能够使用延迟检查，我们将不得不切换到另一个 RDBMS，因为它在 MS SQL Server 中不可用。PostgreSQL 支持延迟检查，但仅限于外键约束，而不包括检查约束。因此，我在本节其余部分将使用的 RDBMS 是 Oracle。(我使用的是 Oracle Express Edition，这是一个免费提供的开发者版本。)

Oracle 中修改后的示例表的 DDL 如下。

CREATE TABLE Contract(
    ID int not null,
    ValidFrom DATE DEFAULT CURRENT_DATE,
    ValidTo DATE null,
CONSTRAINT chk_ContractDate CHECK ((ValidTo IS NULL) OR (ValidTo >= ValidFrom)),
CONSTRAINT pk_Contract PRIMARY KEY (ID));

CREATE TABLE Client(
    ID int not null,
    Name varchar(255) not null,
CONSTRAINT pk_Client PRIMARY KEY (ID));

CREATE TABLE ClientContract(
    ClientID int NOT NULL,
    ContractID int NOT NULL,
CONSTRAINT fk_ClientContract_ClientID FOREIGN KEY (ClientID) REFERENCES Client(ID)
ON DELETE CASCADE 
DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT fk_ClientContract_ContractID FOREIGN KEY (ContractID) REFERENCES Contract(ID),
CONSTRAINT pk_ClientContract PRIMARY KEY (ClientID, ContractID));

相关的约束都声明为 `DEFERRABLE INITIALLY DEFERRED`，这意味着它们不会在修改表时立即检查，而是延迟到提交时进行检查。

基于上述表，断言将是

CREATE ASSERTION asrt_MoreThanOneValidContract CHECK (1 <= ALL (SELECT COUNT(c.ClientID)
FROM ClientContract c
    JOIN Contract ct ON c.ContractID = ct.ID
WHERE ct.ValidFrom <= CURRENT_DATE AND (ct.ValidTo >= CURRENT_DATE OR ct.ValidTo IS NULL)
GROUP BY c.ClientID));

下一步是考虑哪些修改会对断言产生影响 (如果断言存在的话)，这些修改是

• 插入客户 
• 插入、更新、删除 ClientContract 
• 更新合同的有效日期

删除合同也会有影响，但由于外键的限制，这是不允许的，所以我们不必处理。另外，由于声明了级联删除，删除客户也由数据库处理，所以我们也不必处理。

为了使用延迟检查，我们需要将 Oracle 不支持的断言检查转换为 Oracle 支持的检查约束。乍一看很困难，但实际上很简单：设置一个影子表，用一个自动更新的计数器列来存储客户的有效合同数量，并定义一个延迟检查约束，以确保在提交时该计数大于 1。

--Temporary table to store a list of clients for which counter needs to be updated
CREATE GLOBAL TEMPORARY TABLE AffectedClients(
    ID int not null,
        ContractCount int DEFAULT 0,
CONSTRAINT chk_ContractCount CHECK (ContractCount > 0) DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT pk_AffectedClients PRIMARY KEY (ID))
      ON COMMIT DELETE ROWS;

该表被定义为临时表，因为受修改影响的客户列表只需要存储到提交为止，之后就不再相关了。(临时表的用法与 MS SQL Server 不同。请参阅 http://docs.oracle.com/cd/E11882_01/server.112/e25789/tablecls.htm#CNCPT1138 了解 Oracle 中的临时表。)

现在剩下的是定义触发器，在上述相关修改发生时更新 AffectedClients。在这样做之前，我们将定义一个视图来显示客户的有效合同，因为在设置触发器时，将经常查询此信息。

--View to show number of valid contracts
CREATE VIEW vw_ValidClientContract
AS 
SELECT 
    c.ClientID, c.ContractID
FROM ClientContract c
    JOIN Contract ct ON c.ContractID = ct.ID
WHERE ct.ValidFrom <= CURRENT_DATE AND (ct.ValidTo >= CURRENT_DATE OR ct.ValidTo IS NULL);

现在我们转向相关触发器的实现。Oracle 中的触发器与 MS SQL 中的触发器工作方式也不同。有一篇单独的文章对此进行了介绍：https://codeproject.org.cn/Articles/621532/Comparison-of-Triggers-in-MS-SQL-and-Oracle。 下面的触发器都是复合触发器，它们首先填充 AffectedClients，然后用合同数量对其进行更新。

--Create trigger on ClientContract, Contract, and Client to automatically update ContractCount on AffectedClients
CREATE OR REPLACE TRIGGER tiud_ClientContract
  FOR INSERT OR UPDATE OR DELETE
  ON ClientContract
COMPOUND TRIGGER
    --Variables to check for already inserted client ids to avoid duplicates
    CountOLD INTEGER;
    CountNEW INTEGER;
    
    AFTER EACH ROW IS
    BEGIN
        SELECT COUNT(*) INTO CountOLD FROM AffectedClients WHERE ID = :OLD.ClientID;
        SELECT COUNT(*) INTO CountNEW FROM AffectedClients WHERE ID = :NEW.ClientID;
        
        IF DELETING AND CountOLD = 0 
        THEN
            INSERT INTO AffectedClients(ID) VALUES (:OLD.ClientID);
        ELSIF INSERTING AND CountNEW = 0 
        THEN
            INSERT INTO AffectedClients(ID) VALUES (:NEW.ClientID);
        ELSE
            IF :NEW.ClientID <> :OLD.ClientID THEN
                IF CountOLD = 0 THEN
                    INSERT INTO AffectedClients(ID) VALUES (:OLD.ClientID);
                END IF;
                IF CountNEW = 0 THEN
                    INSERT INTO AffectedClients(ID) VALUES (:NEW.ClientID);
                END IF;
            END IF;
        END IF;
    END AFTER EACH ROW;
    
    AFTER STATEMENT IS
    BEGIN
        --First delete rows for clients that are no longer existing
        DELETE AffectedClients C WHERE ID NOT IN (SELECT ID FROM Client WHERE ID = C.ID);
        --Then update contract count for remaining ones
        UPDATE AffectedClients C
        SET ContractCount = (SELECT COUNT(*) FROM vw_ValidClientContract CC
            WHERE C.ID = CC.ClientID);
    END AFTER STATEMENT;
    
END tiud_ClientContract;
/

CREATE OR REPLACE TRIGGER tu_Contract
  FOR UPDATE OF ValidFrom, ValidTo
  ON Contract
COMPOUND TRIGGER

    AFTER EACH ROW IS
    BEGIN
        INSERT INTO AffectedClients(ID) 
        SELECT DISTINCT ClientID FROM ClientContract 
        WHERE ContractID IN (:OLD.ID, :NEW.ID)
        AND ClientID NOT IN (SELECT ID FROM AffectedClients);
    END AFTER EACH ROW;

    AFTER STATEMENT IS
    BEGIN
        UPDATE AffectedClients C
        SET ContractCount = (SELECT COUNT(*) FROM vw_ValidClientContract CC 
            WHERE C.ID = CC.ClientID);
    END AFTER STATEMENT;
    
END tu_Contract;
/

CREATE OR REPLACE TRIGGER ti_Client
  FOR INSERT
  ON Client
COMPOUND TRIGGER

    AFTER EACH ROW IS
    BEGIN
        INSERT INTO AffectedClients(ID) VALUES (:NEW.ID);
    END AFTER EACH ROW;

    AFTER STATEMENT IS
    BEGIN
        UPDATE AffectedClients C
        SET ContractCount = (SELECT COUNT(*) FROM vw_ValidClientContract CC 
            WHERE C.ID = CC.ClientID);
    END AFTER STATEMENT;
    
END ti_Client;
/

设置好触发器后，我们就可以测试实现了

--First insert some contracts
NSERT INTO Contract (ID, ValidFrom, ValidTo) VALUES (1, '01/01/2011', '01/01/2012');
INSERT INTO Contract (ID, ValidFrom, ValidTo) VALUES (2, '01/01/2012', null);
COMMIT;

--Try to insert client without contract linked to it which fails 
INSERT INTO Client(ID, Name) VALUES (1, 'Tom Inc.');
COMMIT;
COMMIT
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (SYSTEM.CHK_CONTRACTCOUNT) violated

--Try to insert mapping of contract for non existing client which fails
INSERT INTO ClientContract(ClientID, ContractID) VALUES (1,1);
COMMIT;
COMMIT
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (SYSTEM.CHK_CONTRACTCOUNT) violated

--Then try to insert a client and link to contract #1 which fails as the contract is invalid
INSERT INTO Client(ID, Name) VALUES (1, 'Tom Inc.');
INSERT INTO ClientContract(ClientID, ContractID) VALUES (1,1);
COMMIT;

COMMIT
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (SYSTEM.CHK_CONTRACTCOUNT) violated

--Now try again to insert a client and link to the existing contracts which works as one is valid
INSERT INTO Client(ID, Name) VALUES (1, 'Tom Inc.');
INSERT INTO ClientContract(ClientID, ContractID) VALUES (1,1);
INSERT INTO ClientContract(ClientID, ContractID) VALUES (1,2);
COMMIT;

Commit complete.

--Now try to delete the links which fails as then the client would have no contracts any more
DELETE ClientContract;
COMMIT;
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (SYSTEM.CHK_CONTRACTCOUNT) violated
 
--Insert a second client and move all contracts to it
--which fails as the first client would be left without one
INSERT INTO Client(ID, Name) VALUES (2, 'Jones Inc.');
UPDATE ClientContract SET ClientID = 2 WHERE ClientID = 1;
COMMIT;

COMMIT
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (SYSTEM.CHK_CONTRACTCOUNT) violated
 
--Insert a second client and also assign all contracts to it which succeeds
INSERT INTO Client(ID, Name) VALUES (2, 'Jones Inc.');
INSERT INTO ClientContract(ClientID, ContractID) VALUES (2,2);
COMMIT;

Commit complete.

--Set all contracts to valid which succeeds
UPDATE Contract set ValidTo = NULL;
COMMIT;

Commit complete.

--Set all contracts to a future date which also succeeds
UPDATE Contract set ValidTo = '01/01/2099';
COMMIT;

Commit complete.

--Set all contracts to a date in the past which fails as expected
UPDATE Contract set ValidTo = '01/01/2013';
COMMIT;

COMMIT
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (SYSTEM.CHK_CONTRACTCOUNT) violated

--Cleanup clients which succeeds as mappings to contracts are also deleted
DELETE Client;
COMMIT;

Commit complete.

--Cleanup contracts which succeeds
DELETE Contract;
COMMIT;

Commit complete.

上面的代码显示，可以使用影子表和触发器在 Oracle 中模拟断言。此方法的步骤是

1. 定义断言
2. 将断言转换为带延迟检查的检查约束，并将其放在影子表上。
3. 识别会影响断言的修改。
4. 设置在这些修改事件中触发的触发器，并更新影子表，以查看它们是通过还是未能通过检查约束。

正如 Oracle 解决方案所建议的，断言与 RDBMS 支持延迟约束检查的能力密切相关，因为断言可以定义为与表或行级别的现有约束相冲突，而解决此问题的唯一方法是延迟约束的检查。

关注点

如前所述，MS SQL Server 不支持延迟约束检查，但可以在事务中启用和禁用模式级别的约束检查，并延迟会违反约束的修改。我想将这些技术包含在这篇文章中，但决定将其放在另一篇文章中，该文章应很快发布。

Oracle 解决方案的弱点在于它不包括 Client ID 或 Contract ID 的更新。虽然 Oracle 支持级联删除，但它不像 MS SQL Server 那样支持级联更新。为了处理主键更新，需要扩展触发器框架，以便传播更新或简单地通过回滚来阻止它。

历史

• 2013 年 8 月 7 日：初始版本。