Chapter 20 – SQL Constraint and Assertion¶
Note
You are reading a digital copy of SQL-99 Complete, Really, a book that documents the SQL-99 standard.
For more information specific to CrateDB, check out the CrateDB Reference.
In this chapter, we’ll describe SQL Constraints and SQL Assertions in detail, and show you the syntax to use to create, alter and destroy them.
Table of Contents
Constraint¶
A Schema may contain zero or more integrity Constraints (an Assertion is just a
special type of integrity Constraint: it is not necessarily dependent on a
single Base table as simple Constraints are.) An SQL Constraint is a named rule
which helps define valid sets of values by putting limits on the results of
INSERT
, UPDATE
or DELETE
operations performed on a Base table, an
Assertion, by contrast, may define valid sets of values for individual rows of
a Base table or for an entire Base table or it may define the set of valid
values required to exist among a number of Base tables. Constraints are
dependent on some Schema – the <Constraint name> must be unique within the
Schema the Constraint belongs to – and are created and dropped using standard
SQL statements.
There are four Constraint variations – UNIQUE
Constraints, PRIMARY KEY
Constraints, FOREIGN KEY
Constraints and CHECK
Constraints.
A
UNIQUE
Constraint defines one or more Columns of a Table as unique Columns: it is satisfied if no two rows in the Table have the same non-null values in the unique Columns.A
PRIMARY KEY
Constraint is aUNIQUE
Constraint that specifiesPRIMARY KEY
: it is satisfied if (a) no two rows in the Table have the same non-null values in the unique Columns and (b) none of the primary key Columns areNULL
.UNIQUE
Constraints andPRIMARY KEY
Constraints describe a Base table’s candidate keys.A
FOREIGN KEY
Constraint defines one or more Columns of a Table as referencing Columns whose values must match the values of some corresponding referenced Columns in a referenced Base table (the referenced Columns must beUNIQUE
Columns for the referenced Table). It is satisfied if, for every row in the referencing Table, the values of the referencing Columns are equal to those of the corresponding referenced Columns in some row of the referenced Table. (If either Table containsNULL
s, satisfaction of theFOREIGN KEY
Constraint depends on the Constraint’s match type.)FOREIGN KEY
Constraints describe linkages between Base tables.A
CHECK
Constraint defines a search condition: it is violated if the result of the condition isFALSE
for any row of the Table. An Assertion is aCHECK
Constraint that may operate on multiple Tables.
Non-deterministic Constraints¶
A CHECK
Constraint may not define a non-deterministic search condition –
that is, any condition whose result may vary from time to time. Here is an
example of an invalid CHECK
Constraint:
CREATE TABLE Table_1 (
column_1 DATE CHECK (column_1 = CURRENT_DATE);
This SQL statement would return an error because the CHECK
Constraint’s
search condition is non-deterministic – since the value of CURRENT_DATE
changes each time the function is called, your DBMS is not able to determine
whether the Constraint has been violated or not. For another example, run this
query twice, on the same database:
SELECT *
FROM Table_1
WHERE column_time = TIME '13:14:15';
You can be sure that the results will be the same both times. Now run this query twice:
SELECT *
FROM Table_1
WHERE column_time = CURRENT_TIME;
You can’t be sure that the results will be the same both times because the current time is a value from outside your SQL environment, beyond the control of your DBMS. Now run this query twice:
SELECT 'a '
FROM Table_1
UNION
SELECT 'A'
FROM Table_1;
Once again, you can’t be sure that the results will be the same both times.
With most Collations (NO PAD
and “case insensitive”), the <literal>s
'a'
and 'A'
are equivalent – that is, they’re equal to each other. But
they’re still not the same <literal>.
The point is that the only predictable queries are those which depend on SQL- data and defined rules. As soon as you start to use values which are outside SQL, or which result from implementation-dependent answers to areas which the SQL Standards leaves undefined, you have a query which requires a nine-syllable term to describe: “possibly non-deterministic”. Specifically, queries are possibly non-deterministic (and therefore not allowed in Constraints) if they depend on:
A niladic function (
CURRENT_DATE
,CURRENT_TIME
,CURRENT_TIMESTAMP
,LOCALTIME
,LOCALTIMESTAMP
,USER
,CURRENT_USER
,SESSION_USER
,SYSTEM_USER
,CURRENT_PATH
,CURRENT_ROLE
).An operation which picks from multiple values that may be equivalent-but-not-the-same. Picky operations include:
MIN
,MAX
,UNION
(thoughUNION ALL
is okay),INTERSECT
,EXCEPT
,DISTINCT
and grouping columns. Equivalent-but-not-the-same can be true for: character strings and times and timestamps (in the latter cases the external factor that causes non-determinism is the time zone).A routine invocation which is based on a procedure in a host language or on parameters that are set by a host program.
No matter what type of Constraint you’re defining, the main ideas are always the same.
You’re describing a state which must not be
FALSE
. This means it can be eitherTRUE
orUNKNOWN
. (It can also be “temporarilyFALSE
” – your DBMS is supposed to allow bad data until constraint check time. Then, if it descries aFALSE
condition, it must wipe the bad data out again, so it’s equally correct to say “Constraint violation” and “attempted Constraint violation”.) Evaluation of a Constraint is one of the areas whereNULL
s and three-valued logic play an important role.A Constraint is an Object in a Schema – it is not a procedure. It is, rather, a revelation to the DBMS about what you want and what you don’t want to see in your database.
Constraint Deferrability¶
All Constraints are defined with a deferral mode of either DEFERRABLE
or
NOT DEFERRABLE
. A deferral mode of DEFERRABLE
allows you to specify
when you want your DBMS to check the Constraint for violation (the choices are
at statement end or at transaction end). A deferral mode of NOT DEFERRABLE
doesn’t give you this option: your DBMS will check the Constraint for violation
as soon as it finishes executing an SQL statement. Of course, not every SQL
statement will cause your DBMS to check Constraints – the main statements that
cause Constraint checking are INSERT
, UPDATE
and DELETE
(there is
no Constraint checking for DROP
statements). DELETE
is slightly less
important because whenever you get rid of a row, there is no longer any need to
check whether that row violates a Constraint. Consider these SQL statements:
CREATE TABLE Table_1 (
column_1 SMALLINT);
ALTER TABLE Table_1 ADD CONSTRAINT constraint_1
``UNIQUE`` (column_1) NOT DEFERRABLE;
INSERT INTO Table_1 (column_1)
VALUES (1);
INSERT INTO Table_1 (column_1)
VALUES (2);
UPDATE Table_1 SET
column_1 = column_1 + 1;
Believe it or not, there are DBMSs alive today which will fail when they
encounter this example’s UPDATE
statement. The reason is that they
UPDATE
one row at a time and perform the Constraint check immediately after
doing each row (this is normally the case whenever a DBMS implements UNIQUE
Constraints using a “unique index”). Therefore, as soon as 1+1=2
is done
for the first row, there’s a duplication – even though, if the DBMS would only
proceed to do the next row, the duplication would disappear (it would end up
with a 2
in the first row and a 3
in the second row). The fact is,
there never is a need for the Constraint to be checked until the end of the
UPDATE
statement – nor does the SQL Standard allow for Constraint checking
until that time.
Every Constraint is also defined with a persistent initial constraint check
time that depends on its deferral mode: it is either INITIALLY IMMEDIATE
or
INITIALLY DEFERRED
. A Constraint that is NOT DEFERRABLE
always has an
initial constraint check time of INITIALLY IMMEDIATE
. A Constraint that is
DEFERRABLE
may have an initial constraint check time of either INITIALLY
IMMEDIATE
or INITIALLY DEFERRED
. During a transaction, each Constraint
also has a current constraint check time: its defined initial constraint check
time is always the current constraint check time at the beginning of a
transaction but you may change the check time for the period of the transaction
(from IMMEDIATE
to DEFERRED
or vice versa) if the Constraint is
DEFERRABLE
.
During a transaction, your DBMS will check every Constraint with a current constraint check time of
IMMEDIATE
for violation right after it executes an SQL statement – thus each such Constraint may be checked multiple times during a transaction.During a transaction, your DBMS will wait to check every Constraint with a current constraint check time of
DEFERRED
until the transaction ends – thus each such Constraint will be checked only once per transaction.
For each SQL-session, the current constraint check time of all Constraints is a property of that SQL-session.
To create a Constraint, use a <Table Constraint> definition or a <Column
Constraint> definition in a CREATE TABLE
or an ALTER TABLE
statement or
use a <Domain Constraint> definition in a CREATE DOMAIN
or an ALTER
DOMAIN
statement or use the CREATE ASSERTION
statement. To destroy a
Constraint, use the ALTER TABLE
, ALTER DOMAIN
or DROP ASSERTION
statements. To change an existing Constraint, drop and then redefine it.
There is a one-to-many association between Base tables and <Table Constraint>s or <Column Constraint>s: one Base table may be constrained by the rules of many <Table Constraint>s and/or many <Column Constraint>s (each of which may help define only that Table’s set of valid values). There is also a many-to-many association between Base tables and <Domain Constraint>s: multiple Base tables may contain one or more Columns that are based on the same Domain – and that Domain may be constrained by the rules of many <Domain Constraint>s. Finally, there is a many-to-many association between Base tables and Assertions: multiple Base tables may be constrained by the rules of one Assertion, and one Base table may be constrained by the rules of many Assertions.
Constraint Names¶
A <Constraint name> identifies a Constraint or an Assertion. The required syntax for a <Constraint name> is:
<Constraint name> ::=
[ <Schema name>. ] unqualified name
A <Constraint name> is a <regular identifier> or a <delimited identifier> that is unique (for all Constraints and Assertions) within the Schema it belongs to. The <Schema name> that qualifies a <Constraint name> names the Schema that the Constraint or Assertion belongs to and can either be explicitly stated, or a default will be supplied by your DBMS as follows:
If a <Constraint name> in a
CREATE SCHEMA
statement isn’t qualified, the default qualifier is the name of the Schema you’re creating.If the unqualified <Constraint name> is found in any other SQL statement in a Module, the default qualifier is the name of the Schema identified in the
SCHEMA
clause orAUTHORIZATION
clause of theMODULE
statement which defines that Module.
Here are some examples of <Constraint name>s:
CONSTRAINT_1
-- a <Constraint name>
SCHEMA_1.CONSTRAINT_1
-- a simple qualified <Constraint name>
CATALOG_1.SCHEMA_1.CONSTRAINT_1
-- a fully qualified <Constraint name>
<Table Constraint> and <Column Constraint>¶
A Base table may be constrained by zero or more <Table Constraint>s:
Constraints defined on one or more of its Columns in a CREATE TABLE
or an
ALTER TABLE
statement. <Table Constraint>s are dependent on some Base
table, and therefore on some Schema – the <Constraint name> must be unique
within the Schema the Constraint belongs to. There are five kinds of <Table
Constraint>s: UNIQUE
Constraints, PRIMARY KEY
Constraints, FOREIGN
KEY
Constraints, CHECK
Constraints and NOT NULL
Constraints (which
are really just a type of CHECK
Constraint).
A <Column definition> (and therefore a Base table) may be constrained by zero
or more <Column Constraint>s: Constraints defined on a single Column in a
CREATE TABLE
or an ALTER TABLE
statement. A <Column Constraint>
logically becomes a <Table Constraint> as soon as it is created. <Column
Constraint>s are dependent on some Base table, and therefore on some Schema –
the <Constraint name> must be unique within the Schema the Constraint belongs
to. A <Column Constraint> may be any Constraint that can be a <Table
Constraint>.
<Domain Constraint>¶
A Domain may be constrained by zero or more <Domain Constraint>s: Constraints
defined in a CREATE DOMAIN
or an ALTER DOMAIN
statement. <Domain
Constraint>s are dependent on some Domain, and therefore on some Schema – the
<Constraint name> must be unique within the Schema the Constraint belongs to.
All <Domain Constraint>s are CHECK
Constraints whose search conditions are
applied to all Columns based on the Domain and to all values cast to the
Domain. The search condition may not be a recursive search condition (that is,
it may not refer, either directly or indirectly, to the Domain that the <Domain
Constraint> belongs to) and it must begin with the <value specification>
VALUE
; that is, the only proper form for a <Domain Constraint>’s rule is:
CHECK (VALUE ...)
(This is the only time SQL allows you to use the <value specification>
VALUE
.)
Three things wrong with the World Wide Web are:
Pages can be written in different styles and formats, or just be garbage.
Pages can be duplicated.
Links can be broken (the notorious “URL not found” error).
If we could control the World Wide Web, we’d do what we could to stomp out each
of those practices, in turn. Specifically, we’d add three basic kinds of
Constraints. Well, we don’t control the Web. But we do control databases, so we
can use Constraints to stop bad data from getting into our Base tables. (There
are other lines of defense against bad data – for example, the requirement
that values correspond to a defined <data type>, the WITH CHECK OPTION
requirement on a View, the SQL3 TRIGGER
feature, and the procedures in your
host language programs. We describe these defenses in other chapters.)
If it’s possible, you should create your Constraints and associate them only
with Base tables – that way, the process is clear to all users. You’ll know
where to look for information about the Constraints – they’ll be associated
with the Tables themselves in the INFORMATION_SCHEMA
. And, after reading
this chapter, you’ll know what the specific, rather rigid, rules are – which
reduces uncertainty, since specific and rigid rules are clear and
well-understood rules. In any case, it is logically proper to associate a
Constraint with a Table, because a Table is a set of row values and a
Constraint is a restriction (or description) of that set of values.
Constraint Descriptors¶
A UNIQUE
Constraint is defined by a descriptor that contains five pieces of
information:
The <Constraint name>, qualified by the <Schema name> of the Schema it belongs to.
The Constraint’s deferral mode: either
DEFERRABLE
orNOT DEFERRABLE
.The Constraint’s initial constraint check time: either
INITIALLY DEFERRED
orINITIALLY IMMEDIATE
.The Constraint’s rule: the <keyword>
UNIQUE
, which forces the Table’s set of valid values for one or more Columns to be unique.The names and positions of the Columns that are required to contain only unique values.
A PRIMARY KEY
Constraint is defined by a descriptor that contains five
pieces of information:
The <Constraint name>, qualified by the <Schema name> of the Schema it belongs to.
The Constraint’s deferral mode: either
DEFERRABLE
orNOT DEFERRABLE
.The Constraint’s initial constraint check time: either
INITIALLY DEFERRED
orINITIALLY IMMEDIATE
.The Constraint’s rule: the <keyword> phrase
PRIMARY KEY
, which forces the Table’s set of valid values for one or more Columns to be unique and notNULL
.The names and positions of the Columns that are the Table’s primary key and thus are required to contain only unique, non-null values. (A Table that has a primary key cannot have a proper supertable.)
A FOREIGN KEY
Constraint is defined by a descriptor that contains nine
pieces of information:
The <Constraint name>, qualified by the <Schema name> of the Schema it belongs to.
The Constraint’s deferral mode: either
DEFERRABLE
orNOT DEFERRABLE
.The Constraint’s initial constraint check time: either
INITIALLY DEFERRED
orINITIALLY IMMEDIATE
.The Constraint’s rule: the <keyword> phrase
FOREIGN KEY
, which forces the Table’s set of valid values for one or more Columns to match some corresponding Columns.The names and positions of the referencing Column(s) that make up a foreign key for a Table.
The name of the Table that contains the referenced Column(s).
The names and positions of the references Column(s) in the referenced Table.
The Constraint’s
MATCH
type (if any).The Constraint’s referential triggered actions (if any).
A NOT NULL
Constraint is defined by a descriptor that contains four
pieces of information:
The <Constraint name>, qualified by the <Schema name> of the Schema it belongs to.
The Constraint’s deferral mode: either
DEFERRABLE
orNOT DEFERRABLE
.The Constraint’s initial constraint check time: either
INITIALLY DEFERRED
orINITIALLY IMMEDIATE
.The Constraint’s rule:
CHECK
(<Column name>IS NOT NULL
).
A CHECK
Constraint is defined by a descriptor that contains four pieces of
information:
The <Constraint name>, qualified by the <Schema name> of the Schema it belongs to.
The Constraint’s deferral mode: either
DEFERRABLE
orNOT DEFERRABLE
.The Constraint’s initial constraint check time: either
INITIALLY DEFERRED
orINITIALLY IMMEDIATE
.The Constraint’s rule: the <keyword>
CHECK
followed by the parenthesized search condition that forces the Table’s set of valid values for one or more Columns to beTRUE
orUNKNOWN
for the condition.
An Assertion is defined by a descriptor that contains five pieces of information:
The <Constraint name>, qualified by the <Schema name> of the Schema it belongs to.
The Constraint’s deferral mode: either
DEFERRABLE
orNOT DEFERRABLE
.The Constraint’s initial constraint check time: either
INITIALLY DEFERRED
orINITIALLY IMMEDIATE
.The Constraint’s rule: the <keyword>
CHECK
followed by the parenthesized search condition that forces the set of valid values for one or more Base Tables to beTRUE
orUNKNOWN
for the condition.
Constraint Definition¶
A Constraint definition creates a <Table Constraint>, a <Column Constraint> or
a <Domain Constraint>. Used in a CREATE TABLE
, ALTER TABLE
, CREATE
DOMAIN
or ALTER DOMAIN
statement, it names a Constraint and defines the
Constraint’s type, deferral mode and constraint check time. The required syntax
for a Constraint definition is:
Constraint definition ::=
[ CONSTRAINT <Constraint name> ]
Constraint_type
[ <constraint attributes> ]
Constraint_type ::=
<Table Constraint> |
<Column Constrain> |
<Domain Constraint>
<Table Constraint> ::=
UNIQUE Constraint |
PRIMARY KEY Constraint |
FOREIGN KEY Constraint |
CHECK Constraint
<Column Constraint> ::=
UNIQUE Constraint |
PRIMARY KEY Constraint |
FOREIGN KEY Constraint |
NOT NULL Constraint |
CHECK Constraint
<Domain Constraint> ::=
CHECK Constraint
<constraint attributes> ::=
<constraint check time> [ [ NOT ] DEFERRABLE ] |
[ NOT ] DEFERRABLE [ <constraint check time> ]
<constraint check time> ::=
INITIALLY DEFERRED | INITIALLY IMMEDIATE
A Constraint definition defines a new rule that will constrain a Base table’s set of valid values. A <Table Constraint> and a <Column Constraint> are owned by the Table they belong to. A <Domain Constraint> is owned by the Domain it belongs to.
<Constraint Name>¶
All Constraints have names. The optional CONSTRAINT
clause of a Constraint
definition is used to provide an explicit name for a Constraint. If you omit
the CONSTRAINT
clause from a Constraint definition, your DBMS will provide
a default <Constraint name> to identify the Constraint. For example, this SQL
statement includes a Constraint definition that includes a CONSTRAINT
clause:
ALTER TABLE Table_1 ADD CONSTRAINT constraint_1
PRIMARY KEY(column_1);
(The name of the Constraint is CONSTRAINT_1
.) This SQL statement includes a
Constraint definition that omits the CONSTRAINT
clause:
CREATE TABLE Table_1 (
column_1 SMALLINT PRIMARY KEY);
(The name of the Constraint is defined by your DBMS and is therefore non- standard, so we recommend that you explicitly name all of your Constraints.)
The <Constraint name> identifies the Constraint and the Schema that it belongs
to. A <Constraint name> that includes an explicit <Schema name> qualifier
belongs to the Schema named. A <Constraint name> that does not include an
explicit <Schema name> qualifier belongs to the SQL-session default Schema. (In
both cases, that Schema must, of course, own the Table or Domain for which the
Constraint is defined.) The <Constraint name> must be unique (for all
Constraints and Assertions) within the Schema that owns it. If CREATE
TABLE
, ALTER TABLE
, CREATE DOMAIN
or ALTER DOMAIN
are part of a
CREATE SCHEMA
statement, the <Constraint name>, if explicitly qualified,
must include the <Schema name> of the Schema being created; that is, it isn’t
possible to create a Constraint belonging to a different Schema from within
CREATE SCHEMA
.
Types of Constraints¶
A <Table Constraint> defines a rule that limits the set of values for one or more Columns of a Base table.
A <Column Constraint> defines a rule that limits the set of values for one Column of a Base Table. You may define a <Column Constraint> only within a <Column definition>. Once created, a <Column Constraint> logically becomes a <Table Constraint> for the Table that owns the Column that the Constraint is defined for.
A <Domain Constraint> defines a rule that limits the set of values for every
Column that is based on the Domain that the Constraint is defined for. One or
more Columns from one or more Base tables may thus be affected by a <Domain
Constraint>. A <Domain Constraint> is a CHECK
Constraint that uses the
<value specification> VALUE
.
Deferral Mode¶
A Constraint definition may include a specification of the Constraint’s
deferral mode: either DEFERRABLE
or NOT DEFERRABLE
. A deferral mode of
NOT DEFERRABLE
means that your DBMS will check the Constraint for violation
immediately after executing every SQL statement in a transaction. A deferral
mode of DEFERRABLE
means that your DBMS may defer checking the Constraint
for violation until the end of the transaction. If you omit the deferral mode
specification from a Constraint definition, the Constraint’s deferral mode
depends on its initial constraint check time: the deferral mode for an
INITIALLY DEFERRED
Constraint defaults to DEFERRABLE
and the deferral
mode for an INITIALLY IMMEDIATE
Constraint defaults to NOT DEFERRABLE
.
Constraint Check Time¶
A Constraint definition may also include a specification of the Constraint’s
initial constraint check time: either INITIALLY DEFERRED
or INITIALLY
IMMEDIATE
. If you omit the constraint check time specification from a
Constraint definition, the Constraint will have a constraint check time of
INITIALLY IMMEDIATE
.
If its initial constraint check time is INITIALLY DEFERRED
, a Constraint’s
deferral mode must be DEFERRABLE
and its constraint check time will be
DEFERRED
at the beginning of every transaction. You may use the SET
CONSTRAINTS
statement to change a DEFERRABLE INITIALLY DEFERRED
Constraint’s constraint check time for a transaction (this is the current
constraint check time) to IMMEDIATE
.
If its initial constraint check time is INITIALLY IMMEDIATE
, a Constraint’s
deferral mode may be either DEFERRABLE
or NOT DEFERRABLE
and its
constraint check time will be IMMEDIATE
at the beginning of every
transaction. You may use the SET CONSTRAINTS
statement to change a
DEFERRABLE INITIALLY IMMEDIATE
Constraint’s constraint check time for a
transaction to DEFERRED
but you may not use SET CONSTRAINTS
on a NOT
DEFERRABLE INITIALLY IMMEDIATE
Constraint because such Constraints can’t have
their constraint check times changed.
Immediately after executing any SQL statement, your DBMS checks every
Constraint with a current constraint check time of IMMEDIATE
for violation,
but does not check the Constraints with a current constraint check time of
DEFERRED
. At the end of a transaction, any Constraints with a current
constraint check time of DEFERRED
have it changed to IMMEDIATE
– thus,
your DBMS checks every Constraint for violation at the end of a transaction.
When checked, if any Constraint is violated, the SQL statement that caused it
to be checked will fail: your DBMS will return the SQLSTATE error 23000
"integrity constraint violation"
unless the SQL statement that fails is a
COMMIT
statement. If COMMIT
fails, your DBMS will return the SQLSTATE
error 40002 "transaction rollback-integrity constraint violation"
. In either
case, the status of all SQL-data remains as it was prior to the execution of
the failed SQL statement.
Caution
You’re taking a huge risk when you use deferred Constraints, since you’re
not warned of any problems until COMMIT
time. Remember that, at this
point, instead of returning a message like “sorry the Constraint’s been
violated” and giving you a chance to fix the problem, your DBMS will say
“sorry the Constraint’s been violated” and ROLLBACK
s the entire
transaction! In other words, although you’ve asked for COMMIT
, what
you get is ROLLBACK
. This is perhaps the only command in any
programming language where, if you ask for x, you not only don’t get
x, you actually get the precise reverse of x! If you must use
deferred Constraints, add this SQL statement to your transaction before
you COMMIT
:
SET CONSTRAINTS ALL IMMEDIATE;
The advantage of SET CONSTRAINTS ALL IMMEDIATE
is that it won’t
ROLLBACK
, so if you execute it before you COMMIT
, you improve
your chances of having something to commit.
Although it’s usually best to check all Constraints for violation right after you’ve done an operation that might cause your data to be invalid, here’s some reasons why you might want to defer Constraint checking:
Because some invalid state might be true for a while (such as a transaction that temporarily throws everything out of balance), but you know that the situation will resolve itself by transaction end.
Because you want to subvert or ignore Constraints until there is some reason to worry about them. For example, there might be some calculations that you want to perform on a “what if” basis, and the only way to get them straight is by temporarily turning off the Constraint checking mechanism. Such “what if” calculations are normally ended with a
ROLLBACK
statement.
Caution
There are some systems – the notorious example is the requirement for
ODBC – which “auto-commit”. This means that as soon as a SQL statement
is finished, your helpful DBMS will automatically execute a COMMIT
statement for you! As well as being a violation of the SQL Standard and
making the ROLLBACK
statement useless, this action destroys the basis
on which we lay our deferred-Constraint plans.
Here is an example of a SQL statement that adds a NOT DEFERRABLE INITIALLY
IMMEDIATE
Constraint to a Table:
ALTER TABLE Table_1 ADD CONSTRAINT constraint_1
CHECK (column_1 > 500) NOT DEFERRABLE INITIALLY IMMEDIATE;
This SQL statement adds a DEFERRABLE INITIALLY IMMEDIATE
Constraint to a
Table:
ALTER TABLE Table_1 ADD CONSTRAINT constraint_1
CHECK (column_1 > 500) DEFERRABLE INITIALLY IMMEDIATE;
This SQL statement adds a DEFERRABLE INITIALLY DEFERRED
Constraint to a
Table:
ALTER TABLE Table_1 ADD CONSTRAINT constraint_1
CHECK (column_1 > 500) DEFERRABLE INITIALLY DEFERRED;
If you want to restrict your code to Core SQL, don’t name your Constraints and
don’t add a <constraint attributes> clause to your Constraint definitions.
(This means you’ll be defining all Constraints as NOT DEFERRABLE INITIALLY
IMMEDIATE
.)
Constraint_type – UNIQUE Constraint¶
A UNIQUE
Constraint is either a <Table Constraint> or a <Column Constraint>
and defines a rule that constrains a unique key to non-duplicate values only.
The required syntax for a UNIQUE
Constraint is:
UNIQUE <Table Constraint> ::=
[ CONSTRAINT <Constraint name> ]
UNIQUE (<Column name> [ {,<Column name>}... ]) | UNIQUE (VALUE)
[ <constraint attributes> ]
UNIQUE <Column Constraint> ::=
[ CONSTRAINT <Constraint name> ]
<Column name> UNIQUE
[ <constraint attributes> ]
A Base table may be constrained by zero or more UNIQUE
Constraints, each
specifying a rule that a group of one or more Columns (the unique key) may
contain only unique values. You can’t define a unique key with Columns that
have a <data type> of BLOB
, CLOB
, NCLOB
or ARRAY
. A unique key
is also known as a candidate key of the Table. The main reasons you need
candidate keys are (a) to get row-level addressing, (b) so that foreign
keys can reference the candidate key and (c) to prevent duplication (keyboard
errors, etc).
Each UNIQUE
Constraint must name a set of Columns that is different from
the set of Columns named by any other UNIQUE
or PRIMARY KEY
Constraint
defined for the Table. If you use UNIQUE (VALUE)
to define a UNIQUE
Constraint, you’re constraining the Table that owns the Constraint to have just
that one UNIQUE
Constraint and – since a PRIMARY KEY
Constraint is a
type of UNIQUE
Constraint – you’re also constraining that Table not to
have any PRIMARY KEY
Constraint. UNIQUE (VALUE)
constrains the entire
row of the Table to be unique from any other row.
Here are some examples of UNIQUE
Constraint definitions:
CREATE TABLE Table_1 (
column_1 SMALLINT,
CONSTRAINT constraint_1 UNIQUE(column_1) DEFERRED INITIALLY DEFERRED);
-- defines a UNIQUE <Table Constraint> in CREATE TABLE
CREATE TABLE Table_1 (
column_1 SMALLINT
CONSTRAINT constraint_1 UNIQUE DEFERRED INITIALLY DEFERRED,
column_2 CHAR(5));
-- defines a UNIQUE <Column Constraint> in CREATE TABLE
ALTER TABLE Table_1 ADD CONSTRAINT constraint_2
UNIQUE(column_1,column_2) DEFERRED INITIALLY DEFERRED;
-- defines a UNIQUE <Table Constraint> in ALTER TABLE
Once created, a UNIQUE
<Column Constraint> logically becomes a
UNIQUE
<TableConstraint>. The <Column Constraint> in this SQL statement:
CREATE TABLE Table_1 (
column_1 SMALLINT UNIQUE);
is therefore equivalent to the <Table Constraint> in this SQL statement:
CREATE TABLE Table_1 (
column_1 SMALLINT,
UNIQUE(column_1));
A UNIQUE
Constraint makes it impossible to COMMIT
any operation that
would cause the unique key to contain any non-null duplicate values. (Multiple
null values are allowed, since the null value is never equal to anything, even
another null value.) A UNIQUE
Constraint is violated if its condition is
FALSE
for any row of the Table it belongs to. Consider these SQL
statements:
CREATE TABLE Table_1 (
column_1 SMALLINT,
column_2 VARCHAR(5),
CONSTRAINT constraint_1
UNIQUE(column_1,column_2) DEFERRABLE INITIALLY DEFERRED);
INSERT INTO Table_1 (column_1, column_2)
VALUES (1, 'hello');
For this example, CONSTRAINT_1
would be violated only if you tried to
INSERT
another {1
, 'hello'
} row into TABLE_1
: a {1
,
'bye'
} row, a {2
, 'hello'
} row, a {null, 'hello'
} row, a
{1
, null} row and a {null, null} row would all satisfy the Constraint.
If you want to restrict your code to Core SQL, don’t use the UNIQUE(VALUE)
form to define a UNIQUE
Constraint and don’t add a NOT NULL
Constraint
to any Column that is part of a unique key for a UNIQUE
Constraint.
Constraint_type – PRIMARY KEY Constraint¶
A PRIMARY KEY
Constraint is either a <Table Constraint> or a <Column
Constraint> and defines a rule that constrains a unique key to non-duplicate,
non-null values only. The required syntax for a PRIMARY KEY
Constraint is:
PRIMARY KEY <Table Constraint> ::=
[ CONSTRAINT <Constraint name> ]
PRIMARY KEY (<Column name> [ {,<Column name>}... ])
[ <constraint attributes> ]
PRIMARY KEY <Column Constraint> ::=
[ CONSTRAINT <Constraint name> ]
<Column name> PRIMARY KEY
[ <constraint attributes> ]
A Base table may be constrained by no more than one PRIMARY KEY
Constraint,
which specifies a rule that a group of one or more Columns is the Table’s
primary key. A primary key is the set of Columns in a Base table that (because
they will be used as the main unique identifier for a row) must contain only
unique and not null values. You can’t define a primary key with Columns that
have a <data type> of BLOB
, CLOB
, NCLOB
or ARRAY
.
A Table’s PRIMARY KEY
Constraint must name a set of Columns that is
different from the set of Columns named by any other UNIQUE
Constraint
defined for the Table. Which unique key should be the primary key for a Table?
The criteria are:
Simplicity, i.e.: the key with the fewest Columns and smallest size.
Permanence.
Mnemonicity, i.e.: the key that people will understand and remember.
The key’s use in other (e.g.: foreign) Tables.
Here are some examples of PRIMARY KEY
Constraint definitions:
CREATE TABLE Table_1 (
column_1 SMALLINT,
CONSTRAINT constraint_1 PRIMARY KEY(column_1) NOT DEFERRABLE;
-- defines a PRIMARY KEY <Table Constraint> in CREATE TABLE
CREATE TABLE Table_1 (
column_1 SMALLINT CONSTRAINT constraint_1 PRIMARY KEY NOT DEFERRABLE,
column_2 CHAR(5));
-- defines a PRIMARY KEY <Column Constraint> in CREATE TABLE
ALTER TABLE Table_2 ADD CONSTRAINT constraint_2
PRIMARY KEY(column_1,column_2) NOT DEFERRABLE INITIALLY IMMEDIATE;
-- defines a PRIMARY KEY <Table Constraint> in ALTER TABLE
Once created, a PRIMARY KEY
<Column Constraint> logically becomes a
PRIMARY KEY
<Table Constraint>. The <Column Constraint> in this SQL
statement:
CREATE TABLE Table_1 (
column_1 SMALLINT PRIMARY KEY);
is therefore equivalent to the <Table Constraint> in this SQL statement:
CREATE TABLE Table_1 (
column_1 SMALLINT,
PRIMARY KEY(column_1));
A PRIMARY KEY
Constraint makes it impossible to COMMIT
any
operation that would cause the unique key to contain any NULL
s or
any duplicate values. A PRIMARY KEY
Constraint is violated if its
condition is FALSE
for any row of the Table it belongs to. Consider
these SQL statements:
CREATE TABLE Table_1 (
column_1 SMALLINT,
column_2 VARCHAR(5),
CONSTRAINT constraint_1 PRIMARY KEY(column_1,column_2) NOT DEFERRABLE );
INSERT INTO Table_1 (column_1, column_2)
VALUES (1, 'hello');
For this example, CONSTRAINT_1
would be violated if you tried to INSERT
another {1, 'hello'
} row into TABLE_1
or if you tried to insert a
{null, 'hello'
} row, a {1
, null} row or a {null, null} into
TABLE_1
.
The uniqueness of a primary key helps guarantee the integrity of your data. Once you’ve defined a primary key for a Table, you’re protected from simple errors like putting in the same person twice. More importantly, or at least equally importantly, you have the reflection of the “real world” fact that two things aren’t alike in every respect – if they were, they’d form the same record. When you declare a primary key, you are hinting to your DBMS that the data in the key is relatively static. Many attributes of a Table are transient: an employee’s salary, age, weight, title, evaluation, etc. But a primary key Column’s values tend to be stable – we don’t change people’s names or our part numbers very often. A primary key identifier also comes in handy when you’re splitting your data into two Tables. For example, consider an “Internet address”. You might start off with this Table definition:
CREATE TABLE Table_1 (
table_id VARCHAR(40),
int_address VARCHAR(50));
This is fine as long as whomever is represented in the TABLE_ID
Column only
has one Internet address. But that person now gives you the Internet address
used at work – and perhaps at several other locations. Should you have a
repeating Column (ARRAY
) for this data? Well maybe, but the use of
non-atomic values is still frowned on and deservedly has a bad rep – see our
chapter on <collection type>s for a discussion of the problem. The classic
relational solution is to split your data into two Tables. For example:
CREATE TABLE Table_1 (
table_id VARCHAR(40),
CONSTRAINT constraint_1 PRIMARY KEY(table_id));
CREATE TABLE Table_2 (
table_id VARCHAR(40),
int_address VARCHAR(50),
CONSTRAINT constraint_2 FOREIGN KEY(int_address) REFERENCES Table_1);
These definitions allow you to store as many Internet addresses for a single person as you want – a repeating group is possible in SQL, it’s just an avoidance of first-normal form if you try to put it into one Table.
Constraint_type – FOREIGN KEY Constraint¶
A FOREIGN KEY
Constraint is either a <Table Constraint> or a <Column
Constraint> and defines a rule that constrains a foreign key to values that
match only those values contained in a referenced unique key. The required
syntax for a FOREIGN KEY
Constraint is:
FOREIGN KEY <Table Constraint> ::=
[ CONSTRAINT <Constraint name> ]
FOREIGN KEY (referencing <Column name> [ {,<Column name>}... ])
REFERENCES referenced <Table name>
[ (referenced <Column name> [ {,<Column name>}... ]) ]
[ MATCH {FULL | PARTIAL | SIMPLE} ]
[ <referential triggered action> ]
[ <constraint attributes> ]
<referential triggered action> ::=
ON UPDATE <action> [ ON DELETE <action> ] |
ON DELETE <action> [ ON UPDATE <action> ]
<action> ::=
NO ACTION |
CASCADE |
RESTRICT |
SET NULL |
SET DEFAULT
FOREIGN KEY <Column Constraint> ::=
[ CONSTRAINT <Constraint name> ]
<Column name> REFERENCES referenced <Table name>
[ (referenced <Column name>) ]
[ MATCH {FULL | PARTIAL | SIMPLE} ]
[ <referential triggered action> ]
[ <constraint attributes> ]
A Base table may be constrained by zero or more FOREIGN KEY
Constraints,
which specify a rule that a group of one or more Columns of the Table may
contain only those values found in a similar set of unique Columns belonging to
(usually) another Table. You can’t define a foreign key with Columns that have
a <data type> of BLOB
, CLOB
, NCLOB
or ARRAY
. Here are some
examples of FOREIGN KEY
Constraint definitions:
CREATE TABLE Table_2 (
column_1 SMALLINT,
CONSTRAINT constraint_1 FOREIGN KEY(column_1) REFERENCES Table_1
NOT DEFERRABLE);
-- defines a FOREIGN KEY <Table Constraint> in CREATE TABLE
CREATE TABLE Table_2 (
column_1 SMALLINT CONSTRAINT constraint_1
FOREIGN KEY REFERENCES Table_1 NOT DEFERRABLE,
column_2 CHAR(5));
-- defines a FOREIGN KEY <Column Constraint> in CREATE TABLE
ALTER TABLE Table_2 ADD CONSTRAINT constraint_2
FOREIGN KEY(column_1,column_2) REFERENCES Table_1(column_3,column_5)
DEFERRABLE INITIALLY IMMEDIATE;
-- defines a FOREIGN KEY <Table Constraint> in ALTER TABLE
Once created, a FOREIGN KEY
<Column Constraint> logically becomes a
FOREIGN KEY
<Table Constraint>. The <Column Constraint> in this SQL
statement:
CREATE TABLE Table_2 (
column_1 SMALLINT REFERENCES Table_1);
is therefore equivalent to the <Table Constraint> in this SQL statement:
CREATE TABLE Table_2 (
column_1 SMALLINT,
FOREIGN KEY(column_1) REFERENCES Table_1);
The rationale for a foreign key is: you can’t have an employee in department D
if there is no department D, you can’t have a branch that produces Widgets if
you don’t have a product called a Widget, you can’t locate an office in state =
‘TY’ if there is no state named ‘Tynnessee’. A FOREIGN KEY
Constraint
forges a link between the referencing Table and the referenced Table: it makes
it impossible to COMMIT
any operation that would cause the foreign key to
contain any values that are not found in the referenced unique key. (The
referencing Table is the Table that the FOREIGN KEY
Constraint belongs to;
the foreign key itself is made up of one or more Columns of that Table: these
are called the referencing Columns. The referenced Table is the Table that
contains the unique key that the foreign key must match: the Columns that make
up that unique key are called the referenced Columns. SQL allows the
referencing Table and the referenced Table to be the same.) In the usual
situation, illustrated in the examples above (other actions can be specified),
the Constraint makes it impossible to drop TABLE_1
(because TABLE_2
references it), or to delete or change a row in TABLE_1
so that TABLE_2
is left with unmatched referencing values, or to insert a row into TABLE_2
unless its referencing values are matched somewhere in TABLE_1
. A FOREIGN
KEY
Constraint is violated if its condition is FALSE
for any row of the
Table it belongs to. The result of the evaluation of the FOREIGN KEY
Constraint condition depends on the presence of null values and the degree of
matching specified for the Constraint; see the comments on the MATCH
clause, later in this section.
Referencing Columns¶
The FOREIGN KEY
clause of a FOREIGN KEY
<Table Constraint> definition
names the referencing Columns: the group of one or more Columns that make up
the foreign key (a Column may appear in the list only once). You may specify
only unqualified <Column name>s in this clause.
Referenced Table and Columns¶
The REFERENCES
clause of a FOREIGN KEY
Constraint definition names the
referenced Base table: the Base table that contains the referenced unique key.
The Table types must match: if the Table that owns the FOREIGN KEY
Constraint is a persistent Base table, the referenced Table must also be a
persistent Base Table; if the referencing Table is a GLOBAL TEMPORARY
Base
table, the referenced Table must also be a GLOBAL TEMPORARY
Base Table; if
the referencing Table is a created LOCAL TEMPORARY
Base table, the
referenced Table must be either a GLOBAL TEMPORARY
Base Table or a created
LOCAL TEMPORARY
Base table; if the referencing Table is a declared LOCAL
TEMPORARY
Base table, the referenced Table must be either a GLOBAL
TEMPORARY
Base Table, a created LOCAL TEMPORARY
Base table or a declared
LOCAL TEMPORARY
Base table; and if the referencing Table is any temporary
Base table defined with an ON COMMIT DELETE ROWS
clause, the referenced
Table must also be a temporary Base Table defined with that clause.
The referenced Columns, optionally named in the REFERENCES
clause of a
FOREIGN KEY
Constraint definition, are the group of one or more Columns
that make up the referenced unique key (that is, the referenced Columns must be
named in a NOT DEFERRABLE UNIQUE
or NOT DEFERRABLE PRIMARY KEY
Constraint that belongs to the referenced Table and may therefore appear in the
list only once). You may specify only unqualified <Column name>s in this
clause. The Columns in the foreign key must match the number of, and have a
comparable <data type> with, the corresponding Columns in the referenced unique
key. If you omit the referenced Columns list from a FOREIGN KEY
Constraint
definition, the referenced Table must be constrained by a NOT DEFERRABLE
PRIMARY KEY
Constraint; the primary key is also the referenced unique key.
Here are some more examples of FOREIGN KEY
Constraint definitions:
CREATE TABLE Table_1 (
column_1 SMALLINT,
column_2 VARCHAR(4),
CONSTRAINT constraint_1
PRIMARY KEY(column_1,column_2) NOT DEFERRABLE);
CREATE TABLE Table_2 (
column_1 SMALLINT,
column_2 VARCHAR(4),
CONSTRAINT constraint_2
FOREIGN KEY(column_1,column_2) REFERENCES Table_1);
-- Here the referenced unique key defaults to Table_1's primary key
CREATE TABLE Table_1 (
column_1 SMALLINT,
column_2 VARCHAR(4),
CONSTRAINT constraint_1
PRIMARY KEY(column_1,column_2) NOT DEFERRABLE);
CREATE TABLE Table_2 (
column_1 SMALLINT,
column_2 VARCHAR(4),
CONSTRAINT constraint_2
FOREIGN KEY(column_1,column_2)
REFERENCES Table_1(column_1,column_2));
-- Here the foreign key explicitly matches Table_1's primary key
CREATE TABLE Table_1 (
column_1 SMALLINT,
column_2 VARCHAR(4),
column_3 DATE,
CONSTRAINT constraint_1
PRIMARY KEY(column_1,column_2) NOT DEFERRABLE,
CONSTRAINT constraint_2
UNIQUE(column3) NOT DEFERRABLE);
CREATE TABLE Table_2 (
column_1 SMALLINT,
column_2 VARCHAR(4),
column_3 DATE,
CONSTRAINT constraint_3
FOREIGN KEY(column3) REFERENCES Table_1(column3));
-- Here the foreign key explicitly matches Table_1's unique key; this is
mandatory because, if the referenced Column list were omitted, your DBMS would
attempt to match the foreign key (COLUMN_3) to Table_1's primary key
(COLUMN_1,COLUMN_2) and would fail.
Privileges¶
In order to create a FOREIGN KEY
Constraint, the <AuthorizationID> that
owns the referencing Table must be the current <AuthorizationID> and must have
the REFERENCES
Privilege on every referenced Column named.
MATCH Clause¶
The optional MATCH
clause of a FOREIGN KEY
Constraint definition
specifies the degree of the required match between the values of the foreign
key and the referenced unique key. There are three match options: MATCH
SIMPLE
, MATCH FULL
and MATCH PARTIAL
. If you omit the MATCH
clause, it defaults to MATCH SIMPLE
. For example, these two SQL statements
are equivalent:
CREATE TABLE Table_2 (
column_1 SMALLINT,
CONSTRAINT constraint_1 REFERENCES Table_1);
CREATE TABLE Table_2 (
column_1 SMALLINT,
CONSTRAINT constraint_1 REFERENCES Table_1 MATCH SIMPLE);
The MATCH
option specified for a FOREIGN KEY
Constraint has an effect
only when your foreign key contains null values.
For MATCH SIMPLE
, a FOREIGN KEY
Constraint is satisfied if, for each
row of the referencing Table, either (a) at least one of the foreign key
Columns is NULL
or (b) none of the foreign key Columns is NULL
and
the value of the entire foreign key equals the value of the entire unique key
in at least one row of the referenced Table. For example, given a referenced
Table with these two unique key rows:
{10,'tiny'} {20,'huge'}
these foreign key rows are valid for the referencing Table:
{10,'tiny'} -- because a matching unique key row exists
{NULL,'tiny'} {10,NULL} {NULL,'soso'} {30,NULL} -- because, in each case,
one foreign key Column is NULL
and this foreign key row is invalid:
{10,'huge'} -- because no matching unique key row exists
For MATCH FULL
, a FOREIGN KEY
Constraint is satisfied if, for each row
of the referencing Table, either (a) every foreign key Column is NULL
or
(b) none of the foreign key Columns is NULL
and the value of the entire
foreign key equals the value of the entire unique key in at least one row of
the referenced Table. (If you define a FOREIGN KEY
Constraint with MATCH
FULL
and there is either (a) only one Column in the foreign key or (b)
one or more Columns defined as NOT NULL
in the foreign key, then the
Constraint will have the same effect as if you had defined the Constraint with
MATCH SIMPLE
.) For example, given a referenced Table with these two unique
key rows:
{10,'tiny'} {20,'huge'}
these foreign key rows are valid for the referencing Table:
{10,'tiny'} -- because a matching unique key row exists
{NULL,NULL} -- because the entire foreign key is NULL
and these foreign key rows are invalid:
{10,'huge'} -- because no matching unique key row exists
{NULL,'tiny'} {10,NULL} -- because, in each case, only some of the foreign
key is NULL
For MATCH PARTIAL
, a FOREIGN KEY
Constraint is satisfied if, for each
row of the referencing Table, at least one foreign key Column is NULL
and
the values of the rest of the foreign key Columns equal the values of the
corresponding unique key Columns in at least one row of the referenced Table.
(If you define a FOREIGN KEY
Constraint with MATCH PARTIAL
and there is
either (a) only one Column in the foreign key or (b) one or more Columns
defined as NOT NULL
in the foreign key, then the Constraint will have the
same effect as if you had defined the Constraint with MATCH SIMPLE
.) For
example, given a referenced Table with these two unique key rows:
{10,'tiny'} {20,'huge'}
these foreign key rows are valid for the referencing Table:
{10,'tiny'} -- because a matching unique key row exists
{NULL,NULL} -- because the entire foreign key is NULL
{NULL,'tiny'} {10,NULL} {NULL,'huge'} {20,NULL} -- because, in each case,
one foreign key Column is NULL but the other matches the corresponding unique
Column in some row of the referenced Table
and these foreign key rows are invalid:
{10,'huge'} -- because no matching unique key row exists
{NULL,'big'} {30,NULL} -- because, although one foreign key Column is NULL.
the other does not match the value of the corresponding unique Column in any
row of the referenced Table
Tip
Use MATCH FULL
, or define all foreign key Columns with a NOT NULL
Constraint.
Referential Action¶
What happens if you UPDATE
a primary key? What happens if you DELETE
a
primary key? Neither should happen often, but if you must, remember that the
rule for primary/foreign key relationships is in terms of database states: “no
foreign key shall dangle”. There are two ways to get rid of a dangling key:
prevent it from happening in the first place, or compensate if it does happen.
You can do this by defining your FOREIGN KEY
Constraints with one ON
UPDATE
clause and/or one ON DELETE
clause, in any order. The optional
ON UPDATE
clause specifies the action you want your DBMS to take when an
UPDATE
operation on the referenced Table causes the FOREIGN KEY
Constraint to be violated. The optional ON DELETE
clause specifies the
action you want your DBMS to take when a DELETE
operation on the referenced
Table causes the FOREIGN KEY
Constraint to be violated. If you omit either
clause, both default to ON UPDATE NO ACTION
and ON DELETE NO ACTION
.
For example, these two SQL statements are equivalent:
CREATE TABLE Table_2 (
column_1 SMALLINT,
CONSTRAINT constraint_1 REFERENCES Table_1);
CREATE TABLE Table_2 (
column_1 SMALLINT,
CONSTRAINT constraint_1 REFERENCES Table_1
ON UPDATE NO ACTION ON DELETE NO ACTION);
Besides NO ACTION
, you may also specify these actions in the ON UPDATE
and ON DELETE
clauses: RESTRICT
, CASCADE
, SET NULL
and SET
DEFAULT
. To decide which to use, consider first what you would like to
happen. Should you be prevented from leaving a dangling reference – or should
you change the dangling reference too? (A dangling reference is a foreign key
that doesn’t point to a unique key any more, and it isn’t allowed in SQL.) If
you do change the dangling reference, should you be changing to (a) the same
value as the new unique key, (b) NULL
or (c) some other value? Or
should the change be a deletion? All these options are available. The action
taken by your DBMS in all cases depends on the definition of “matching rows”
for the FOREIGN KEY
Constraint: this, in turn, depends on the FOREIGN
KEY
Constraint’s MATCH
option.
For MATCH SIMPLE
and MATCH FULL
, given a row in the referenced Table,
every row in your referencing Table that contains a foreign key whose value
equals the value of that unique key, is a matching row. For MATCH PARTIAL
,
given a row in the referenced Table, every row in your referencing Table that
contains a foreign key with at least one non-null Column whose value equals the
value of that unique key, is a matching row – and a matching row that matches
only one row of the referenced Table is a unique matching row.
If you define a
FOREIGN KEY
Constraint withMATCH SIMPLE ON DELETE CASCADE
or withMATCH FULL ON DELETE CASCADE
, every time youDELETE
rows from the referenced Table, your DBMS will alsoDELETE
all matching rows from the referencing Table. If you define aFOREIGN KEY
Constraint withMATCH PARTIAL ON DELETE CASCADE
, every time youDELETE
rows from the referenced Table, your DBMS will alsoDELETE
all unique matching rows from the referencing Table.If you define a
FOREIGN KEY
Constraint withMATCH SIMPLE ON DELETE SET NULL
or withMATCH FULL ON DELETE SET NULL
, every time youDELETE
rows from the referenced Table, your DBMS will also set the entire foreign key in every matching row of the referencing Table toNULL
. If you define aFOREIGN KEY
Constraint withMATCH PARTIAL ON DELETE SET NULL
, every time youDELETE
rows from the referenced Table, your DBMS will also set the entire foreign key in every unique matching row of the referencing Table toNULL
.If you define a
FOREIGN KEY
Constraint withMATCH SIMPLE ON DELETE SET DEFAULT
or withMATCH FULL ON DELETE SET DEFAULT
, every time youDELETE
rows from the referenced Table, your DBMS will also set each Column of the foreign key in every matching row of the referencing Table to its default value. If you define aFOREIGN KEY
Constraint withMATCH PARTIAL ON DELETE SET DEFAULT
, every time youDELETE
rows from the referenced Table, your DBMS will also set each Column of the foreign key in every unique matching row of the referencing Table to its default value.If you define a
FOREIGN KEY
Constraint withMATCH SIMPLE ON DELETE RESTRICT
or withMATCH FULL ON DELETE RESTRICT
, every time you attempt toDELETE
rows from the referenced Table, your DBMS will check for matching rows in the referencing Table. If you define aFOREIGN KEY
Constraint withMATCH PARTIAL ON DELETE RESTRICT
, every time you attempt toDELETE
rows from the referenced Table, your DBMS will check for unique matching rows in the referencing Table. In either case, if any matching (or unique matching, as appropriate) rows exist, the operation will fail: your DBMS will return theSQLSTATE error 23001 "integrity constraint violation-restrict violation"
. AFOREIGN KEY
Constraint defined withON DELETE NO ACTION
acts essentially the same as one defined withON DELETE RESTRICT
.If you define a
FOREIGN KEY
Constraint withMATCH SIMPLE ON UPDATE CASCADE
or withMATCH FULL ON UPDATE CASCADE
, every time youUPDATE
a referenced Column, your DBMS will alsoUPDATE
the corresponding foreign key Column in all matching rows of the referencing Table to the same value. If you define aFOREIGN KEY
Constraint withMATCH PARTIAL ON UPDATE CASCADE
, every time youUPDATE
a referenced Column, your DBMS will alsoUPDATE
any corresponding non-null foreign key Column in every unique matching row of the referencing Table to the same value – provided that, for each referencing row changed, all rows of the referenced Table that considered that referencing row to be a matching row also have the same change made. If this isn’t the case, the operation will fail: your DBMS will return theSQLSTATE error 27000 "triggered data change violation"
.If you define a
FOREIGN KEY
Constraint withMATCH SIMPLE ON UPDATE SET NULL
, every time youUPDATE
a referenced Column, your DBMS will also set the corresponding foreign key Column in all matching rows of the referencing Table toNULL
. If you define aFOREIGN KEY
Constraint withMATCH FULL ON UPDATE SET NULL
, every time youUPDATE
a referenced Column, your DBMS will also set the entire foreign key in every matching row of the referencing Table toNULL
. If you define aFOREIGN KEY
Constraint withMATCH PARTIAL ON UPDATE SET NULL
, every time youUPDATE
a referenced Column, your DBMS will also set any corresponding non-null foreign key Column in every unique matching row of the referencing Table toNULL
.If you define a
FOREIGN KEY
Constraint withMATCH SIMPLE ON UPDATE SET DEFAULT
or withMATCH FULL ON UPDATE SET DEFAULT
, every time youUPDATE
a referenced Column, your DBMS will also set the corresponding foreign key Column in all matching rows of the referencing Table to its default value. If you define aFOREIGN KEY
Constraint withMATCH PARTIAL ON UPDATE SET DEFAULT
, every time youUPDATE
a referenced Column, your DBMS will also set any corresponding non-null foreign key Column in every unique matching row of the referencing Table to its default value.If you define a
FOREIGN KEY
Constraint withMATCH SIMPLE ON UPDATE RESTRICT
or withMATCH FULL ON UPDATE RESTRICT
, every time you attempt toUPDATE
a referenced Column, your DBMS will check for matching rows in the referencing Table. If you define aFOREIGN KEY
Constraint withMATCH PARTIAL ON UPDATE RESTRICT
, every time you attempt toUPDATE
a referenced Column, your DBMS will check for unique matching rows in the referencing Table. In either case, if any matching (or unique matching, as appropriate) rows exist, the operation will fail: your DBMS will return theSQLSTATE error 23001 "integrity constraint violation-restrict violation"
. AFOREIGN KEY
Constraint defined withON UPDATE NO ACTION
acts essentially the same as one defined withON UPDATE RESTRICT
.
For an example of the NO ACTION
/ RESTRICT
option, consider the
following SQL statements:
CREATE TABLE Table_1 (
column_1 SMALLINT DEFAULT 12
CONSTRAINT constraint_1 PRIMARY KEY(column_1) NOT DEFERRABLE);
CREATE TABLE Table_2 (
column_1 SMALLINT DEFAULT 15
CONSTRAINT constraint_2 FOREIGN KEY(column_1) REFERENCES Table_1
MATCH FULL
ON UPDATE NO ACTION ON DELETE NO ACTION
NOT DEFERRABLE);
INSERT INTO Table_1 VALUES(10);
INSERT INTO Table_1 VALUES(15);
INSERT INTO Table_2 VALUES(10);
For TABLE_1
and TABLE_2
, the effect of each of these SQL statements:
UPDATE Table_1 SET column_1=11 WHERE column_1=10;
UPDATE Table_2 SET column_1=11 where column_1=10;
INSERT INTO Table_2 VALUES(11);
is an error return, because the result in each case would be a value in
TABLE_2.COLUMN_1
that does not match some value in TABLE_1.COLUMN_1
.
Note
The action specified for the ON UPDATE
clause has no effect on
UPDATE
operations or INSERT
operations performed on the referencing
Table. Thus, an INSERT
operation that attempts to put a row into
TABLE_2
, or an UPDATE
operation that attempts to change a row of
TABLE_2
, will always fail if the resulting value in TABLE_2.COLUMN_1
does not match some value of TABLE_1.COLUMN_1
.)
The effect of this SQL statement:
DELETE FROM Table_1 WHERE column_1=10;
is also an error return, because deleting the applicable row from
TABLE_1
would leave TABLE_2
with a row containing a COLUMN_1
value that does not match any TABLE_1.COLUMN_1
value.
To summarize:
When an
UPDATE
operation attempts to update a non-null value in a Column that is referenced in aFOREIGN KEY
Constraint defined withON UPDATE NO ACTION
orON UPDATE RESTRICT
, theUPDATE
fails, regardless of theMATCH
option, if there are matching rows in the referencing Table.When a
DELETE
operation attempts to delete a row from a Table that is referenced in aFOREIGN KEY
Constraint defined withON DELETE NO ACTION
orON DELETE RESTRICT
, theDELETE
operation fails, regardless of theMATCH
option, if there are matching rows in the referencing Table.
For an example of the CASCADE
option, consider the following SQL
statements:
CREATE TABLE Table_1 (
column_1 SMALLINT DEFAULT 12
CONSTRAINT constraint_1 PRIMARY KEY(column_1) NOT DEFERRABLE);
CREATE TABLE Table_2 (
column_1 SMALLINT DEFAULT 15
CONSTRAINT constraint_2 FOREIGN KEY(column_1) REFERENCES Table_1
MATCH FULL
ON UPDATE CASCADE ON DELETE CASCADE
NOT DEFERRABLE);
INSERT INTO Table_1 VALUES(10);
INSERT INTO Table_1 VALUES(15);
INSERT INTO Table_2 VALUES(10);
For TABLE_1
and TABLE_2
, the effect of this SQL statement:
UPDATE Table_1 SET column_1=11 where column_1=10;
is that all values of TABLE_1.COLUMN_1
that are equal to 10
are set to
11
, with the same effect cascading down; that is, all values in
TABLE_2.COLUMN_1
that are equal to 10
are also set to 11
. And the
effect of this SQL statement:
DELETE FROM Table_1 WHERE column_1=10;
is that all applicable rows are deleted from TABLE_1
, with the same effect
cascading down; that is, all matching rows of TABLE_2
are also deleted.
To summarize:
When an
UPDATE
operation attempts to update a non-null value in a Column that is referenced in aFOREIGN KEY
Constraint defined withMATCH SIMPLE
orMATCH FULL
andON UPDATE CASCADE
, the referenced Column, and the corresponding referencing Column in all matching rows, are set to the new value. When anUPDATE
operation attempts to update a non-null value in a Column that is referenced in aFOREIGN KEY
Constraint defined withMATCH FULL
andON UPDATE CASCADE
, the referenced Column, and the corresponding referencing Column in all unique matching rows where the referencing Column contains a non-null value, are set to the new value. Unique matching rows with a referencing Column that contains the null value are not updated.When a
DELETE
operation attempts to delete a row from a Table that is referenced in aFOREIGN KEY
Constraint defined withMATCH SIMPLE
orMATCH FULL
andON DELETE CASCADE
, the applicable row, and all matching rows, are deleted. When aDELETE
operation attempts to delete a row from a Table that is referenced in aFOREIGN KEY
Constraint defined withMATCH PARTIAL
andON DELETE CASCADE
, the applicable row, and all unique matching rows, are deleted.
For an example of the SET NULL
option, consider the following SQL
statements:
CREATE TABLE Table_1 (
column_1 SMALLINT DEFAULT 12
CONSTRAINT constraint_1 PRIMARY KEY(column_1) NOT DEFERRABLE);
CREATE TABLE Table_2 (
column_1 SMALLINT DEFAULT 15
CONSTRAINT constraint_2 FOREIGN KEY(column_1) REFERENCES Table_1
MATCH FULL
ON UPDATE SET NULL ON DELETE SET NULL
NOT DEFERRABLE);
INSERT INTO Table_1 VALUES(10);
INSERT INTO Table_1 VALUES(15);
INSERT INTO Table_2 VALUES(10);
For TABLE_1
and TABLE_2
, the effect of this SQL statement:
UPDATE Table_1 SET column_1=11 where column_1=10;
is that all values of TABLE_1.COLUMN_1
that are equal to 10
are set to
11
, and that all values in TABLE_2.COLUMN_1
that are equal to 10
are set to the null value. (If TABLE_2.COLUMN_1
did not allow null values,
the UPDATE
statement would fail.) And the effect of this SQL statement:
DELETE FROM Table_1 WHERE column_1=10;
is that all applicable rows are deleted from TABLE_1
, and that all values
in TABLE_2.COLUMN_1
that are equal to 10
are set to the null value. (If
TABLE_2.COLUMN_1
did not allow null values, the DELETE
statement would
fail.)
To summarize:
When an
UPDATE
operation attempts to update a non-null value in a Column that is referenced in aFOREIGN KEY
Constraint defined withMATCH SIMPLE
andON UPDATE SET NULL
, the referenced Column is set to the new value and the corresponding referencing Column in all matching rows is set to the null value. When anUPDATE
operation attempts to update a non-null value in a Column that is referenced in aFOREIGN KEY
Constraint defined withMATCH FULL
andON UPDATE SET NULL
, the referenced Column is set to the new value and every referencing Column (not just the corresponding Column) in all matching rows is set to the null value. When anUPDATE
operation attempts to update a non-null value in a Column that is referenced in aFOREIGN KEY
Constraint defined withMATCH PARTIAL
andON UPDATE SET NULL
, the referenced Column is set to the new value and the corresponding referencing Column in all unique matching rows is set to the null value.When a
DELETE
operation attempts to delete a row from a Table that is referenced in aFOREIGN KEY
Constraint defined withMATCH SIMPLE
orMATCH FULL
andON DELETE SET NULL
, the applicable row is deleted and, for all matching rows, each referencing Column is set to the null value. When aDELETE
operation attempts to delete a row from a Table that is referenced in aFOREIGN KEY
Constraint defined withMATCH PARTIAL
andON DELETE SET NULL
, the applicable row is deleted, and, for all unique matching rows, each referencing Column is set to the null value.
For an example of the SET DEFAULT
option, consider the following SQL
statements:
CREATE TABLE Table_1 (
column_1 SMALLINT DEFAULT 12
CONSTRAINT constraint_1 PRIMARY KEY(column_1) NOT DEFERRABLE);
CREATE TABLE Table_2 (
column_1 SMALLINT DEFAULT 15
CONSTRAINT constraint_2 FOREIGN KEY(column_1) REFERENCES Table_1
MATCH FULL
ON UPDATE SET DEFAULT ON DELETE SET DEFAULT
NOT DEFERRABLE);
INSERT INTO Table_1 VALUES(10);
INSERT INTO Table_1 VALUES(15);
INSERT INTO Table_2 VALUES(10);
For TABLE_1
and TABLE_2
, the effect of this SQL statement:
UPDATE Table_1 SET column_1=11 where column_1=10;
is that all values of TABLE_1.COLUMN_1
that are equal to 10
are set to
11
, and that all values of TABLE_2.COLUMN_1
that are equal to 10
are set to COLUMN_1
's default value, 15
. (If no row existed where the
value of TABLE_1.COLUMN_1
was 15
, the UPDATE
statement would fail.)
And the effect of this SQL statement:
DELETE FROM Table_1 WHERE column_1=10;
is that all applicable rows are deleted from TABLE_1
and that all values in
TABLE_2.COLUMN_1
that are equal to 10
are set to COLUMN_1
's
default value, 15
. (If no row existed where the value of
TABLE_1.COLUMN_1
was 15
, the DELETE
statement would fail.)
To summarize:
When an
UPDATE
operation attempts to update a non-null value in a Column that is referenced in aFOREIGN KEY
Constraint defined withMATCH SIMPLE
orMATCH FULL
andON UPDATE SET DEFAULT
, the referenced Column is set to the new value and the corresponding referencing Column in all matching rows is set to its default value. When anUPDATE
operation attempts to update a non-null value in a Column that is referenced in aFOREIGN KEY
Constraint defined withMATCH PARTIAL
andON UPDATE SET DEFAULT
, the referenced Column is set to the new value and the corresponding referencing Column in all unique matching rows is set to its default value.When a
DELETE
operation attempts to delete a row from a Table that is referenced in aFOREIGN KEY
Constraint defined withMATCH SIMPLE
orMATCH FULL
andON DELETE SET DEFAULT
, the applicable row is deleted, and, for all matching rows, each referencing Column is set to its default value. When aDELETE
operation attempts to delete a row from a Table that is referenced in aFOREIGN KEY
Constraint defined withMATCH PARTIAL
andON DELETE SET DEFAULT
, the applicable row is deleted, and, for all unique matching rows, each referencing Column is set to its default value.
Note
It is not possible to update the same Column more than once in a single
SQL statement. If such an operation is attempted, the statement will fail:
your DBMS will return the SQLSTATE error 27000 "triggered data change
violation"
.
Note
All rows that are to be deleted by an SQL statement are effectively deleted at the end of that statement’s execution, prior to the checking of any integrity constraints.
If you want to restrict your code to Core SQL, don’t define your FOREIGN
KEY
Constraints with a MATCH
clause, an ON UPDATE
clause or an ON
DELETE
clause.
Constraint_type – NOT NULL Constraint¶
A NOT NULL
Constraint is a <Column Constraint>, defining a rule that
constrains a key to non-null values only. The required syntax for a NOT
NULL
Constraint is:
NOT NULL <Column Constraint> ::=
[ CONSTRAINT <Constraint name> ]
<Column name> NOT NULL
[ <constraint attributes> ]
A Column may be constrained by no more than one NOT NULL
Constraint, which
specifies a rule that the Column may contain only non-null values. Here is an
example of a NOT NULL
Constraint definition:
CREATE TABLE Table_1 (
column_1 SMALLINT
DEFAULT 15
CONSTRAINT constraint_1 NOT NULL DEFERRABLE INITIALLY IMMEDIATE);
-- defines a NOT NULL <Column Constraint> in CREATE TABLE
Once created, a NOT NULL
<Column Constraint> logically becomes a CHECK
<Table Constraint>. The <Column Constraint> in this SQL statement:
CREATE TABLE Table_1 (
column_1 SMALLINT NOT NULL);
is therefore equivalent to the <Table Constraint> in this SQL statement:
CREATE TABLE Table_1 (
column_1 SMALLINT,
CHECK (column_1 IS NOT NULL));
A NOT NULL
Constraint makes it impossible to COMMIT
any operation that
would cause the Column to which it belongs to contain any NULL
s. A NOT
NULL
Constraint is violated if it is FALSE
for any row of the Table it
belongs to. Consider this SQL statement:
CREATE TABLE Table_1 (
column_1 SMALLINT CONSTRAINT constraint_1 NOT NULL,
column_2 VARCHAR(4));
For this example, CONSTRAINT_1
would be violated if you tried to make
COLUMN_1
contain NULL
.
Constraint_type – CHECK Constraint¶
A CHECK
Constraint is either a <Table Constraint>, a <Column Constraint> or
a <Domain Constraint> and defines a rule that constrains the set of valid
values for a Base table. The required syntax for a CHECK
Constraint is:
CHECK <Table Constraint> ::=
[ CONSTRAINT <Constraint name> ]
CHECK (search condition)
[ <constraint attributes> ]
CHECK <Column Constraint> ::=
[ CONSTRAINT <Constraint name> ]
<Column name> CHECK (search condition)
[ <constraint attributes> ]
CHECK <Domain Constraint> ::=
[ CONSTRAINT <Constraint name> ]
CHECK (VALUE search condition)
[ <constraint attributes> ]
A Base table may be constrained by zero or more CHECK
Constraints, which
specify a rule that a group of one or more Columns of a Table may contain only
those values that fall into the set defined by the rule – that is, a CHECK
Constraint is satisfied if its search condition evaluates to TRUE
or to
UNKNOWN
for all rows within its scope.
Here are some examples of CHECK
Constraint definitions:
CREATE TABLE Table_1 (
column_1 SMALLINT,
CONSTRAINT constraint_1 CHECK(column_1<400) NOT DEFERRABLE);
-- defines a CHECK <Table Constraint> in CREATE TABLE
CREATE TABLE Table_1 (
column_1 SMALLINT
CONSTRAINT constraint_1 CHECK(column_1<400) NOT DEFERRABLE,
column_2 CHAR(5));
-- defines a CHECK <Column Constraint> in CREATE TABLE
ALTER TABLE Table_2 ADD CONSTRAINT constraint_2
CHECK(column_1>100 OR column_2='hello')
NOT DEFERRABLE INITIALLY IMMEDIATE;
-- defines a CHECK <Table Constraint> in ALTER TABLE
CREATE DOMAIN domain_1 AS SMALLINT
CONSTRAINT constraint_1 CHECK(VALUE IN 50,100,150)
DEFERRABLE INITIALLY DEFERRABLE;
-- defines a CHECK <Domain Constraint> in CREATE DOMAIN
ALTER DOMAIN domain_1 ADD CONSTRAINT constraint_2
CHECK(VALUE IS NOT NULL);
-- defines a CHECK <Domain Constraint> in ALTER DOMAIN
CHECK
<Column Constraint>s may be defined only in a CREATE TABLE
statement and must be for a single Column only. CHECK
<Table Constraint>s
may be defined in a CREATE TABLE
or an ALTER TABLE
statement and may be
for one or more Columns. CHECK
<Domain Constraint>s may be defined in a
CREATE DOMAIN
or an ALTER DOMAIN
statement and must contain a search
condition that uses the <value specification> VALUE
; valid only in a
<Domain Constraint> (the <data type> of a given instance of VALUE
is the
<data type> of the Domain that the <Domain Constraint> belongs to). A <Domain
Constraint>’s search condition may not be a recursive search condition (that
is, it may not refer, either directly or indirectly, to the Domain that the
<Domain Constraint> belongs to).
Once created, a CHECK
<Column Constraint> logically becomes a CHECK
<Table Constraint>. The <Column Constraint> in this SQL statement:
CREATE TABLE Table_1 (
column_1 SMALLINT CHECK(column_1<400));
is therefore equivalent to the <Table Constraint> in this SQL statement:
CREATE TABLE Table_1 (
column_1 SMALLINT,
CHECK(column_1<400));
A CHECK
Constraint’s search condition may specify any conditional
expression, subject to the following rules:
The search condition may not contain (a) a <target specification> or (b) a set function (i.e.:
COUNT
,AVG
,MAX
,MIN
orSUM
) unless the set function is contained in a subquery or (c) any of these functions:CURRENT_PATH
,CURRENT_USER
,SESSION_USER
,SYSTEM_USER
,USER
,CURRENT_DATE
,CURRENT_TIME
,CURRENT_TIMESTAMP
,LOCALTIME
orLOCALTIMESTAMP
or (d) any query that is possibly non-deterministic, as defined earlier in this chapter.The search condition may not invoke a non-deterministic routine, or a routine which possibly modifies SQL-data.
If a
CHECK
Constraint belongs to a persistent Base table or to a Domain, its search condition may not refer to any temporary Tables.If a
CHECK
Constraint belongs to aGLOBAL TEMPORARY
Base table, its search condition may refer only toGLOBAL TEMPORARY
Base Tables. If aCHECK
Constraint belongs to a createdLOCAL TEMPORARY
Base table, its search condition may refer only toGLOBAL TEMPORARY
Base Tables or to createdLOCAL TEMPORARY
Base tables. If aCHECK
Constraint belongs to a declaredLOCAL TEMPORARY
Base table, its search condition may not refer to any persistent Base Tables.If a
CHECK
Constraint belongs to a temporary Table defined withON COMMIT PRESERVE ROWS
, its search condition may not contain a subquery that refers to a temporary Table defined withON COMMIT DELETE ROWS
.
[Obscure Rule] If a CHECK
Constraint’s search condition can’t be
represented in INFORMATION_SCHEMA
without truncation, your DBMS will return
the SQLSTATE warning 01009 "warning-search condition too long for information
schema"
.
Privileges¶
In order to create a CHECK
Constraint, the <AuthorizationID> that owns the
Schema to which the Constraint will belong must be the current
<AuthorizationID> and must have the REFERENCES
Privilege on every Column
that is explicitly named in the CHECK
Constraint’s search condition. If the
search condition doesn’t explicitly name any Columns, the current
<AuthorizationID> must have the REFERENCES
Privilege on at least one Column
of every Table referred to in the search condition.
A CHECK
Constraint makes it impossible to COMMIT
any operation that
would cause the Constraint’s search condition to evaluate to FALSE
. (This
means, of course, that if the condition evaluates to TRUE
or to
UNKNOWN
, the Constraint is satisfied.) Thus, for example, the Constraint
defined in this CREATE TABLE
statement is violated if any row of
TABLE_1
contains a COLUMN_1
value that is greater than 99
:
CREATE TABLE Table_1 (
column_1 SMALLINT,
column_2 VARCHAR(4),
CONSTRAINT constraint_1 CHECK(column_1<100) NOT DEFERRABLE);
This SQL statement would therefore violate CONSTRAINT_1
:
INSERT INTO Table_1 VALUES (105);
because a search condition that evaluates to FALSE
violates the Constraint.
Both of these SQL statements, however, would satisfy CONSTRAINT_1
:
INSERT INTO Table_1 VALUES (-30);
-- a search condition that evaluates to TRUE satisfies the Constraint
INSERT INTO Table_1 VALUES (NULL);
-- NULL is allowed; a search condition that evaluates to UNKNOWN satisfies
the Constraint
The first use of a CHECK
<Table Constraint> is to restrict what range of
values is allowed in a Column, for example:
ALTER TABLE Table_1 ADD CONSTRAINT constraint_1
CHECK(column_1 BETWEEN 5 AND 9) NOT DEFERRABLE;
You’ll often see Column values restrained like this; it’s a feature in dialog
boxes. The second use of a CHECK
<Table Constraint> is to see that two
Columns within the same Table agree with each other, for example:
ALTER TABLE Films ADD CONSTRAINT constraint_1
CHECK(film_type <> 'Action' OR star = 'Stallone') NOT DEFERRABLE;
The third use is to find out whether some relation is true between a row in one Table, and a row in another Table or a different row in the same Table, for example:
ALTER TABLE Table_1 ADD CONSTRAINT constraint_1
CHECK (column_1 > (SELECT MAX(column_2) FROM Table_2) NOT DEFERRABLE;
This sort of thing was once illegal, but in modern variations of SQL, you’ll
see inter-table Constraint references on an occasional basis. The fourth use
of a CHECK
<Table Constraint> is documentary, for example:
ALTER TABLE Table_1 ADD CONSTRAINT comment_1
CHECK ('this is a comment ...' IS NOT NULL)
DEFERRABLE INITIALLY DEFERRED;
Most DBMSs allow comments to be added to the metadata some other way, so this final use is rare.
For <Domain Constraint>s, the general idea is that object X doesn’t belong in type Y or, to put it positively: certain things go in certain classes. These two examples both express the theme “values based on this Domain must not be space”:
CREATE DOMAIN domain_1 AS CHAR(1)
CONSTRAINT constraint_1 CHECK (VALUE <> ' ');
CREATE DOMAIN domain_2 AS CHAR(1);
ALTER DOMAIN domain_2 ADD CONSTRAINT constraint_2
CHECK (VALUE <> ' ');
In a <Domain Constraint>’s CHECK
condition, the word VALUE
is a
placeholder: your DBMS replaces it with the appropriate <Column name> when
checking the Constraint. The second of these two SQL statements would force a
Constraint check:
CREATE TABLE Table_1 (
column_1 domain_1,
column_2 CHAR(10));
-- makes a Column based on DOMAIN_1
INSERT INTO Table_1
VALUES (' ',
'bob');
-- fails; CONSTRAINT_1 stops it
A <Domain Constraint> applies to every Column that’s defined on the Domain, now or in the future. This makes sense since it’s rare that a Column is in a Domain all on its own – and if the name of a manager is subject to some Constraint (must be alphabetic, say), then surely the employees’ names and spouses’ names should be subject to the same Constraint. There’s a case for suggesting that “data type checking” is just a vague form of “<Domain Constraint> checking”; the error messages are different, but the point is the same – you are restricted as to what you can put in.
If you want to restrict your code to Core SQL, don’t use a subquery in a
CHECK
Constraint’s search condition. Also, for Core SQL, the REFERENCES
Privilege isn’t needed to create a CHECK
Constraint.
CREATE ASSERTION Statement¶
The CREATE ASSERTION
statement names a new Constraint and defines the
Constraint’s deferral mode, initial constraint check time and its CHECK
search condition. The required syntax for the CREATE ASSERTION
statement
is:
CREATE ASSERTION <Constraint name>
CHECK (search condition)
[ <constraint attributes> ]
CREATE ASSERTION
defines a new rule that will constrain the set of valid
values for one or more Base tables. An Assertion is owned by the Schema it
belongs to.
The <Constraint name> identifies the Assertion and the Schema that it belongs to. A <Constraint name> that includes an explicit <Schema name> qualifier belongs to the Schema named. A <Constraint name> that does not include an explicit <Schema name> qualifier belongs to the SQL-session default Schema. (In both cases, that Schema must, of course, own the Tables for which the Assertion is defined.) The <Constraint name> must be unique (for all Constraints and Assertions) within the Schema that owns it.
If CREATE ASSERTION
is part of a CREATE SCHEMA
statement, the
<Constraint name>, if explicitly qualified, must include the <Schema name> of
the Schema being created; that is, it isn’t possible to create an Assertion
belonging to a different Schema from within CREATE SCHEMA
. For example,
this SQL statement will not return an error because the <Constraint name> will
default to include the qualifying <Schema name>:
CREATE SCHEMA bob
CREATE TABLE Table_1 (column_1 SMALLINT)
CREATE ASSERTION constraint_1
CHECK ((SELECT AVG(column_1) FROM Table_1 >40) NOT DEFERRABLE;
-- creates an Assertion called BOB.CONSTRAINT_1 in Schema BOB
This SQL statement will not return an error either because the <Constraint name> explicitly includes a qualifying <Schema name> that matches the name of the Schema being created:
CREATE SCHEMA bob
CREATE TABLE bob.Table_1 (column_1 SMALLINT)
CREATE ASSERTION bob.constraint_1
CHECK ((SELECT AVG(column_1) FROM Table_1 >40) NOT DEFERRABLE;
-- creates an Assertion called BOB.CONSTRAINT_1 in Schema BOB
But this SQL statement will return an error because the <Constraint name> explicitly includes a qualifying <Schema name> that is different from the name of the Schema being created:
CREATE SCHEMA bob
CREATE TABLE Table_1 (column_1 SMALLINT)
CREATE ASSERTION sam.constraint_1
CHECK ((SELECT AVG(column_1) FROM Table_1 >40) NOT DEFERRABLE;
-- tries to create a Constraint belonging to Schema SAM inside Schema BOB;
illegal syntax
If CREATE ASSERTION
is executed as a stand-alone SQL statement, the current
<AuthorizationID> must either be the owner of the Schema that this new
Constraint belongs to, or the Schema’s owner must be a Role that the current
<AuthorizationID> may use. That is, only the owner of a Schema can create
Constraints for that Schema.
An Assertion’s CHECK
search condition may specify any conditional
expression (it will almost inevitably contain the <keyword> EXISTS
, the
<keyword> UNIQUE
or a set function), subject to the following rules (note
that these rules are slightly different from the rules given earlier for a
CHECK
Constraint’s search condition):
The search condition may not contain a <host parameter name>, an <SQL parameter name>, any of these functions:
CURRENT_PATH
,CURRENT_USER
,SESSION_USER
,SYSTEM_USER
,USER
,CURRENT_DATE
,CURRENT_TIME
,CURRENT_TIMESTAMP
,LOCALTIME
orLOCALTIMESTAMP
or any query that is possibly non-deterministic, as defined earlier in this chapter.The search condition may not invoke a non-deterministic routine, or a routine which possibly modifies SQL-data.
The search condition may not refer to any temporary Tables.
[Obscure Rule] If an Assertion’s CHECK
search condition can’t be
represented in INFORMATION_SCHEMA
without truncation, your DBMS will return
the SQLSTATE warning 01009 "warning-search condition too long for information
schema"
.
An Assertion makes it impossible to COMMIT
any operation that would cause
the Constraint’s search condition to evaluate to FALSE
. (This means, of
course, that if the condition evaluates to TRUE
or to UNKNOWN
, the
Constraint is satisfied.) Thus, for example, for these two SQL statements:
CREATE TABLE Table_1 (
column_1 SMALLINT,
column_2 VARCHAR(4));
CREATE ASSERTION constraint_1
CHECK ((SELECT AVG(column_1) FROM Table_1 >40) NOT DEFERRABLE;
CONSTRAINT_1
is violated if the average of the TABLE_1.COLUMN_1
values
is less than 41
. Assume that TABLE_1
contains one row, where
COLUMN_1
contains 42
. This SQL statement would then violate
CONSTRAINT_1
:
INSERT INTO Table_1 VALUES (38);
because a search condition that evaluates to FALSE
violates the Constraint.
Both of these SQL statements, however, would satisfy CONSTRAINT_1
:
INSERT INTO Table_1 VALUES (100);
-- a search condition that evaluates to TRUE satisfies the Constraint
INSERT INTO Table_1 VALUES (NULL);
-- NULL is allowed; a search condition that evaluates to UNKNOWN satisfies
the Constraint
The <constraint attributes> clause of CREATE ASSERTION
is as defined in
“Constraint definition”, earlier in this chapter. If you omit the clause, the
Constraint defaults to a NOT DEFERRABLE INITIALLY IMMEDIATE
Constraint.
We’ve already said that an Assertion constrains Base tables. The reason
they’re not <Table Constraint>s – or the reason you’ll sometimes want to use
Assertions rather than <Table Constraint>s – lies in the difference between
the way an Assertion is checked and the way a <Table Constraint> is checked:
an Assertion is checked once, while a <Table Constraint> is checked once for
each row in the Table. This difference doesn’t affect efficiency: modern DBMSs
are capable of figuring out when they really need to check, so we note this
only as a guide to what “effectively” happens. But consider what it means to
you: Assume a Constraint that, in English, is: “There must be at least one row
in Table TABLE_1
". If you try to implement this requirement as a <Table
Constraint>, for example with this SQL statement:
ALTER TABLE Table_1 ADD CONSTRAINT constraint_1
CHECK (0 <> (SELECT COUNT(*) FROM Table_1));
you’ll find it won’t work: since a <Table Constraint> is checked once “for each
row”, and there are no rows, the check never happens if you leave the
TABLE_1
empty. To make it work, create an Assertion to ensure the condition
is checked at least once. For example:
CREATE ASSERTION constraint_1
CHECK (0 <> (SELECT COUNT(*) FROM Table_1));
It’s always a good idea to consider creating an Assertion when you see a
SELECT
condition. Here’s another example: consider a Constraint that, in
English, is: “We can only hold picnics if there’s money.” In this case, you
could use this <Table Constraint> and it would work:
ALTER TABLE Picnics ADD CONSTRAINT constraint_1
CHECK (EXISTS (SELECT * FROM Accounts WHERE balance > 0));
But CONSTRAINT_1
, as defined, is misleading – the SQL statement suggests
that there’s a Constraint on the PICNICS
Table. There is, of course, but
there’s a Constraint on the ACCOUNTS
Table too and this isn’t immediately
clear. If you define the same condition with CREATE ASSERTION
, you’ll be
signalling that there’s more to it; for example:
CREATE ASSERTION Picnic_Account_Check
CHECK (NOT EXISTS (SELECT * FROM Picnics) OR
EXISTS (SELECT * FROM Accounts WHERE balance > 0));
If you want to restrict your code to Core SQL, don’t use the CREATE
ASSERTION
statement.
Interlocking References¶
An example of an interlocking reference is:
Every Employee must be in a department.
Every Department must have at least one employee.
This is an “interlock” problem because there must be a reference from the
EMPLOYEES
Table to the DEPARTMENTS
Table, as well as a reference going
the other way: from the DEPARTMENTS
Table to the EMPLOYEES
Table. Here
are the Table definitions:
CREATE TABLE Employees (
emp_id INT,
dept_id INT,
CONSTRAINT emp_constraint_1
FOREIGN KEY (dept_id) REFERENCES Departments NOT DEFERRABLE);
CREATE TABLE Departments (
dept_id,
CONSTRAINT dept_constraint_1
PRIMARY KEY (dept_id) NOT DEFERRABLE,
CONSTRAINT dept_constraint_2
CHECK (dept_id IN (SELECT * FROM Employees) NOT DEFERRABLE);
-- this CHECK clause illustrates the normal way to make a "foreign
reference" to a "key" which is not unique or primary
In this example, the CREATE TABLE Employees...
statement will return an
error because it refers to the DEPARTMENTS
Table before that Table has been
created. Interchanging the statement order wouldn’t help, because then the
CREATE TABLE Departments...
statement will return an error because it
refers to the EMPLOYEES
Table before that Table has been created. You could
put both Table definitions inside a CREATE SCHEMA
statement, but that isn’t
a general solution. To solve the problem, split the CREATE TABLE
statements
up like this:
CREATE TABLE Employees (
emp_id INT,
dept_id INT);
CREATE TABLE Departments (
dept_id INT);
ALTER TABLE Departments ADD CONSTRAINT dept_constraint_1
PRIMARY KEY (dept_id) NOT DEFERRABLE;
ALTER TABLE Employees ADD CONSTRAINT emps_constraint_1
FOREIGN KEY (dept_id) REFERENCES Departments NOT DEFERRABLE;
ALTER TABLE Departments ADD CONSTRAINT dept_constraint_2
CHECK (dept_id IN (SELECT * FROM Employees) NOT DEFERRABLE);
Not only does this second attempt resolve the legalities, it also looks better: it’s easier to read several short statements, rather than a few long statements. Anyway, with this method, there’s no problem defining interlocked Tables. However, there’s still a problem with putting data into them. For example, assuming no data exists in either Table, this SQL statement:
INSERT INTO Employees VALUES (1, 1);
will cause EMPS_CONSTRAINT_1
to fail because there are no departments. And
this SQL statement:
INSERT INTO Departments VALUES (1);
will cause DEPT_CONSTRAINT_2
to fail because there are no employees.
There are three solutions to this problem:
Solution #1
Use SQL3 features. There are, in fact, a few ways to do this with SQL3 – the clearest would be to join the two Tables and update the join.
Solution #2
Take advantage of the fact that NULL
matches anything. Begin with the
assumption that the DEPARTMENTS
Table is not empty, presumably because you
used Solution #1 for some other department. Then execute these SQL statements:
INSERT INTO Employees VALUES (1, NULL);
INSERT INTO Departments VALUES (1);
UPDATE Employees SET dept_id = 1 WHERE emp_id = 1;
You can sometimes use NULL
where you can’t use anything else – so insert a
NULL
as a temporary placeholder, and replace it when both rows exist.
Solution #3
Change the initial setup so that all Constraints are deferred. For example:
CREATE TABLE Employees (
emp_id INT,
dept_id INT);
CREATE TABLE Departments (
dept_id INT);
ALTER TABLE Departments ADD CONSTRAINT dept_constraint_1
PRIMARY KEY (dept_id) DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE Employees ADD CONSTRAINT emps_constraint_1
FOREIGN KEY (dept_id) REFERENCES Departments
DEFERRABLE INITIALLY DEFERRED;
ALTER TABLE Departments ADD CONSTRAINT dept_constraint_2
CHECK (dept_id IN (SELECT * FROM Employees)
DEFERRABLE INITIALLY DEFERRED);
This method causes the INSERT
problem to disappear because no checks will
occur at INSERT
time. Thus, these INSERT
statements would now work
without returning an error:
INSERT INTO Employees VALUES (1, 1);
INSERT INTO Departments VALUES (1);
SET CONSTRAINTS ALL IMMEDIATE;
-- recommended once the INSERTs are done
We like Solution #3 best because it lacks dependence on SQL3 only features or on tricks.
Dropping Constraints¶
Dropping a Constraint is straightforward, providing that you know the
<Constraint name> – that’s why we recommend that you explicitly give every
Constraint a name when you make it (even a NOT NULL
Constraint). <Table
Constraint>s and <Column Constraint>s are dropped using the DROP CONSTRAINT
<Constraint name> clause of the ALTER TABLE
statement, <Domain Constraint>s
are dropped using the DROP CONSTRAINT
<Constraint name> clause of the
ALTER DOMAIN
statement and Assertions are dropped with the DROP
ASSERTION
statement.
DROP ASSERTION Statement¶
The DROP ASSERTION
statement destroys an Assertion. The required syntax for
the DROP ASSERTION
statement is:
DROP ASSERTION <Constraint name>
The <Constraint name> must identify an existing Assertion whose owner is either the current <AuthorizationID> or a Role that the current <AuthorizationID> may use. That is, only the <AuthorizationID> that owns the Schema may drop its Assertions. If <Constraint name> does not include an explicit <Schema name> qualifier, the Assertion must belong to the SQL-session default Schema.
The effect of DROP ASSERTION
<Constraint name>, e.g.:
DROP ASSERTION constraint_1;
is that the Assertion named CONSTRAINT_1
will be destroyed, providing that
CONSTRAINT_1
is not referred to in any SQL routine or in any Trigger. If
the Assertion’s CHECK
search condition includes a NOT NULL
condition
that causes one or more Columns to have the “known not nullable” nullability
characteristic, then the affected Columns’ nullability characteristic becomes
“possibly nullable” (unless some other Constraint also constrains them to non-
null values).
If you want to restrict your code to Core SQL, don’t use the DROP ASSERTION
statement.
Dialects¶
In most DBMSs, it’s common that the UNIQUE
specification is not supported,
but you’ll often see a (non-SQL) CREATE UNIQUE INDEX
statement that gives
you the same functionality instead.
Some DBMSs reportedly don’t support the FOREIGN KEY
<Column Constraint>
syntax, but do allow foreign keys to be defined as <Table Constraint>s.