Release 3 (8.1.7)
Part Number A85397-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8i SQL Reference Release 3 (8.1.7) Part Number A85397-01 |
|
SQL Statements:
ALTER TABLE to constraint_clause, 14 of 14
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 or an index-organized table.
Constraint clauses can appear in either CREATE TABLE or ALTER 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.
table_ref_constraint::=
column_ref_constraint::=
constraint_state::=
using_index_clause::=
global_index_clause::=
global_partition_clause::=
physical_attributes_clause::=
storage_clause: See the storage_clause.
table_constraint
The 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.
The 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.
column_constraint
The 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_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.
Restriction: The only column constraint allowed on a VARRAY column is NOT NULL. However, you can specify any type of column constraint on the scalar attributes of a NESTED TABLE column.
CONSTRAINT
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_Cn.
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.
UNIQUE
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_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:
LONG or LONG RAW.
PRIMARY KEY
Specify PRIMARY 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 column_constraint syntax.
Restrictions:
LONG, LONG RAW, VARRAY, NESTED TABLE, OBJECT, LOB, BFILE, or REF.
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.)
NULL | NOT NULL
Indicate whether a column can contain nulls. You must specify NULL and NOT NULL with column_constraint syntax, not with table_constraint syntax.
Restriction: You cannot specify 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 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.
foreign_key_clause with the table_constraint syntax. This syntax allows you to 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. Also, a single column can be part of more than one foreign key.
Restrictions on referential integrity constraints:
LONG or LONG RAW.
UNIQUE or PRIMARY KEY constraint on the parent table 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.
foreign_key_clause
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 the value of at least one of the columns of the foreign key must be null.
Restrictions:
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:
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:
table_ref_constraint and column_ref_constraint
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.
DEFERRABLE | NOT DEFERRABLE
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.
Restrictions:
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).
RELY | NORELY
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:
Oracle8i Data Warehousing Guide for more information on materialized views and query rewrite |
Restrictions:
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.
using_index_clause
The using_index_clause lets you specify parameters for the index Oracle uses to enable a UNIQUE or PRIMARY KEY constraint. The name of the index is the same as the name of the constraint.
You can choose the values of the INITRANS, MAXTRANS, TABLESPACE, STORAGE, and 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 UNIQUE and PRIMARY KEY constraints.
|
See Also:
|
NOSORT
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.
ENABLE
Specify 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.
ENABLE VALIDATE additionally 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 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 inserting data into the column.)
ENABLE NOVALIDATE ensures 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
Specify DISABLE to disable the integrity constraint. 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.
VALIDATE nor NOVALIDATE, the default is NOVALIDATE.
EXCEPTIONS INTO
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.
Restriction: You cannot specify this clause in a CREATE TABLE statement, because no rowids exist until after the successful completion of the statement.
|
See Also:
|
The following statement creates the dept table and defines and enables a unique key on the dname column:
CREATE TABLE dept (deptno NUMBER(2), dname VARCHAR2(9) CONSTRAINT unq_dname UNIQUE, loc VARCHAR2(10) );
The constraint 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 table_constraint syntax:
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 USING 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 city and state columns of the census table:
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;
The unq_city_state constraint ensures that the same combination of city and state 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_keys_in_ship_cont table about any rows currently in the census table that violate the constraint.
The following statement creates the dept table and defines and enables a primary key on the deptno column:
CREATE TABLE dept (deptno NUMBER(2) CONSTRAINT pk_dept PRIMARY KEY, dname VARCHAR2(9), loc VARCHAR2(10) );
The 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 NULL.
Alternatively, you can define and enable this constraint with table_constraint syntax:
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 ship_nop and container_no columns of the ship_cont table:
ALTER TABLE ship_cont ADD PRIMARY KEY (ship_no, container_no) DISABLE;
This constraint identifies the combination of the ship_no and 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 container_no column.
The CONSTRAINT clause 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 emp table and defines and enables a NOT NULL constraint on the SAL column:
ALTER TABLE emp MODIFY (sal NUMBER CONSTRAINT nn_sal NOT NULL);
nn_sal ensures that no employee in the table has a null salary.
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 theemp 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_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_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_low 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_constraint syntax and column definitions can appear in any order. In this example, the table_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-2000, Oracle Corporation. All Rights Reserved. |
|