|Oracle8i SQL Reference
Release 3 (8.1.7)
Part Number A85397-01
ALTER TABLE to constraint_clause, 14 of 14
Use the constraint_clause in a
TABLE statement to define an integrity constraint. An integrity constraint is a rule that restricts the values for one or more columns in a table or an index-organized table.
Constraint clauses can appear in either
TABLE statements. To define an integrity constraint, you must have the privileges necessary to issue one of these statements.
To create a referential integrity constraint, the parent table must be in your own schema, or you must have the
REFERENCES privilege on the columns of the referenced key in the parent table.
storage_clause: See the storage_clause.
table_constraint syntax is part of the table definition. An integrity constraint defined with this syntax can impose rules on any columns in the table.
table_constraint syntax can appear in a
TABLE statement. This syntax can define any type of integrity constraint except a
column_constraint syntax is part of a column definition. Usually, an integrity constraint defined with this syntax can impose rules only on the column in which it is defined.
column_constraintsyntax that appears in a
ADDstatement can define any type of integrity constraint.
Column_constraintsyntax that appears in an
column_optionsstatement can only define or remove a
Restriction: The only column constraint allowed on a
VARRAY column is
NULL. However, you can specify any type of column constraint on the scalar attributes of a
Specify a name for the constraint. Oracle stores this name in the data dictionary along with the definition of the integrity constraint. If you omit this identifier, Oracle generates a name with the form
If you do not specify
NULL in a column definition,
NULL is the default.
Restriction: You cannot create a constraint on columns or attributes whose type is user-defined object, LOB, or
REF, with the following exceptions:
NULLconstraint on columns or attributes of user-defined object type, varray, and LOB.
NULLand referential integrity constraints on a column of type
UNIQUE to designate a column or combination of columns as a unique key. To satisfy a
UNIQUE constraint, no two rows in the table can have the same value for the unique key. However, the unique key made up of a single column can contain nulls.
A composite unique key is made up of a combination of columns. To define a composite unique key, you must use
table_constraint syntax rather than
column_constraint syntax. Any row that contains nulls in all key columns automatically satisfies the constraint. However, two rows that contain nulls for one or more key columns and the same combination of values for the other key columns violate the constraint.
KEY to designate a column or combination of columns as the table's primary key. A composite primary key is made up of a combination of columns. To define a composite primary key, you must use the
table_constraint syntax rather than the
KEYof an index-organized table cannot exceed one-half of the database block size or 3800 bytes, whichever is less. (
KEYis required for an index-organized table.)
Indicate whether a column can contain nulls. You must specify
column_constraint syntax, not with
Restriction: You cannot specify
NULL for an attribute of an object. Instead, use a
CHECK constraint with the
Referential integrity constraints designate a column or combination of columns as the foreign key and establish a relationship between that foreign key and a specified primary or unique key, called the referenced key. The table containing the foreign key is called the child table, and the table containing the referenced key is called the parent table. The foreign key and the referenced key can be in the same table. In this case, the parent and child tables are the same.
table_constraintsyntax. This syntax allows you to specify a composite foreign key, which is made up of a combination of columns.
REFERENCESclause of the
column_constraintsyntax to specify a referential integrity constraint in which the foreign key is made up of a single column.
You can designate the same column or combination of columns as both a foreign key and a primary or unique key. You can also designate the same column or combination of columns as both a foreign key and a cluster key.
You can define multiple foreign keys in a table. Also, a single column can be part of more than one foreign key.
Restrictions on referential integrity constraints:
KEYconstraint on the parent table must already be defined.
TABLEstatement that contains an
subqueryclause. Instead, you must create the table without the constraint and then add it later with an
foreign_key_clause lets you designate a column or combination of columns as the foreign key from the table level. You must use this syntax to define a composite foreign key.
To satisfy a referential integrity constraint involving composite keys, either the values of the foreign key columns must match the values of the referenced key columns in a row in the parent table, or the value of at least one of the columns of the foreign key must be null.
CHECK clause lets you specify a condition that each row in the table must satisfy. To satisfy the constraint, each row in the table must make the condition either
TRUE or unknown (due to a null). When Oracle evaluates a
CHECK constraint condition for a particular row, any column names in the condition refer to the column values in that row.
If you create multiple
CHECK constraints for a column, design them carefully so their purposes do not conflict, and do not assume any particular order of evaluation of the conditions. Oracle does not verify that
CHECK conditions are not mutually exclusive.
"Conditions" for additional information and syntax
CHECKconstraint can refer to any column in the table, but it cannot refer to columns of other tables.
CHECKconstraint conditions cannot contain the following constructs:
column_ref constraints let you further describe a column of type
REF. The only difference between these clauses is that you specify
table_ref_constraint from the table level, so you must identify the
REF column or attribute you are defining. You specify
column_ref_constraint after you have already identified the
REF column or attribute. Both types of constraint let you specify a
SCOPE constraint, a
ROWID constraint, or a referential integrity constraint.
As is the case for regular table and column constraints, you use
KEY syntax for a referential integrity constraint at the table level, and
REFERENCES syntax for a referential integrity constraint at the column level.
REF column's scope table or reference table has a primary-key-based object identifier, then it is a user-defined
Specify the name of a
Specify an embedded
In a table with a
If you do not specify
DEFERRABLE to indicate that constraint checking can be deferred until the end of the transaction by using the
DEFERRABLE to indicate that this constraint is checked at the end of each DML statement. If you do not specify either word, then
DEFERRABLE is the default.
DEFERRABLEconstraint with the
DEFERRABLEif you are modifying an existing constraint directly (that is, by specifying the
NORELY parameters specify whether a constraint in
NOVALIDATE mode is to be taken into account for query rewrite. Specify
RELY to activate an existing constraint in
NOVALIDATE mode for query rewrite in an unenforced query rewrite integrity mode. The constraint is in
NOVALIDATE mode, so Oracle does not enforce it. The default is
Unenforced constraints are generally useful only with materialized views and query rewrite. Depending on the
QUERY_REWRITE_INTEGRITY mode (see ALTER SESSION), query rewrite can use only constraints that are in
VALIDATE mode, or that are in
NOVALIDATE mode with the
RELY parameter set, to determine join information.
Oracle8i Data Warehousing Guide for more information on materialized views and query rewrite
NORELYare relevant only if you are modifying an existing constraint (that is, you have issued the
using_index_clause lets you specify parameters for the index Oracle uses to enable a
KEY constraint. The name of the index is the same as the name of the constraint.
You can choose the values of the
PCTFREE parameters for the index.
If table is partitioned, you can specify a locally or globally partitioned index for the unique or primary key constraint.
Restriction: Use this clause only when enabling
NOSORT to indicate that the rows are stored in the database in ascending order and therefore Oracle does not have to sort the rows when creating the index.
ENABLE if you want the constraint to be applied to all new data in the table. Before you can enable a referential integrity constraint, its referenced constraint must be enabled.
VALIDATEadditionally indicates that all old data also complies with the constraint. An enabled validated constraint guarantees that all data is and will continue to be valid.
If you place a primary key constraint in
VALIDATE mode, the validation process will verify that the primary key columns contain no nulls. To avoid this overhead, mark each column in the primary key
NULL before enabling the table's primary key constraint. (For optimal results, do this before inserting data into the column.)
NOVALIDATEensures that all new DML operations on the constrained data comply with the constraint, but does not ensure that existing data in the table complies with the constraint.
Enabling a primary key or unique key constraint automatically creates a unique index to enforce the constraint. This index is dropped if the constraint is subsequently disabled, causing Oracle to rebuild the index every time the constraint is enabled. To avoid this behavior, create new primary key and unique key constraints initially disabled. Then create nonunique indexes or use existing nonunique indexes to enforce the constraints.
DISABLE to disable the integrity constraint. If you do not specify this clause when creating a constraint, Oracle automatically enables the constraint.
VALIDATEdisables the constraint and drops the index on the constraint, but keeps the constraint valid. This feature is most useful in data warehousing situations, where the need arises to load into a range-partitioned table a quantity of data with a distinct range of values in the unique key. In such situations, the disable validate state enables you to save space by not having an index. You can then load data from a nonpartitioned table into a partitioned table using the
TABLEstatement or using SQL*Loader. All other modifications to the table (inserts, updates, and deletes) by other SQL statements are disallowed.
If the unique key coincides with the partitioning key of the partitioned table, disabling the constraint saves overhead and has no detrimental effects. If the unique key does not coincide with the partitioning key, Oracle performs automatic table scans during the exchange to validate the constraint, which might offset the benefit of loading without an index.
NOVALIDATEsignifies that Oracle makes no effort to maintain the constraint (because it is disabled) and cannot guarantee that the constraint is true (because it is not being validated).
You cannot drop a table whose primary key is being referenced by a foreign key even if the foreign key constraint is in
NOVALIDATE state. Further, the optimizer can use constraints in
Oracle8i Performance Guide and Reference for information on when to use this setting
NOVALIDATE, the default is
INTO clause lets you specify a table into which Oracle places the rowids of all rows violating the constraint. If you omit schema, Oracle assumes the exceptions table is in your own schema. If you omit this clause altogether, Oracle assumes that the table is named
EXCEPTIONS. The exceptions table must be on your local database.
INTO clause is valid only when validating a constraint.
You can create the
EXCEPTIONS table using one of these scripts:
UTLEXCPT.SQLuses physical rowids. Therefore it can accommodate rows from conventional tables but not from index-organized tables. (See the Note that follows.)
UTLEXPT1.SQLuses universal rowids, so it can accommodate rows from both conventional and index-organized tables.
If you create your own exceptions table, it must follow the format prescribed by one of these two scripts.
Restriction: You cannot specify this clause in a
TABLE statement, because no rowids exist until after the successful completion of the statement.
If you are collecting exceptions from index-organized tables based on primary keys (rather than universal rowids), you must create a separate exceptions table for each index-organized table to accommodate its primary-key storage. You create multiple exceptions tables with different names by modifying and resubmitting the script.
The following statement creates the
dept table and defines and enables a unique key on the
CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(9) CONSTRAINT unq_dname UNIQUE, loc VARCHAR2(10) );
unq_dname identifies the
dname column as a unique key. This constraint ensures that no two departments in the table have the same name. However, the constraint does allow departments without names.
Alternatively, you can define and enable this constraint with the
CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(9), loc VARCHAR2(10), CONSTRAINT unq_dname UNIQUE (dname) USING INDEX PCTFREE 20 TABLESPACE user_x STORAGE (INITIAL 8K NEXT 6K) );
The above statement also uses the
INDEX clause to specify storage characteristics for the index that Oracle creates to enable the constraint.
The following statement defines and enables a composite unique key on the combination of the
state columns of the
ALTER TABLE census ADD CONSTRAINT unq_city_state UNIQUE (city, state) USING INDEX PCTFREE 5 TABLESPACE user_y EXCEPTIONS INTO bad_keys_in_ship_cont;
unq_city_state constraint ensures that the same combination of
state values does not appear in the table more than once.
CONSTRAINT clause also specifies other properties of the constraint:
INDEXclause specifies storage characteristics for the index Oracle creates to enable the constraint.
INTOclause causes Oracle to write information to the
bad_keys_in_ship_conttable about any rows currently in the
censustable that violate the constraint.
The following statement creates the
dept table and defines and enables a primary key on the
CREATE TABLE dept (deptno NUMBER(2) CONSTRAINT pk_dept PRIMARY KEY, dname VARCHAR2(9), loc VARCHAR2(10) );
pk_dept constraint identifies the
deptno column as the primary key of the
dept table. This constraint ensures that no two departments in the table have the same department number and that no department number is
Alternatively, you can define and enable this constraint with
CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(9), loc VARCHAR2(10), CONSTRAINT pk_dept PRIMARY KEY (deptno) );
The following statement defines a composite primary key on the combination of the
container_no columns of the
This constraint identifies the combination of the
container_no columns as the primary key of the
ship_cont table. The constraint ensures that no two rows in the table have the same values for both the
ship_no column and the
CONSTRAINT clause also specifies the following properties of the constraint:
DISABLEclause causes Oracle to define the constraint but not enable it.
The following statement alters the emp table and defines and enables a
NULL constraint on the
nn_sal ensures that no employee in the table has a null salary.
The following example guarantees that a value exists for both the
last_name attributes of the
name column in the
CREATE TYPE person_name AS OBJECT (first_name VARCHAR2(30), last_name VARCHAR2(30)); CREATE TABLE students (name person_name, age INTEGER, CHECK (name.first_name IS NOT NULL AND name.last_name IS NOT NULL));
The following statement creates the
emp table and defines and enables a foreign key on the
deptno column that references the primary key on the
deptno column of the
CREATE TABLE emp (empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno CONSTRAINT fk_deptno REFERENCES dept(deptno) );
fk_deptno ensures that all departments given for employees in the
emp table are present in the
dept table. However, employees can have null department numbers, meaning they are not assigned to any department. To ensure that all employees are assigned to a department, you could create a
NULL constraint on the
deptno column in the
emp table, in addition to the
Before you define and enable this constraint, you must define and enable a constraint that designates the
deptno column of the
dept table as a primary or unique key.
The referential integrity constraint definition does not use the
KEY keyword to identify the columns that make up the foreign key. Because the constraint is defined with a column constraint clause on the
deptno column, the foreign key is automatically on the
The constraint definition identifies both the parent table and the columns of the referenced key. Because the referenced key is the parent table's primary key, the referenced key column names are optional.
The above statement omits the
deptno column's datatype. Because this column is a foreign key, Oracle automatically assigns it the datatype of the
dept.deptno column to which the foreign key refers.
Alternatively, you can define a referential integrity constraint with
CREATE TABLE emp (empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno, CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept(deptno) );
The foreign key definitions in both statements of this statement omit the
DELETE clause, causing Oracle to forbid the deletion of a department if any employee works in that department.
This statement creates the
emp table, defines and enables two referential integrity constraints, and uses the
CREATE TABLE emp (empno NUMBER(4) PRIMARY KEY, ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4) CONSTRAINT fk_mgr REFERENCES emp ON DELETE SET NULL, hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2) CONSTRAINT fk_deptno REFERENCES dept(deptno) ON DELETE CASCADE );
Because of the first
DELETE clause, if manager number 2332 is deleted from the
emp table, Oracle sets to null the value of
mgr for all employees in the
emp table who previously had manager 2332.
Because of the second
DELETE clause, Oracle cascades any deletion of a
deptno value in the
dept table to the
deptno values of its dependent rows of the
emp table. For example, if Department 20 is deleted from the
dept table, Oracle deletes the department's employees from the
The following statement defines and enables a foreign key on the combination of the
phoneno columns of the
ALTER TABLE phone_calls ADD CONSTRAINT fk_areaco_phoneno FOREIGN KEY (areaco, phoneno) REFERENCES customers(areaco, phoneno) EXCEPTIONS INTO wrong_numbers;
fk_areaco_phoneno ensures that all the calls in the
phone_calls table are made from phone numbers that are listed in the
customers table. Before you define and enable this constraint, you must define and enable a constraint that designates the combination of the
phoneno columns of the
customers table as a primary or unique key.
INTO clause causes Oracle to write information to the
wrong_numbers table about any rows in the
phone_calls table that violate the constraint.
The following statement creates the
dept table and defines a
check constraint in each of the table's columns:
CREATE TABLE dept (deptno NUMBER CONSTRAINT check_deptno CHECK (deptno BETWEEN 10 AND 99) DISABLE, dname VARCHAR2(9) CONSTRAINT check_dname CHECK (dname = UPPER(dname)) DISABLE, loc VARCHAR2(10) CONSTRAINT check_loc CHECK (loc IN ('DALLAS','BOSTON', 'NEW YORK','CHICAGO')) DISABLE);
Each constraint restricts the values of the column in which it is defined:
check_deptnoensures that no department numbers are less than 10 or greater than 99.
check_dnameensures that all department names are in uppercase.
check_locrestricts department locations to Dallas, Boston, New York, or Chicago.
CONSTRAINT clause contains the
DISABLE clause, Oracle only defines the constraints and does not enable them.
The following statement creates the
emp table and uses a
table_constraint_clause to define and enable a
CREATE TABLE emp (empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), mgr NUMBER(4), hiredate DATE, sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2), CHECK (sal + comm <= 5000) );
This constraint uses an inequality condition to limit an employee's total compensation, the sum of salary and commission, to $5000:
CONSTRAINT clause in this example does not supply a constraint name, Oracle generates a name for the constraint.
The following statement defines and enables a
KEY constraint, two referential integrity constraints, a
NULL constraint, and two
CREATE TABLE order_detail (CONSTRAINT pk_od PRIMARY KEY (order_id, part_no), order_id NUMBER CONSTRAINT fk_oid REFERENCES scott.order (order_id), part_no NUMBER CONSTRAINT fk_pno REFERENCES scott.part (part_no), quantity NUMBER CONSTRAINT nn_qty NOT NULL CONSTRAINT check_qty_low CHECK (quantity > 0), cost NUMBER CONSTRAINT check_cost CHECK (cost > 0) );
The constraints enable the following rules on table data:
pk_odidentifies the combination of the
part_nocolumns as the primary key of the table. To satisfy this constraint, no two rows in the table can contain the same combination of values in the
part_nocolumns, and no row in the table can have a null in either the
order_idcolumn or the
order_idcolumn as a foreign key that references the
order_idcolumn in the
scott's schema. All new values added to the column
order_idmust already appear in the column
part_nocolumn as a foreign key that references the
part_nocolumn in the
parttable owned by
scott. All new values added to the column
order_detail.part_nomust already appear in the column
nn_qtyforbids nulls in the
check_qtyensures that values in the
quantitycolumn are always greater than zero.
check_costensures the values in the cost column are always greater than zero.
This example also illustrates the following points about constraint clauses and column definitions:
Table_constraintsyntax and column definitions can appear in any order. In this example, the
table_constraintsyntax that defines the
pk_odconstraint precedes the column definitions.
column_constraintsyntax multiple times. In this example, the definition of the
quantitycolumn contains the definitions of both the
CHECKconstraints, each with a simple condition enforcing a single business rule, is better than a single
CHECKconstraint with a complicated condition enforcing multiple business rules. When a constraint is violated, Oracle returns an error identifying the constraint. Such an error more precisely identifies the violated business rule if the identified constraint enables a single business rule.
The following statement creates table
games with a
IMMEDIATE constraint check on the
To define a unique constraint on a column as
DEFERRABLE, issue the following statement:
CREATE TABLE orders (ord_num NUMBER CONSTRAINT unq_num UNIQUE (ord_num) INITIALLY DEFERRED DEFERRABLE);