See and
for details on those statements.
A CONSTRAINT can be one of the following:
- Column-level constraintsConstraintscolumn-level
A columnLevelConstraint
Column-level constraints refer to a single column
in the table and do not specify a column name (except check constraints).
They refer to the column that they follow.
- Table-level constraintsConstraintstable-level
A tableLevelConstraint
Table-level constraints refer to one or more columns
in the table. Table-level constraints specify the names of the columns to
which they apply. Table-level CHECK constraints can refer to 0 or more columns
in the table.
Column-level constraintsused in CREATE
TABLE statementConstraintscolumn-levelColumn
constraints include:
- NOT NULL
Specifies that this column cannot hold NULL values (constraints
of this type are not nameable).
- PRIMARY KEYPrimary keyscreatingadding
Specifies the column that uniquely identifies a row in the table. The
identified columns must be defined as NOT NULL.
If you attempt
to add a primary key using ALTER TABLE and any of the columns included in
the primary key contain null values, an error will be generated and the primary
key will not be added. See for
more information.
- UNIQUE
Specifies that values in the column must be unique.
- FOREIGN KEY
Specifies that the values in the column must correspond
to values in a referenced primary key or unique key column or that they are
NULL.
- CHECK
Specifies rules for values in the column.
Table constraintsused in CREATE TABLE statementConstraintstable-levelTable constraints include:
- PRIMARY KEYPrimary keyscreatingadding
Specifies
the column or columns that uniquely identify a row in the table. NULL values
are not allowed.
- UNIQUE
Specifies that values in the columns must be unique.
- FOREIGN KEY
Specifies that the values in the columns must correspond
to values in referenced primary key or unique columns or that they are NULL.
If
the foreign key consists of multiple columns, and any column is NULL,
the whole key is considered NULL. The insert is permitted no matter what is
on the non-null columns.
- CHECK
Specifies a wide range of rules for values in the table.
Constraintsdifferences between column-level
and table-levelColumn constraints and table constraints
have the same function; the difference is in where you specify them. Table
constraints allow you to specify more than one column in a PRIMARY KEY, UNIQUE,
CHECK, or FOREIGN KEY constraint definition. Column-level constraints (except
for check constraints) refer to only one column.
A constraint operates with the privileges of the owner of the constraint.
See "Using SQL standard authorization" and "Privileges on views, triggers, and
constraints" in the for
details.
Deferrable constraints
Constraints can be deferred, meaning that
does not check
constraints immediately. By default, a constraint is checked as soon as a
statement completes. Deferrable constraints allow temporary breaches of
constraints for more flexible insert and update operations.
Deferrable constraints are available only after a database has been fully
upgraded to
Release 10.11 or higher. (See "Upgrading a database" in the
for more information.) They
cannot be used in a database that is at Release 10.10 or lower.
When a deferrable constraint's constraint mode is DEFERRED before execution
of a statement starts, the checking of the constraint does not take place at the
end of the statement execution as usual, but only when it is explicitly or
implicitly requested using one of the following mechanisms:
- The transaction ends (a commit operation takes place)
- A SET CONSTRAINTS statement which
sets the constraint mode to IMMEDIATE is executed
- A return from a stored procedure or function reverts the constraint mode to
IMMEDIATE
The point at which a deferrable constraint is checked is referred to as the
deferred checking time.
If the constraint mode of a constraint is IMMEDIATE before a call to a stored
procedure or function, and the stored procedure or function sets the constraint
mode of that constraint to DEFERRED, the constraint mode is implicitly reset to
IMMEDIATE on return from the stored procedure. This happens because the
constraint mode is pushed on a stack when we enter the stored procedure or
function (as are other session state variables, like the current role). If a
constraint violation happens as a result, the transaction is rolled back and an
exception is thrown.
See for
information about the behavior of deferrable foreign keys.
Primary key constraints Primary
key constraintsA primary
key defines the set of columns that uniquely identifies rows in a table.
When
you create a primary key constraint, none of the columns included in the primary
key can have NULL constraints; that is, they must not permit NULL values.
ALTER
TABLE ADD PRIMARY KEY allows you to include existing columns in a primary
key if they were first defined as NOT NULL. NULL values are not allowed. If
the column(s) contain NULL values, the system will not add the primary key
constraint. See for more information.
A
table can have at most one PRIMARY KEY constraint.
Unique constraintsUnique constraints
A UNIQUE constraint defines a set of columns that uniquely identify rows in a
table only if all the key values are not NULL. If one or more key parts are
NULL, duplicate keys are allowed.
For example, if there is a UNIQUE constraint on col1 and
col2 of a table, the combination of the values held by
col1 and col2 will be unique as long
as these values are not NULL. If one of col1 and
col2 holds a NULL value, there can be another identical row in
the table.
A table can have multiple UNIQUE constraints.
Foreign key constraints Foreign keysReferential
integrityvia foreign keysForeign keys provide
a way to enforce the referential integrity of a database. A foreign key is
a column or group of columns within a table that references a key in some
other table (or sometimes, though rarely, the same table). The foreign key
must always include the columns of which the types exactly match those in
the referenced primary key or unique constraint.
For a table-level
foreign key constraint in which you specify the columns in the table that
make up the constraint, you cannot use the same column more than once.
If
there is a column list in the ReferencesSpecification (a list of columns
in the referenced table), it must correspond either to a unique constraint
or to a primary key constraint in the referenced table. The ReferencesSpecification can
omit the column list for the referenced table if that table has a declared
primary key.
If there is no column list in the ReferencesSpecification and
the referenced table has no primary key, a statement exception is thrown.
(This means that if the referenced table has only unique keys, you must include
a column list in the ReferencesSpecification.)
If the REFERENCES clause contains a CASCADE or SET NULL referential action,
the primary or unique key referenced must not be deferrable.
A foreign key
constraint is satisfied if there is a matching value in the referenced unique
or primary key column. If the foreign key consists of multiple columns, the
foreign key value is considered NULL if any of its columns contains a NULL.
It is possible for a foreign key consisting of multiple columns to
allow one of the columns to contain a value for which there is no matching
value in the referenced columns, per the SQL standard. To avoid this situation,
create NOT NULL constraints on all of the foreign key's columns.
Foreign key constraints and DML When you insert
into or update a table with an enabled foreign key constraint, checks
that the row does not violate the foreign key constraint by looking up the
corresponding referenced key in the referenced table. If the constraint is
not satisfied, rejects
the insert or update with a statement exception.
When you update or
delete a row in a table with a referenced key (a primary or unique constraint
referenced by a foreign key), checks
every foreign key constraint that references the key to make sure that the
removal or modification of the row does not cause a constraint violation.
If removal or modification of the row would cause a constraint violation,
the update or delete is not permitted and throws
a statement exception.
If the constraint mode is IMMEDIATE (the default),
performs constraint
checks at the time the statement is executed. If the constraint mode is
DEFERRED, the checking is done later, typically at commit time. See
for
more information.
Backing indexes Backing indexescreated
automatically to enforce primary key, unique, and foreign key constraintsIndexesautomatic
creation of for primary key, unique, and foreign key constraintsUNIQUE,
PRIMARY KEY, and FOREIGN KEY constraints generate indexes that enforce or
"back" the constraint (and are sometimes called backing indexes).
PRIMARY KEY constraints generate unique indexes. FOREIGN KEY constraints
generate non-unique indexes. UNIQUE constraints generate unique indexes if
all the columns are non-nullable, and they generate non-unique indexes if
one or more columns are nullable. Therefore, if a column or set of columns has
a UNIQUE, PRIMARY KEY, or FOREIGN KEY constraint on it, you do not need to
create an index on those columns for performance. has
already created it for you. See .
These indexes are
available to the optimizer for query optimization (see )
and have system-generated names.
You cannot drop backing indexes with
a DROP INDEX statement; you must drop the constraint or the table.
Check constraints Check constraintsA
check constraint can be used to specify a wide range of rules for the contents
of a table. A search condition (which is a boolean expression) is specified
for a check constraint. This search condition must be satisfied for all rows
in the table. The search condition is applied to each row that is modified
on an INSERT or UPDATE at the time of the row modification. The entire statement
is aborted if any check constraint is violated.
Requirements for search conditions If
a check constraint is specified as part of a columnDefinition, a column reference
can only be made to the same column. Check constraints specified as part of
a table definition can have column references identifying columns previously
defined in the CREATE TABLE statement.
The search condition must always
return the same value if applied to the same values. Thus, it cannot contain
any of the following:
- Dynamic parameters (?)
- Date/Time Functions (CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP)
- Subqueries
- User Functions (such as USER, SESSION_USER, CURRENT_USER)
Referential actions
Referential actions
CONSTRAINT clausereferential actions in
You can specify
an ON DELETE clause and/or an ON UPDATE clause, followed by the appropriate
action (CASCADE, RESTRICT, SET NULL, or NO ACTION) when defining foreign keys.
These clauses specify whether should
modify corresponding foreign key values or disallow the operation, to keep
foreign key relationships intact when a primary key value is updated or deleted
from a table.
You specify the update and delete rule of a referential
constraint when you define the referential constraint.
The update rule
applies when a row of either the parent or dependent table is updated. The
choices are NO ACTION and RESTRICT.
- When a value in a column of the
parent table's primary key is updated and the update rule has been specified
as RESTRICT, checks
dependent tables for foreign key constraints. If any row in a dependent table
violates a foreign key constraint, the statement is rolled back.
- If
the update rule is NO ACTION, checks
the dependent tables for foreign key constraints after all updates and
BEFORE triggers have been executed, but before AFTER triggers have been
executed. If any row
in a dependent table violates a foreign key constraint, the statement is
rejected.
When
a value in a column of the dependent table is updated, and that value is part
of a foreign key, NO ACTION is the implicit update rule. NO ACTION means that
if a foreign key is updated with a non-null value, the update value must match
a value in the parent table's primary key when the update statement is completed.
If the update does not match a value in the parent table's primary key, the
statement is rejected.
The delete rule applies when a row of the parent
table is deleted and that row has dependents in the dependent table of the
referential constraint. If rows of the dependent table are deleted as part of a
CASCADE on the parent table, the delete
operation on the parent table is said to be propagated to the dependent
table. If the dependent table is also a parent table, the action specified
applies, in turn, to its dependents.
The choices are NO ACTION, RESTRICT,
CASCADE, or SET NULL. SET NULL can be specified only if some column of the
foreign key allows null values. If the delete rule is:
- NO ACTION, checks the dependent
tables for foreign key constraints after all deletes and BEFORE triggers
have been executed, but before AFTER triggers have been executed. If any
row in a dependent table violates a foreign key constraint, the statement is
rejected.
- RESTRICT, checks dependent tables
for foreign key constraints. If any row in a dependent table violates a foreign
key constraint, the statement is rolled back.
- CASCADE, the delete
operation is propagated to the dependent table (and that table's dependents,
if applicable).
- SET NULL, each nullable column of the dependent table's
foreign key is set to null.
If ON DELETE is not specified, NO ACTION is the implicit delete rule.
Each
referential constraint in which a table is a parent has its own delete rule;
all applicable delete rules are used to determine the result of a delete operation.
Thus, a row cannot be deleted if it has dependents in a referential constraint
with a delete rule of RESTRICT or NO ACTION. Similarly, a row cannot be deleted
if the deletion cascades to any of its descendants that are dependents in
a referential constraint with the delete rule of RESTRICT or NO ACTION.
Deleting
a row from the parent table involves other tables. Any table involved in a
delete operation on the parent table is said to be delete-connected to the
parent table. The delete can affect rows of these tables in the following
ways:
- If the delete rule is RESTRICT or NO ACTION, a dependent table is involved
in the operation but is not affected by the operation. (That is, checks
the values within the table, but does not delete any values.)
- If the delete rule is SET NULL, a dependent table's rows can be updated
when a row of the parent table is the object of a delete or propagated delete
operation.
- If the delete rule is CASCADE, a dependent table's rows can be deleted
when a parent table is the object of a delete.
- If the dependent table is also a parent table, the actions described in
this list apply, in turn, to its dependents.
If a foreign key's constraint mode is DEFERRED, an insert (or update of a row
that changes the foreign key) in the child table will be checked at deferred
checking time, notwithstanding the ON DELETE or ON UPDATE referential action
specification. If a row in the parent table is deleted (or updated so as to
modify the referenced key), the behavior depends on the specification of
ON DELETE or ON UPDATE. Only if NO ACTION has been specified is the checking
ever deferred. If the primary table's referenced primary or unique key
constraint is also deferred, any delete of a parent row can lead to a foreign
key violation immediately (or at deferred checking time, if the foreign key is
also deferred, as the case may be) when the last of possibly several key
duplicates of the referenced key is deleted or updated.
Statement dependency system INSERT
and UPDATE statements depend on all constraints on the target table. DELETEs
depend on unique, primary key, and foreign key constraints. These statements
are invalidated if a constraint is added to or dropped from the target table.
Examples -- column-level primary key constraint named OUT_TRAY_PK:
CREATE TABLE SAMP.OUT_TRAY
(
SENT TIMESTAMP,
DESTINATION CHAR(8),
SUBJECT CHAR(64) NOT NULL CONSTRAINT OUT_TRAY_PK PRIMARY KEY,
NOTE_TEXT VARCHAR(3000)
);
-- the table-level primary key definition allows you to
-- include two columns in the primary key definition:
CREATE TABLE SAMP.SCHED
(
CLASS_CODE CHAR(7) NOT NULL,
DAY SMALLINT NOT NULL,
STARTING TIME,
ENDING TIME,
PRIMARY KEY (CLASS_CODE, DAY)
);
-- Use a column-level constraint for an arithmetic check
-- Use a table-level constraint
-- to make sure that a employee's taxes does not
-- exceed the bonus
CREATE TABLE SAMP.EMP
(
EMPNO CHAR(6) NOT NULL CONSTRAINT EMP_PK PRIMARY KEY,
FIRSTNME CHAR(12) NOT NULL,
MIDINIT VARCHAR(12) NOT NULL,
LASTNAME VARCHAR(15) NOT NULL,
SALARY DECIMAL(9,2) CONSTRAINT SAL_CK CHECK (SALARY >= 10000),
BONUS DECIMAL(9,2),
TAX DECIMAL(9,2),
CONSTRAINT BONUS_CK CHECK (BONUS > TAX)
);
-- use a check constraint to allow only appropriate
-- abbreviations for the meals
CREATE TABLE FLIGHTS
(
FLIGHT_ID CHAR(6) NOT NULL ,
SEGMENT_NUMBER INTEGER NOT NULL ,
ORIG_AIRPORT CHAR(3),
DEPART_TIME TIME,
DEST_AIRPORT CHAR(3),
ARRIVE_TIME TIME,
MEAL CHAR(1) CONSTRAINT MEAL_CONSTRAINT
CHECK (MEAL IN ('B', 'L', 'D', 'S')),
PRIMARY KEY (FLIGHT_ID, SEGMENT_NUMBER)
);
-- use the same check constraint, but
-- make the MEAL_CONSTRAINT deferrable
CREATE TABLE FLIGHTS
(
FLIGHT_ID CHAR(6) NOT NULL,
SEGMENT_NUMBER INTEGER NOT NULL,
ORIG_AIRPORT CHAR(3),
DEPART_TIME TIME,
DEST_AIRPORT CHAR(3),
ARRIVE_TIME TIME,
MEAL CHAR(1) CONSTRAINT MEAL_CONSTRAINT
CHECK (MEAL IN ('B', 'L', 'D', 'S'))
DEFERRABLE INITIALLY DEFERRED,
PRIMARY KEY (FLIGHT_ID, SEGMENT_NUMBER)
);
CREATE TABLE METROPOLITAN
(
HOTEL_ID INT NOT NULL CONSTRAINT HOTELS_PK PRIMARY KEY,
HOTEL_NAME VARCHAR(40) NOT NULL,
CITY_ID INT CONSTRAINT METRO_FK REFERENCES CITIES
);
-- create a table with a table-level primary key constraint
-- and a table-level foreign key constraint
CREATE TABLE FLTAVAIL
(
FLIGHT_ID CHAR(6) NOT NULL,
SEGMENT_NUMBER INT NOT NULL,
FLIGHT_DATE DATE NOT NULL,
ECONOMY_SEATS_TAKEN INT,
BUSINESS_SEATS_TAKEN INT,
FIRSTCLASS_SEATS_TAKEN INT,
CONSTRAINT FLTAVAIL_PK PRIMARY KEY (FLIGHT_ID, SEGMENT_NUMBER),
CONSTRAINT FLTS_FK
FOREIGN KEY (FLIGHT_ID, SEGMENT_NUMBER)
REFERENCES Flights (FLIGHT_ID, SEGMENT_NUMBER)
);
-- add a unique constraint to a column
ALTER TABLE SAMP.PROJECT
ADD CONSTRAINT P_UC UNIQUE (PROJNAME);
-- create a table whose city_id column references the
-- primary key in the Cities table
-- using a column-level foreign key constraint
CREATE TABLE CONDOS
(
CONDO_ID INT NOT NULL CONSTRAINT hotels_PK PRIMARY KEY,
CONDO_NAME VARCHAR(40) NOT NULL,
CITY_ID INT CONSTRAINT city_foreign_key
REFERENCES Cities ON DELETE CASCADE ON UPDATE RESTRICT
);