Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
SQL Statements:
ALTER TRIGGER to constraint_clause, 12 of 12
Use the constraint_clause
in a CREATE
TABLE
or ALTER
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, an index-organized table, or a view.
Constraint clauses can appear in CREATE
TABLE
, ALTER
TABLE
, CREATE
VIEW
, or ALTER
VIEW
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.
table_or_view_constraint
column_constraint
table_ref_constraint::=
table_ref_constraint
column_ref_constraint::=
column_ref_constraint
references_clause
constraint_state::=
constraint_state
exceptions_clause
using_index_clause::=
using_index_clause
global_partitioned_index::=
global_partitioned_index
global_partitioning_clause::=
global_partitioning_clause
physical_attributes_clause::=
physical_attributes_clause
storage_clause: See the storage_clause
.
The table_or_view_constraint
syntax is part of the table or view definition. An integrity constraint defined with this syntax can impose rules on any columns in the table or view.
Table constraint syntax can appear in a CREATE
TABLE
or ALTER
TABLE
statement. This syntax can define any type of integrity constraint except a NOT
NULL
constraint.
View constraints are a subset of table constraints. View constraint syntax can appear in a CREATE
VIEW
and ALTER
VIEW
statements. View constraints are declarative only. That is, Oracle does not enforce them. However, operations on views are subject to the integrity constraints defined on the underlying base tables, so you can enforce constraints on views through constraints on base tables.
For table constraints:
In addition, for view constraints:
DEFERRED
or DEFERRABLE
clause.
DISABLE
NOVALIDATE
mode. You cannot specify any other mode.
using_index_clause
, the EXCEPTIONS
INTO
clause, or the ON
DELETE
clause in view constraints.
The column_constraint
syntax is part of a column definition. For tables, an integrity constraint defined with this syntax can usually impose rules only on the column in which it is defined. For views, constraints are declarative only. That is, Oracle does not enforce them. However, operations on views are subject to the integrity constraints defined on the underlying base tables, so you can enforce constraints on views through constraints on base tables.
column_constraint
syntax that appears in a CREATE
TABLE
or ALTER
TABLE
ADD
statement can define any type of integrity constraint.
Column_constraint
syntax that appears in an ALTER
TABLE
MODIFY
column_options
statement can only define or remove a NOT
NULL
constraint.
Column_constraint
syntax that appears in a CREATE
VIEW
or ALTER
VIEW
statement is subject to the same restrictions as view constraints. See "Restrictions on Table and View Constraints".
Restriction:
XMLType
column is NOT
NULL
.
VARRAY
column is NOT
NULL
. However, you can specify any type of column constraint on the scalar attributes of a NESTED
TABLE
column.
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 SYS_C
n
.
If you do not specify NULL
or NOT
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:
NOT
NULL
constraint on columns or attributes of user-defined object type, varray, and LOB.
NOT
NULL
and referential integrity constraints on a column of type REF
.
Specify 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_or_view_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.
Restrictions:
TIMESTAMP
WITH
TIME
ZONE
. However, the unique key can contain a column of TIMESTAMP
WITH
LOCAL
TIME
ZONE
.
LONG
or LONG
RAW
.
Specify PRIMARY
KEY
to designate a column or combination of columns as the primary key or the table or view. A composite primary key is made up of a combination of columns. To define a composite primary key, you must use the table_or_view_constraint
syntax rather than the column_constraint
syntax.
Restrictions:
LONG
, LONG
RAW
, VARRAY
, NESTED
TABLE
, OBJECT
, LOB, BFILE
, or REF
, or TIMESTAMP
WITH
TIME
ZONE
.
PRIMARY
KEY
of an index-organized table cannot exceed one-half of the database block size or 3800 bytes, whichever is less. (PRIMARY
KEY
is required for an index-organized table.)
Indicate whether a column can contain nulls. You must specify NULL
and NOT
NULL
with column_constraint
syntax, not with table_or_view_constraint
syntax.
Specify NULL
if a column can contain null values. The NULL
keyword does not actually define an integrity constraint. If you do not specify either NOT
NULL
or NULL
, the column can contain nulls by default.
Specify NOT
NULL
if a column cannot contain null values. To satisfy this constraint, every row in the table must contain a value for the column.
Restrictions:
NULL
or NOT
NULL
for a view.
NULL
or NOT
NULL
for an attribute of an object. Instead, use a CHECK
constraint with the IS
[NOT
] NULL
condition.
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 or view containing the foreign key is called the child object, and the table or view containing the referenced key is called the parent object. The foreign key and the referenced key can be in the same table or view. In this case, the parent and child tables are the same.
FOREIGN
KEY
clause with the table_or_view_constraint
syntax. This syntax lets you specify a composite foreign key, which is made up of a combination of columns.
REFERENCES
clause of the column_constraint
syntax 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 or view. Also, a single column can be part of more than one foreign key.
LONG
or LONG
RAW
.
UNIQUE
or PRIMARY
KEY
constraint on the parent table or view must already be defined.
CREATE
TABLE
statement that contains an AS
subquery
clause. Instead, you must create the table without the constraint and then add it later with an ALTER
TABLE
statement.
The 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 view, or the value of at least one of the columns of the foreign key must be null.
The REFERENCES
clause lets you designate the current column or attribute as the foreign key and identifies the parent table or view and the column or combination of columns that make up the referenced key. If you identify only the parent table or view and omit the column names, the foreign key automatically references the primary key of the parent table or view. The corresponding columns of the referenced key and the foreign key must match in number and datatypes.
For table constraints, the ON
DELETE
clause lets you determine how Oracle automatically maintains referential integrity if you remove a referenced primary or unique key value. If you omit this clause, Oracle does not allow you to delete referenced key values in the parent table that have dependent rows in the child table. This clause is not valid for view constraints.
CASCADE
if you want Oracle to remove dependent foreign key values.
SET
NULL
if you want Oracle to convert dependent foreign key values to NULL
.
Restrictions on Foreign Keys
The 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.
Restrictions on CHECK Constraints
CHECK
constraint for a view.
CHECK
constraint can refer to any column in the table, but it cannot refer to columns of other tables.
CHECK
constraint conditions cannot contain the following constructs:
The table_ref
and 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 WITH
ROWID
constraint, or a referential integrity constraint.
As is the case for regular table and column constraints, you use FOREIGN
KEY
syntax for a referential integrity constraint at the table level, and REFERENCES
syntax for a referential integrity constraint at the column level.
If the REF
column's scope table or reference table has a primary-key-based object identifier, then it is a user-defined REF
column.
See Also:
Oracle9i Database Concepts for more information on REFs and "Referential Integrity Constraints" |
Specify the name of a REF
column of an object or relational table.
Specify an embedded REF
attribute within an object column of a relational table.
In a table with a REF
column, each REF
value in the column can conceivably reference a row in a different object table. The SCOPE
clause restricts the scope of references to a single table, scope_table_name
. The values in the REF
column or attribute point to objects in scope_table_name
, in which object instances (of the same type as the REF
column) are stored. You can specify only one scope table for each REF
column.
Restrictions on the SCOPE Clause
SCOPE
constraint to an existing column unless the table is empty.
SCOPE
for the REF
elements of a varray column.
AS
subquery
and the subquery returns user-defined REF
s.
scope_table_name
must be in your own schema or you must have SELECT
privileges on scope_table_name
or SELECT
ANY
TABLE
system privileges.
SCOPE
table constraint from a REF
column.
Specify WITH
ROWID
to store the rowid along with the REF
value in ref_column
or ref_attribute
. Storing a REF
value with a rowid can improve the performance of dereferencing operations, but will also use more space. Default storage of REF
values is without rowids.
Restrictions on the WITH ROWID Clause
WITH
ROWID
constraint for the REF
elements of a varray column.
WITH
ROWID
constraint from a REF
column.
REF
column or attribute is scoped, then this clause is ignored and the rowid is not stored with the REF
value.
The references_clause
lets you specify a referential integrity constraint on the REF
column.This clause also implicitly restricts the scope of the REF
column or attribute to the reference table.
If you do not specify CONSTRAINT
, Oracle generates a system name for the constraint.
Restrictions on the references_clause
REF
column that is scoped, then the referenced table must be the same as the scope table of the REF
column.
REF
column. Therefore, all the restrictions that apply for the SCOPE
constraint also apply in this case.
REF
column will remain scoped to the referenced table.
Use the constraint_state to specify how and when Oracle should enforce the constraint.
Specify DEFERRABLE
to indicate that constraint checking can be deferred until the end of the transaction by using the SET
CONSTRAINT(S)
statement.
See Also:
|
Specify NOT
DEFERRABLE
to indicate that this constraint is checked at the end of each DML statement. If you do not specify either word, then NOT
DEFERRABLE
is the default.
Specify INITIALLY
IMMEDIATE
to indicate that at the start of every transaction, the default is to check this constraint at the end of every DML statement. If you do not specify INITIALLY
, INITIALLY
IMMEDIATE
is the default.
Specify INITIALLY
DEFERRED
to indicate that this constraint is DEFERRABLE
and that, by default, the constraint is checked only at the end of each transaction.
Restrictions on DEFERRABLE and NOT DEFERRABLE
NOT
DEFERRABLE
constraint with the SET
CONSTRAINT(S)
statement.
DEFERRABLE
or NOT
DEFERRABLE
if you are modifying an existing constraint directly (that is, by specifying the ALTER
TABLE
... MODIFY
constraint
statement).
The RELY
and 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 NORELY
.
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.
See Also:
Oracle9i Data Warehousing Guide for more information on materialized views and query rewrite |
Restrictions on RELY and NORELY
RELY
and NORELY
are relevant only if you are modifying an existing constraint (that is, you have issued the ALTER
TABLE .
.. MODIFY
constraint statement).
NOT
NULL
constraint to RELY
.
The using_index_clause
lets you specify an index for Oracle to use to enforce a UNIQUE
and PRIMARY
KEY
constraint, or lets you instruct Oracle to create the index used to enforce the constraint.
schema
.index
, Oracle will attempt to enforce the constraint using the specified index. If Oracle cannot find the index or cannot use the index to enforce the constraint, Oracle returns an error.
create_index_statement
, Oracle will attempt to create the index and use it to enforce the constraint. If Oracle cannot create the index or cannot use the index to enforce the constraint, Oracle returns an error.
INITRANS
, MAXTRANS
, TABLESPACE
, STORAGE
, and PCTFREE
parameters for the index.
table
is partitioned, you can specify a locally or globally partitioned index for the unique or primary key constraint.
"Explicit Index Control Example" for an example of how you can create an index for Oracle to use in enforcing a constraint
See Also:
Restrictions on the using_index_clause
using_index_clause
only when enabling UNIQUE
and PRIMARY
KEY
constraints.
schema.index
) or create an index (create_index_statement
) when enabling the primary key of an index-organized table.
See Also:
CREATE
TABLE
for information on the INITRANS
, MAXTRANS
, TABLESPACE
, STORAGE
, and PCTFREE
parameters
LOCAL
and
global_partitioned_index
clause, and for a description of NOSORT
and LOGGING
|NOLOGGING
in relation to indexes
The global_partitioned_index
clause lets you specify that the partitioning of the index is user defined and is not equipartitioned with the underlying table. By default, nonpartitioned indexes are global indexes.
Specify PARTITION
BY
RANGE
to indicate that the global index is partitioned on the ranges of values from the columns specified in column_list
. You cannot specify this clause for a local index.
For column_list
, specify the name of the column(s) of a table on which the index is partitioned. The column_list
must specify a left prefix of the index column list.
You cannot specify more than 32 columns in column_list,
and the columns cannot contain the ROWID
pseudocolumn or a column of type ROWID
.
The PARTITION
clause lets you describe the individual index partitions. The number of clauses determines the number of partitions. If you omit partition
, Oracle generates a name with the form SYS_P
n
.
For VALUES
LESS
THAN
(value_list
), specify the (noninclusive) upper bound for the current partition in a global index. The value_list
is a comma-separated, ordered list of literal values corresponding to column_list
in the partition_by_range_clause
. Always specify MAXVALUE
as the value_list
of the last partition.
Specify 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.
Specify ENABLE
if you want the constraint to be applied to all new data in the table.
ENABLE VALIDATE
specifies 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 any row in the table violates the integrity constraint, the constraint remains disabled and Oracle returns an error. If all rows comply with the constraint, Oracle enables the constraint. Subsequently, if new data violates the constraint, Oracle does not execute the statement and returns an error indicating the integrity constraint violation.
If you place a primary key constraint in ENABLE
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 NOT
NULL
before enabling the table's primary key constraint. (For optimal results, do this before entering data into the column.)
ENABLE NOVALIDATE
ensures that all new DML operations on the constrained data comply with the constraint. This clause does not ensure that existing data in the table complies with the constraint and therefore does not require a table lock.
If you specify neither VALIDATE
nor NOVALIDATE
, the default is VALIDATE
.
If you enable a unique or primary key constraint, and if no index exists on the key, Oracle creates a unique index. This index is dropped if the constraint is subsequently disabled, so Oracle rebuilds the index every time the constraint is enabled.
To avoid rebuilding the index and eliminate redundant indexes, create new primary key and unique constraints initially disabled. Then create (or use existing) nonunique indexes to enforce the constraint. Oracle does not drop a nonunique index when the constraint is disabled, so subsequent ENABLE
operations are facilitated.
If you change the state of any single constraint from ENABLE
NOVALIDATE
to ENABLE
VALIDATE
, the operation can be performed in parallel, and does not block reads, writes, or other DDL operations.
Restriction on ENABLE: You cannot enable a foreign key that references a unique or primary key that is disabled.
Specify DISABLE
to disable the integrity constraint. Disabled integrity constraints appear in the data dictionary along with enabled constraints. If you do not specify this clause when creating a constraint, Oracle automatically enables the constraint.
DISABLE
VALIDATE
disables 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 exchange_partition_clause
of the ALTER
TABLE
statement 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.
DISABLE
NOVALIDATE
signifies 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 DISABLE
NOVALIDATE
state. Further, the optimizer can use constraints in DISABLE
NOVALIDATE
state.
See Also:
Oracle9i Database Performance Guide and Reference for information on when to use this setting |
If you specify neither VALIDATE
nor NOVALIDATE
, the default is NOVALIDATE
.
If you disable a unique or primary key constraint that is using a unique index, Oracle drops the unique index.
The EXCEPTIONS
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.
The EXCEPTIONS
INTO
clause is valid only when validating a constraint.
You can create the EXCEPTIONS
table using one of these scripts:
UTLEXCPT.SQL
uses physical rowids. Therefore it can accommodate rows from conventional tables but not from index-organized tables. (See the Note that follows.)
UTLEXPT1.SQL
uses 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.
Restrictions:
CREATE
TABLE
statement, because no rowids exist until after the successful completion of the statement.
See Also:
DBMS_IOT
package in Oracle9i Supplied PL/SQL Packages and Types Reference for information on the SQL scripts
The following statement is a variation of the statement that created the demo table sh.promotions
. It defines and enables a unique key on the promo_id
column (other constraints are not shown:
CREATE TABLE promotions ( promo_id NUMBER(6) CONSTRAINT promo_id_u UNIQUE , promo_name VARCHAR2(20) , promo_category VARCHAR2(15) , promo_cost NUMBER(10,2) , promo_begin_date DATE , promo_end_date DATE ) ;
The constraint promo_id_u
identifies the promo_id
column as a unique key. This constraint ensures that no two promotions in the table have the same ID. However, the constraint does allow promotions without identifiers.
Alternatively, you can define and enable this constraint with the table_or_view_constraint
syntax:
CREATE TABLE promotions ( promo_id NUMBER(6) , promo_name VARCHAR2(20) , promo_category VARCHAR2(15) , promo_cost NUMBER(10,2) , promo_begin_date DATE , promo_end_date DATE , CONSTRAINT promo_id_u UNIQUE (promo_id) USING INDEX PCTFREE 20 TABLESPACE ts_1 STORAGE (INITIAL 8K NEXT 6K) );
The above statement also uses the USING
INDEX
clause to specify storage characteristics for the index that Oracle creates to enable the constraint.
The following statement shows another way to create a unique (or primary key) constraint that gives you explicit control over the index (or indexes) Oracle uses to enforce the constraint:
CREATE TABLE promotions ( promo_id NUMBER(6) , promo_name VARCHAR2(20) , promo_category VARCHAR2(15) , promo_cost NUMBER(10,2) , promo_begin_date DATE , promo_end_date DATE , CONSTRAINT promo_id_u UNIQUE (promo_id, promo_cost) USING INDEX (CREATE UNIQUE INDEX promo_ix1 ON promotions(promo_id, promo_cost)) , CONSTRAINT promo_id_u2 UNIQUE (promo_cost, promo_id) USING INDEX promo_ix1);
This example also shows that you can create an index for one constraint and use that index to create and enable another constraint in the same statement.
The following statement defines and enables a composite unique key on the combination of the customer_id
and cust_email
columns of the oe.customers
table:
ALTER TABLE customers ADD CONSTRAINT cust_unq UNIQUE (customer_id, cust_email) USING INDEX PCTFREE 5 EXCEPTIONS INTO bad_cust_name;
The unq_city
constraint ensures that the same combination of customer_id
and cust_email
values does not appear in the table more than once.
The ADD
CONSTRAINT
clause also specifies other properties of the constraint:
USING
INDEX
clause specifies storage characteristics for the index Oracle creates to enable the constraint.
EXCEPTIONS
INTO
clause causes Oracle to write information to the bad_cust_name
table about any rows currently in the customers
table that violate the constraint.
The following statement is a variation of the statement that created the hr.locations
table. It creates the locations
table and defines and enables a primary key on the location_id
column (other constraints are omitted):
CREATE TABLE locations ( location_id NUMBER(4) CONSTRAINT loc_id_pk PRIMARY KEY , street_address VARCHAR2(40) , postal_code VARCHAR2(12) , city VARCHAR2(30) , state_province VARCHAR2(25) , country_id CHAR(2) ) ;
The loc_id_pk
constraint identifies the location_id
column as the primary key of the locations
table. This constraint ensures that no two locations in the table have the same location number and that no location number is NULL
.
Alternatively, you can define and enable this constraint with table_or_view_constraint
syntax:
CREATE TABLE locations ( location_id NUMBER(4) , street_address VARCHAR2(40) , postal_code VARCHAR2(12) , city VARCHAR2(30) , state_province VARCHAR2(25) , country_id CHAR(2) , CONSTRAINT loc_id_pk PRIMARY KEY (location_id));
The following statement defines a composite primary key on the combination of the prod_id
and cust_id
columns of the sh.sales
table:
ALTER TABLE sales ADD PRIMARY KEY (prod_id, cust_id) DISABLE;
This constraint identifies the combination of the prod_id
and cust_id
columns as the primary key of the sales
table. The constraint ensures that no two rows in the table have the same values for both the prod_id
column and the cust_id
column.
The constraint clause (PRIMARY
KEY
) also specifies the following properties of the constraint:
DISABLE
clause causes Oracle to define the constraint but not enable it.
NOT
NULL
Example
The following statement alters the locations table "Primary Key Example") to define and enable a NOT
NULL
constraint on the country_id
column:
ALTER TABLE locations MODIFY (country_id CONSTRAINT country_nn NOT NULL);
The constraint country_nn
ensures that no location in the table has a null country_id
.
The following example guarantees that a value exists for both the first_name
and last_name
attributes of the name
column in the students
table:
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 dept
table:
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) );
The constraint 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 NOT
NULL
constraint on the deptno
column in the emp
table, in addition to the REFERENCES
constraint.
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 FOREIGN
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 deptno
column.
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 table_or_view_constraint
syntax:
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 ON
DELETE
clause, causing Oracle to forbid the deletion of a department if any employee works in that department.
ON
DELETE
Example
This statement creates the emp
table, defines and enables two referential integrity constraints, and uses the ON
DELETE
clause:
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 ON
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 ON
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 emp
table.
The following statement defines and enables a foreign key on the combination of the areaco
and phoneno
columns of the phone_calls
table:
ALTER TABLE phone_calls ADD CONSTRAINT fk_areaco_phoneno FOREIGN KEY (areaco, phoneno) REFERENCES customers(areaco, phoneno) EXCEPTIONS INTO wrong_numbers;
The constraint 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 areaco
and phoneno
columns of the customers
table as a primary or unique key.
The EXCEPTIONS
INTO
clause causes Oracle to write information to the wrong_numbers
table about any rows in the phone_calls
table that violate the constraint.
CHECK
Constraint Examples
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_deptno
ensures that no department numbers are less than 10 or greater than 99.
check_dname
ensures that all department names are in uppercase.
check_loc
restricts department locations to Dallas, Boston, New York, or Chicago.
Because each 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_or_view_constraint_clause
to define and enable a CHECK
constraint:
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:
Because the 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 PRIMARY
KEY
constraint, two referential integrity constraints, a NOT
NULL
constraint, and two CHECK
constraints:
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 CHECK (quantity > 0), cost NUMBER CONSTRAINT check_cost CHECK (cost > 0) );
The constraints enable the following rules on table data:
pk_od
identifies the combination of the order_id
and part_no
columns 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 order_id
and the part_no
columns, and no row in the table can have a null in either the order_id
column or the part_no
column.
fk_oid
identifies the order_id
column as a foreign key that references the order_id
column in the order
table in scott
's schema. All new values added to the column order_detail
.order_id
must already appear in the column scott.order.order_id
.
fk_pno
identifies the part_no
column as a foreign key that references the part_no
column in the part
table owned by scott
. All new values added to the column order_detail.part_no
must already appear in the column scott.part.part_no
.
nn_qty
forbids nulls in the quantity
column.
check_qty
ensures that values in the quantity
column are always greater than zero.
check_cost
ensures 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_or_view_constraint
syntax and column definitions can appear in any order. In this example, the table_or_view_constraint
syntax that defines the pk_od
constraint precedes the column definitions.
column_constraint
syntax multiple times. In this example, the definition of the quantity
column contains the definitions of both the nn_qty
and check_qty
constraints.
CHECK
constraints. Multiple CHECK
constraints, each with a simple condition enforcing a single business rule, is better than a single CHECK
constraint 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.
DEFERRABLE
Constraint Examples
The following statement creates table games
with a NOT
DEFERRABLE
INITIALLY
IMMEDIATE
constraint check on the scores
column:
CREATE TABLE games (scores NUMBER CHECK (scores >= 0));
To define a unique constraint on a column as INITIALLY
DEFERRED
DEFERRABLE
, issue the following statement:
CREATE TABLE orders (ord_num NUMBER, CONSTRAINT unq_num UNIQUE (ord_num) INITIALLY DEFERRED DEFERRABLE);
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|