constraint_clause

Purpose

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.

Note: Oracle does not support constraints on columns or attributes whose type is an object, nested table, varray, REF, or LOB. The only exception is that NOT NULL constraints are supported for columns or attributes whose type is object, VARRAY, REF, or LOB.

Prerequisites

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.

See Also: CREATE TABLE and ALTER TABLE

Syntax

table_or_view_constraint::=

Text description of table_or_view_constraint

column_constraint::=

Text description of column_constraint

table_ref_constraint::=

Text description of table_ref_constraint

column_ref_constraint::=

Text description of column_ref_constraint

references_clause::=

Text description of references_clause

constraint_state::=

Text description of constraint_state

exceptions_clause::=

Text description of exceptions_clause

using_index_clause::=

Text description of using_index_clause

global_partitioned_index::=

Text description of global_partitioned_index

global_partitioning_clause::=

Text description of global_partitioning_clause

physical_attributes_clause::=

Text description of physical_attributes_clause

storage_clause: See the storage_clause.

Keywords and Parameters

table_or_view_constraint

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.

Restrictions on Table and View Constraints

For table constraints:

• You cannot define a constraint on an attribute or column whose type is user defined.

In addition, for view constraints:

• You can specify only unique, primary key, and foreign key constraints on views.

• Because view constraints are not enforced directly, you cannot specify any DEFERRED or DEFERRABLE clause.

• View constraints are supported only in DISABLE NOVALIDATE mode. You cannot specify any other mode.

• You cannot specify the using_index_clause, the EXCEPTIONS INTO clause, or the ON DELETE clause in view constraints.

• You cannot define view constraints on attributes of an object column or on attributes of an object attribute.

column_constraint

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.

• The 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:

• The only column constraint allowed on an XMLType column is NOT NULL.

• 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:

• You can specify a NOT NULL constraint on columns or attributes of user-defined object type, varray, and LOB.

• You can specify NOT NULL and referential integrity constraints on a column of type REF.

UNIQUE Clause

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.

Note: When you specify a UNIQUE constraint on one or more columns, Oracle implicitly creates an index on the unique key. If you are defining uniqueness for purposes of query performance, Oracle Corporation recommends that you instead create the unique index explicitly using a CREATE UNIQUE INDEX statement. See CREATE INDEX for more information.

Restrictions:

• None of the columns in the unique key can have datatype TIMESTAMP WITH TIME ZONE. However, the unique key can contain a column of TIMESTAMP WITH LOCAL TIME ZONE.

• For a composite unique key, no two rows in the table or view can have the same combination of values in the key columns.

• A composite unique key cannot have more than 32 columns. The overall size of the key (in bytes) should not exceed approximately the width of all indexed columns plus the number of indexed columns.

• A unique key column cannot be of datatype LONG or LONG RAW.

• You cannot designate the same column or combination of columns as both a unique key and a primary key.

PRIMARY KEY Clause

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:

• A table or view can have only one primary key.

• None of the columns in the primary key can have datatype LONG, LONG RAW, VARRAY, NESTED TABLE, OBJECT, LOB, BFILE, or REF, or TIMESTAMP WITH TIME ZONE.

• No primary key value can appear in more than one row in the table.

• No column that is part of the primary key can contain a null.

• The size of the 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.)

• A composite primary key cannot have more than 32 columns. The overall size of the key (in bytes) should not exceed approximately the width of all indexed columns plus the number of indexed columns.

• You cannot designate the same column or combination of columns as both a primary key and a unique key.

• You cannot specify a primary key when creating a subview. The primary key can be specified only for the top-level (root) view.

NULL | NOT NULL

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.

NULL

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.

NOT NULL

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:

• You cannot specify NULL or NOT NULL for a view.

• You cannot specify NULL or NOT NULL for an attribute of an object. Instead, use a CHECK constraint with the IS [NOT] NULL condition.

  See Also: "Attribute-Level Constraints Example"

Referential Integrity Constraints

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.

• From the table or view level, specify referential integrity using the 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.

• From the column level, use the 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.

Restrictions on Referential Integrity Constraints

• A foreign key cannot be of type LONG or LONG RAW.

• The referenced UNIQUE or PRIMARY KEY constraint on the parent table or view must already be defined.

• The child and parent tables must be on the same database. To enable referential integrity constraints across nodes of a distributed database, you must use database triggers.

• If either the child or parent object is a view, then the constraint is subject to all restrictions on view constraints. See "Restrictions on Table and View Constraints".

• You cannot define a referential integrity constraint in a 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.

  See Also: Oracle9i Application Developer's Guide - Fundamentals

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 view, or the value of at least one of the columns of the foreign key must be null.

REFERENCES Clause

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.

ON DELETE Clause

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.

• Specify CASCADE if you want Oracle to remove dependent foreign key values.

• Specify SET NULL if you want Oracle to convert dependent foreign key values to NULL.

Restrictions on Foreign Keys

• A composite foreign key cannot have more than 32 columns. The overall size of the key (in bytes) should not exceed approximately the width of all indexed columns plus the number of indexed columns.

• A composite foreign key must refer to a composite unique key or a composite primary key.

CHECK Constraints

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.

See Also: Chapter 5, "Conditions" for additional information and syntax

Restrictions on CHECK Constraints

• You cannot specify a CHECK constraint for a view.

• The condition of a 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:
  • Subqueries

  • Calls to the functions SYSDATE, UID, USER, or USERENV

  • The pseudocolumns CURRVAL, NEXTVAL, LEVEL, or ROWNUM

  • Date constants that are not fully specified

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.

See Also: Oracle9i Database Concepts for more information on REFs and "Referential Integrity Constraints"

ref_column

Specify the name of a REF column of an object or relational table.

ref_attribute

Specify an embedded REF attribute within an object column of a relational table.

SCOPE Clause

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

• You cannot add a SCOPE constraint to an existing column unless the table is empty.

• You cannot specify SCOPE for the REF elements of a varray column.

• You must specify this clause if you specify AS subquery and the subquery returns user-defined REFs.

• The 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.

• You cannot drop a SCOPE table constraint from a REF column.

WITH ROWID Clause

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

• You cannot specify a WITH ROWID constraint for the REF elements of a varray column.

• You cannot drop a WITH ROWID constraint from a REF column.

• If the REF column or attribute is scoped, then this clause is ignored and the rowid is not stored with the REF value.

references_clause

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

• If you add a referential integrity constraint to an existing REF column that is scoped, then the referenced table must be the same as the scope table of the REF column.

• The system adds a scope constraint when you add a referential integrity constraint to an existing unscoped REF column. Therefore, all the restrictions that apply for the SCOPE constraint also apply in this case.

• If you later drop the referential integrity constraint, the REF column will remain scoped to the referenced table.

constraint_state

Use the constraint_state to specify how and when Oracle should enforce the constraint.

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: SET CONSTRAINT[S] for information on checking constraints after each DML statement Oracle9i Database Administrator's Guide and Oracle9i Database Concepts for more information about deferred constraints

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.

INITIALLY IMMEDIATE

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.

INITIALLY DEFERRED

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

• You cannot defer a NOT DEFERRABLE constraint with the SET CONSTRAINT(S) statement.

• You cannot specify either DEFERRABLE or NOT DEFERRABLE if you are modifying an existing constraint directly (that is, by specifying the ALTER TABLE ... MODIFY constraint statement).

• You cannot alter a constraint's deferrability status. You must drop the constraint and re-create it.

• You cannot specify these clauses for a view constraint.

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: 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).

• You cannot set a NOT NULL constraint to RELY.

using_index_clause

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.

• If you specify 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.

• If you specify the 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.

• If you neither specify an existing index or create a new index, Oracle creates the index. In this case:
  • The index receives the same name as 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.

    See Also: "Explicit Index Control Example" for an example of how you can create an index for Oracle to use in enforcing a constraint

Restrictions on the using_index_clause

• You cannot specify this clause for a view constraint.

• You can specify the using_index_clause only when enabling UNIQUE and PRIMARY KEY constraints.

• You cannot specify an index (schema.index) or create an index (create_index_statement) when enabling the primary key of an index-organized table.

  See Also: segment_attributes_clause of CREATE TABLE for information on the INITRANS, MAXTRANS, TABLESPACE, STORAGE, and PCTFREE parameters CREATE INDEX for a description of LOCAL and global_partitioned_index clause, and for a description of NOSORT and LOGGING|NOLOGGING in relation to indexes

global_partitioned_index

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.

PARTITION BY RANGE

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.

column_list

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.

Note: If your enterprise has or will have databases using different character sets, use caution when partitioning on character columns. The sort sequence of characters is not identical in all character sets.

See Also: Oracle9i Globalization Support Guide for more information on character set support

PARTITION

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_Pn.

VALUES LESS THAN

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.

Note: If index is partitioned on a DATE column, and if the date format does not specify the first two digits of the year, you must use the TO_DATE function with a 4-character format mask for the year. The date format is determined implicitly by NLS_TERRITORY or explicitly by NLS_DATE_FORMAT.

NOSORT Clause

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 Clause

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.

DISABLE Clause

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.

exceptions_clause

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:

• You cannot specify this clause for a view constraint.

• You cannot specify this clause in a CREATE TABLE statement, because no rowids exist until after the successful completion of the statement.

  Note: 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.

  See Also: Oracle9i Database Migration for compatibility issues related to the use of these scripts The DBMS_IOT package in Oracle9i Supplied PL/SQL Packages and Types Reference for information on the SQL scripts Oracle9i Database Performance Guide and Reference for information on eliminating migrated and chained rows

Examples

Unique Key Example

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.

Explicit Index Control Example

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.

Composite Unique Key Example

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:

• The USING INDEX clause specifies storage characteristics for the index Oracle creates to enable the constraint.

• The 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.

Primary Key Example

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));

Composite Primary Key Example

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:

• The constraint definition does not include a constraint name, so Oracle generates a name for the constraint.

• The 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.

Attribute-Level Constraints Example

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));

Referential Integrity Constraint Example

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.

Composite Referential Integrity Constraint Example

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:

• If an employee has non-null values for both salary and commission, the sum of these values must not exceed $5000 to satisfy the constraint.

• If an employee has a null salary or commission, the result of the condition is unknown and the employee automatically satisfies the constraint.

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.

• A column definition can use 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.

• A table can have multiple 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);