CREATE ASSERTION
NAME
CREATE ASSERTION: define an assertion
SYNOPSIS
<assertion definition> ::=
CREATE ASSERTION <constraint name> CHECK ( [ WITH <XML lexically scoped options> ] <search condition> )
[ <constraint attributes> ]
The <constraint attributes> are those of CREATE TABLE.
DESCRIPTION
CREATE ASSERTION defines an assertion: a constraint of its schema, on no one table, which holds while its condition is not false. It is checked as it is made, and so made only if it holds; then at the end of each statement that changes a table it reads, or, if it is deferred, at commit.
Its XML options, XMLNAMESPACES and XMLBINARY, are in scope of its condition, and kept with its text: see XML Values.
CREATE ASSERTION runs within the session's transaction: ROLLBACK undoes it.
RULES
- The assertion's schema exists, and its user owns it. An assertion named without a schema is of the session's default schema.
- No constraint of the schema has the name.
- Its condition holds as it is made.
- Its condition contains no USER, CURRENT_USER, SESSION_USER or SYSTEM_USER, no CURRENT_DATE, CURRENT_TIME or CURRENT_TIMESTAMP, is not possibly non-deterministic, and reads no temporary table.
- The transaction is not READ ONLY.
PRIVILEGES
REFERENCES on each column the condition names of a table it reads, or, if it names none, on one column of each table it reads.
ERRORS
| SQLSTATE | SQLCODE | When |
|---|---|---|
| 3F000 | -103 | The schema does not exist |
| 2A000 | -103 | A rule above is broken |
| 2A000 | -104 | Its user does not own the schema, or lacks the REFERENCES it needs |
| 23000 | -201 | The condition is false as it is made |
| 25000 | -917 | The transaction is READ ONLY |
Once made, a statement that leaves the assertion false fails with SQLSTATE 23000, SQLCODE -201; a COMMIT at which a deferred one is false fails with SQLSTATE 40002, SQLCODE -913, the transaction rolled back.
EXAMPLES
Of the table LEDGER of CREATE DOMAIN:
CREATE ASSERTION BALANCED CHECK (NOT EXISTS (SELECT * FROM LEDGER WHERE AMOUNT > 1000));
INSERT INTO LEDGER VALUES (3, 5000);
CREATE ASSERTION LATER CHECK (NOT EXISTS (SELECT * FROM LEDGER WHERE ENTRY < 0)) INITIALLY DEFERRED;
INSERT INTO LEDGER VALUES (-1, 1);
COMMIT;
OK
turborel: SQLCODE -201 (SQLSTATE 23000): assertion ANN.BALANCED is false [ISO/IEC 9075:1992 §11.34 GR2]
OK
OK
turborel: SQLCODE -913 (SQLSTATE 40002): the transaction is rolled back: assertion ANN.LATER is false [ISO/IEC 9075:1992 §11.34 GR2] [ISO/IEC 9075:1992 §14.3 GR5a]
STANDARD
ISO/IEC 9075:1992 §11.34; its XML options are ISO/IEC 9075-14:2006
draft §12.4, Features X083 and X133. After a statement that changes a table it
reads, an assertion whose every conjunct has one of these forms is
checked from the rows the statement changed, the others whole.
NOT EXISTS (SELECT ... FROM T WHERE p) is checked from the rows the
statement inserted or updated; so are (SELECT MAX(x) FROM T WHERE p)
less than a constant, or not greater, and MIN greater, or not less
("bounded").
(SELECT COUNT(*) FROM T WHERE p) compared with a constant, and COUNT(x),
is judged of a count the assertion keeps, as SUM's below, with how many
rows the statement brought into the count or took out of it.
COUNT(DISTINCT x) is checked from how many values the statement brought
in or took out, a row keeping its value changing nothing, whole only if
that may move the count toward breaking the comparison, or the
transaction reads at a snapshot of each statement.
MAX greater than a constant, or not less, and MIN less, or not greater,
holds if a row the statement inserted or updated reaches the constant,
or if it changed none of the rows; else it is checked whole. MAX equal
to a constant is both MAX not greater and MAX not less, MIN alike. MAX
unequal to a constant holds if a row the statement inserted or updated
is greater, or if it only brought in rows less than it; MIN alike; else
it is checked whole. Of each form, p and x are of columns,
constants, arithmetic, comparisons, IS NULL, LIKE and CASE alone, of the
one table's row, through the filters and columns of views of it. Where
x may be null there (it is not a column NOT NULL or of the PRIMARY
KEY, not DEFERRABLE, nor one p is not true of when null), the warning
of a null eliminated depends on every row: the assertion is checked from
the change only if a row of it has x null, or a warning was raised
already, else whole. (SELECT SUM(x) FROM T WHERE p) compared with a
constant, and AVG compared with an exact one, x of exact numbers, are
judged of totals the assertion keeps: the values counted, the nulls and
their sum, as each transaction sees them, with what the statement
changed; out of range, the sum raises 22003 as it would checked whole.
A count kept is judged so too.
So are the nulls of x of the other forms, when it may be null. Two
transactions conflict at commit (SQLCODE -911) only if both move the
sum, the mean or the count the same way: the sum either way, the mean
and the count toward their bound; equal or unequal, any two. SUM and AVG of approximate numbers,
or of DISTINCT, are checked whole.
INFORMATION_SCHEMA.ASSERTION_TOTALS shows the totals.
NOT EXISTS, and MAX or MIN bounded, may be of inner joins of tables
too, a table joined to itself among them: the rows the statement
inserted or updated of each table are joined to the others as they are
now, through an index where there is one. Or of a grouping of one
table's rows, HAVING of the group: only the groups of the rows the
statement changed, as they are and as they were, are made again, of
their whole rows. Not of SUM or AVG of approximate numbers there; where
a set function's argument may be null, it is checked whole unless a
warning is raised. An outer join, UNION, EXCEPT or INTERSECT, a
grouping of a join, and COUNT or a greatest reached of a join, are
checked whole; so is a change of more rows than MEMORY.WORK_BYTES holds.
A deferred assertion of one NOT EXISTS, or of conjuncts each
NOT EXISTS or MAX or MIN bounded whose conditions raise no error and
whose x is never null, is checked from each row the transaction wrote
while it was deferred; one of any other of these conjuncts, of joins
and groupings too, from all those rows together once it is checked,
each as it is and as it was before the transaction first changed it
while the assertion was deferred, a count at one snapshot as an
immediate one; any other, whole, once, however many statements of the
transaction changed a table it reads. Whichever way, a statement fails
exactly when the condition is false after it, with the same error and
warning. SQL-86 has no assertions.