Implementation of Database Assertions

Introduction

Most relational database management systems (RDBMS) do not implement assertions. There are however ways to mimic them using mechanisms available depending on the RDBMS used. This article provides some examples on how to do this.

Background

Assertions are constraints defined in SQL-92 as per below:

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.

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

Assertions are similar to check constraints, but unlike check constraints they are not defined on table or column level but are defined on schema level. (i.e., assertions are database objects of their own right and are not defined within a create table or alter table statement.)

(An alternative definition you sometimes come across is that with assertions it is possible to define constraints between tables but this is slightly misleading as according to the SQL standard this is also possible for check constraints.)

The SQL syntax for create assertion is:

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

The next section deals with some implementations.

Implementation

In a first example we want to look at a database that stores clients and their contracts. In MS T-SQL, this would be:

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

The assertion to be created is that not more than one valid contract per client is allowed. If T-SQL was to support assertions this would translate into something like:

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

As assertions are not supported we need to think about an alternative solution. The closest we could get is to define a view that would show all offending records. This is the first handle we will use.

Updatable views with check option 

Rewriting the assertion statement as a view we get:

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

The view shows all offending clients that have more than one valid contract assigned. (As the assertion checks for valid records, the view above is not quite the same as it shows the offending records.)

Next we need some mechanism to ensure that only records for which the search condition is not false (i.e. the ones that are not selected in the view) can be stored. One solution would be to introduce a trigger that checks all modifications against this view and rolls back offending rows. There is however a more simple solution which is to use an updatable view with check option.

Updatable views are views that allow modifications of an underlying table through the view. This means that data can be maintained through the views (i.e. insert, update, delete rows in an underlying table). In MS SQL all views are updatable as long as they meet certain conditions. (see http://technet.microsoft.com/en-us/library/ms187956.aspx for more details)

The with check option ensures that, when maintaining data through the view, it is checked for updates and inserts that the affected rows are still visible through the view after the update or inserts has completed. Otherwise the update or insert is rejected, which is exactly what we want. (For deletes the check is not carried out by MS SQL Server for the obvious reason that it is the nature of a delete to make the row disappear.)

Using an updateable view with check option as per below ensures that there is not more than one valid contract per client:

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

The view returns all records that are not in the view that shows the offending records. The below statements show how the with check option works:

--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.

As can be seen the view does what we expect and only allows modifications that do not assign more than one valid contract to a client. Unfortunately, using updateable views with check option cannot always be used to mimic assertions. It is limited to cases where the assertion only depends on modifications in one table and delete statements do not need to be checked. If modifications in more than one table, or deletes have an impact on the assertion, updateable views with check option are no longer sufficient.

If the example was changed so that a contract can be assigned to more than one client (effectively declaring a m:n relation) and if the assertion was changed so that there must be at least one contract per client we would need to find a different solution. This is shown in the next section.

Deferred checks

The tables for the changed example would look like this:

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

Instead of having a table for contracts that references the client table we have a mapping table ClientContract that links the contracts in one table to the clients in the other. The table allows m:n relations so one contract can be assigned to more than one client.

One approach you could think of to enforce the assertion would be to check after each modification if a record is offending the assertion. This however will only work for existing client records but not for new ones as for these there is a circular dependency built in: When a new client row is inserted there must already be a contract (as the assertion requires each client to have at least one contract). Due to the foreign key constraint it is however not possible to link a contract to a client before the relevant client row has been created.

To solve this we would need a mechanism that delays the checks till all rows are set up, i.e., defer the constraint checks till the data is consistent and can be committed. While deferring triggers till the transaction is committed is not possible in any RDBMS, it is possible to defer the checking of constraints in some (not MS SQL Server though).

To be able to make use of deferred checking we will need to switch to another RDBMS as it is not available in MS SQL Server. PostrgeSQL supports deferred checking but only of foreign key constraints and not of check constraints. The RDBMS I will use for the remainder of this section is therefore Oracle. (I am using the Oracle Express Edition which is a developer edition available for free.)

The DDL of the tables for the modified example in Oracle is as per below.

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));

The relevant constraints are all declared DEFERRABLE INITIALLY DEFERRED which means that they are not checked immediately when the table is modified but deferred till commit.

Based on the tables above the assertion would be something like:

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));

The next step is to consider which modifications would have an impact on the assertion if it existed, which are:

• Insert of Client  
• Insert, update, delete of ClientContract  
• Update of valid dates of Contract

A delete contract would also have an impact but this is prohibited by the foreign key so we do not have to take care of it. Also a delete client is handled by the database as a cascaded delete is declared, so we also do not need to handle this as well.

To use deferred checks we need to transform the assertion check which is not supported by Oracle into a check constraint which is supported. What sounds difficult at first turns out to be quite straightforward: Set up a shadow table that stores the number of valid contracts for a client in a counter column which is automatically updated and for which a deferred check constraint is defined to ensure that it is greater 1 upon commit:

--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;

The table is defined as temporary as the list of clients affected by a modification only needs to be stored till commit and are not relevant afterwards. (Temporary tables work differently compared to MS SQL Server. See http://docs.oracle.com/cd/E11882_01/server.112/e25789/tablecls.htm#CNCPT1138 for temporary tables in Oracle.)

What is now left to do is to define triggers that updates the AffectedClients on the relevant modifications identified above. Before doing so we will define a view that shows the valid contracts for a client as this information will be often queried for in the triggers to be set up:

--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);

We now turn to the implementation of the relevant triggers. Triggers in Oracle also work differently compared to MS SQL. There is a separate article on this: http://www.codeproject.com/Articles/621532/Comparison-of-Triggers-in-MS-SQL-and-Oracle. The below triggers are all compound triggers that first populate AffectedClients and then updates it with the number of contracts.

--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;
/

With the triggers set up we are now ready to test the implementation:

--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.

The code above shows it is possible to mimic assertions in Oracle using a shadow table and triggers. The steps in this approach are:

1. Define the assertion
2. Transform the assertion into check constraints with deferred checking and put them on a shadow table.
3. Identify modifications that would affect the assertion.
4. Setup triggers that fire in the event of these modifications and update the shadow table to see if they pass or fail the check constraint.

As the Oracle solution suggests, assertions are closely linked to the RDBMS' ability to support deferred constraint checking as assertions can be defined to contradict existing constraints on table or row level and the only way to resolve this is to defer the checking of constraints.

Points of Interest

As already said, MS SQL Server does not support deferred constraint checking but it is possible to defer modifications which would offend constraints and enable and disable constraint checking on schema level within a transaction. I wanted to include these techniques in this article but decided to put that into a separate one which should follow soon.

The Oracle solution has the weak point that it does not cover updates of the Client ID or Contract ID. While Oracle supports cascaded deletes, it does (unlike MS SQL Server) not support cascaded updates. To cater for primary key updates the trigger framework would need to be extended to either propagate the update or simply disallow it by rolling back.

History

• 7 August 2013: Initial version.