UNIQUE Constraints

The UNIQUE constraint designates 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 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 or as both a unique key and a cluster key. However, you can designate the same column or combination of columns as both a unique key and a foreign key.

Defining Unique Keys

You can define a unique key on a single column with column_constraint syntax.

Example

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 option to specify storage characteristics for the index that Oracle creates to enable the constraint.

Defining Composite Unique Keys

A composite unique key is a unique key made up of a combination of columns. Oracle creates an index on the columns of a unique key, so a composite unique key can contain a maximum of 16 columns. To define a composite unique key, you must use table_constraint syntax rather than column_constraint syntax.

To satisfy a constraint that designates a composite unique key, no two rows in the table can have the same combination of values in the key columns. 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.

Example

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 CONSTRAINT clause also specifies other properties of the constraint:

The USING INDEX option specifies storage characteristics for the index Oracle creates to enable the constraint.
The EXCEPTIONS option 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.

PRIMARY KEY Constraints

A PRIMARY KEY constraint designates a column or combination of columns as the table's primary key. To satisfy a PRIMARY KEY constraint, both of the following conditions must be true:

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.

A table can have only one primary key.

A primary key column cannot be of datatype LONG or LONG RAW. You cannot designate the same column or combination of columns as both a primary key and a unique key or as both a primary key and a cluster key. However, you can designate the same column or combination of columns as both a primary key and a foreign key.

Defining Primary Keys

You can use the column_constraint syntax to define a primary key on a single column.

Example

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

Defining Composite Primary Keys

A composite primary key is a primary key made up of a combination of columns. Oracle creates an index on the columns of a primary key; therefore, a composite primary key can contain a maximum of 16 columns. To define a composite primary key, you must use the table_constraint syntax rather than the column_constraint syntax.

Example

The following statement defines a composite primary key on the combination of the SHIP_NO 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:

The constraint definition does not include a constraint name, so Oracle generates a name for the constraint.
The DISABLE option causes Oracle to define the constraint but not enable it.

Referential Integrity Constraints

A referential integrity constraint designates a column or combination of columns as a foreign key and establishes a relationship between that foreign key and a specified primary or unique key, called the referenced key. In this relationship, the table containing the foreign key is called the child table and the table containing the referenced key is called the parent table. Note the following restrictions:

The child and parent tables must be on the same database. They cannot be on different nodes of a distributed database. Oracle allows you to enable referential integrity across nodes of a distributed database with database triggers. For information on how to use database triggers for this purpose, see Oracle8 Application Developer's Guide.
The foreign key and the referenced key can be in the same table. In this case, the parent and child tables are the same.

To satisfy a referential integrity constraint, each row of the child table must meet one of the following conditions:

The value of the row's foreign key must appear as a referenced key value in one of the parent table's rows. The row in the child table is said to depend on the referenced key in the parent table.
The value of one of the columns that makes up the foreign key must be null.

A referential integrity constraint is defined in the child table. A referential integrity constraint definition can include any of the following keywords:

FOREIGN KEY	identifies the column or combination of columns in the child table that makes up the foreign key. Ise this keyword only when you define a foreign key with a table constraint clause.
REFERENCES	identifies the parent table and the column or combination of columns that make up the referenced key.
	If you identify only the parent table and omit the column names, the foreign key automatically references the primary key of the parent table.
	The corresponding columns of the referenced key and the foreign key must match in number and datatypes.
ON DELETE CASCADE	allows deletion of referenced key values in the parent table that have dependent rows in the child table and causes Oracle to automatically delete dependent rows from the child table to maintain referential integrity.
	If you omit this option, Oracle forbids deletions of referenced key values in the parent table that have dependent rows in the child table.

Before you define a referential integrity constraint in the child table, the referenced UNIQUE or PRIMARY KEY constraint on the parent table must already be defined. Also, the parent table must be in your own schema or you must have REFERENCES privilege on the columns of the referenced key in the parent table. Before you enable a referential integrity constraint, its referenced constraint must be enabled.

You cannot define a referential integrity constraint in a CREATE TABLE statement that contains an AS clause. Instead, you can create the table without the constraint and then add it later with an ALTER TABLE statement.

A foreign key column cannot be of datatype LONG or LONG RAW. 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.

Defining Referential Integrity Constraints

You can use column_constraint syntax to define a referential integrity constraint in which the foreign key is made up of a single column.

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. If you wish to prevent the latter, 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. For the definition of such a constraint, see the example.

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

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

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

Note that the foreign key definitions in both statements of this example omit the ON DELETE CASCADE option, causing Oracle to forbid the deletion of a department if any employee works in that department.

Maintaining Referential Integrity with ON DELETE CASCADE

If you use the ON DELETE CASCADE option, Oracle permits deletions of referenced key values in the parent table and automatically deletes dependent rows in the child table to maintain referential integrity.

Example

This example creates the EMP table, defines and enables the referential integrity constraint FK_DEPTNO, and uses the ON DELETE CASCADE option:

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)   CONSTRAINT fk_deptno

            REFERENCES dept(deptno)

            ON DELETE CASCADE );

Because of the ON DELETE CASCADE option, 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.

Referential Integrity Constraints with Composite Keys

A composite foreign key is a foreign key made up of a combination of columns. A composite foreign key can contain as many as 16 columns. To define a referential integrity constraint with a composite foreign key, you must use table_constraint syntax. You cannot use column_constraint syntax, because this syntax can impose rules only on a single column. A composite foreign key must refer to a composite unique key or a composite primary key.

To satisfy a referential integrity constraint involving composite keys, each row in the child table must satisfy one of the following conditions:

The values of the foreign key columns must match the values of the referenced key columns in a row in the parent table.
The value of at least one of the columns of the foreign key must be null.

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 option causes Oracle to write information to the WRONG_NUMBERS table about any rows in the PHONE_CALLS table that violate the constraint.

CHECK Constraints

The CHECK constraint explicitly defines a condition. To satisfy the constraint, each row in the table must make the condition either TRUE or unknown (due to a null). For information on conditions, see the syntax description of condition in "Conditions". 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:

queries to refer to values in other rows
calls to the functions SYSDATE, UID, USER, or USERENV
the pseudocolumns CURRVAL, NEXTVAL, LEVEL, or ROWNUM
date constants that are not fully specified

Whenever 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. Oracle does not verify that CHECK conditions are not mutually exclusive.

Example I

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 option, Oracle only defines the constraints and does not enable them.

Unlike other types of constraints, a CHECK constraint defined with column_constraint syntax can impose rules on any column in the table, rather than only on the column in which it is defined.

Example II

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:

If an employee has non-null values for both salary and commission, the sum of these values must not be more than $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.

Example III

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, the following conditions must be true:
	No two rows in the table can contain the same combination of values in the ORDER_ID and the PART_NO columns. 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, note that the table_constraint syntax that defines the PK_OD constraint precedes the column definitions. In Example IV in this section, the table_constraint syntax defining the table's primary key follows 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 message identifying the constraint. Such an error message more precisely identifies the violated business rule if the identified constraint enables a single business rule.

DEFERRABLE Constraints

You can specify table and column constraints as DEFERRABLE or NOT DEFERRABLE. DEFERRABLE means that the constraint will not be checked until the transaction is committed. The default is NOT DEFERRABLE.

If you specify DEFERRABLE, you can also specify the constraint's initial state as INITIALLY DEFERRED and thereby start the transaction in DEFERRED mode. Or you can specify a DEFERRABLE constraint's initial state as INITIALLY IMMEDIATE and start the transaction in NOT DEFERRED mode.

Example I

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

Example III

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

A constraint cannot be defined as NOT DEFERRABLE INITIALLY DEFERRED.

Use SET CONSTRAINT(S) to set, for a single transaction, whether a deferrable constraint is checked following each DML statement or when the transaction is committed. You cannot alter a constraint's deferrability status; you must drop the constraint and re-create it.

See Oracle8 Administrator's Guide and Oracle8 Concepts for more information about deferred constraints.

Enabling and Disabling Constraints

Constraints can have one of three states: DISABLE, ENABLE NOVALIDATE, or ENABLE VALIDATE.

Taking a constraint from a disabled to enable validated state requires an exclusive lock on the table, because while all old data is being checked for validity, no new data can be entered into the table. Due to this behavior, only one constraint can be enabled at a time, and each new constraint must check all existing rows by serial scan.

To avoid locking the table, place the constraint in the ENABLE NOVALIDATE state, using the ENABLE clause . This state ensures that all new DML statements on the table are validated, therefore Oracle does not need to prevent concurrent access to the table.

ENABLE NOVALIDATE also allows you to place several of the table's constraints in the ENABLE VALIDATE state concurrently. Each scan that Oracle performs to validate existing data can also be performed in parallel when possible.

Placing constraints concurrently in the ENABLE VALIDATE state requires that you issue multiple ALTER TABLE commands from separate sessions.

Enabling Primary Key and Unique Key Constraints

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, thus 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 constraint. Because Oracle does not drop the nonunique index when the constraint is disabled, any ENABLE operation on a primary key or unique key constraint occurs almost instantly, because the index already exists. Redundant indexes are also eliminated.

For more information about PRIMARY KEY and UNIQUE constraints, see the ENABLE clause .

Constraints on Object Type Columns

You can create UNIQUE, PRIMARY KEY, and REFERENCES constraints on scalar attributes of object type columns. You can also create NOT NULL constraints on object type columns, and CHECK constraints that reference object type columns or any attribute of an object type column.

Example

CREATE TYPE address AS OBJECT

  ( hno NUMBER,

    street VARCHAR2(40),

    city VARCHAR2(20),

    zip VARCHAR2(5),

    phone VARCHAR2(10) );

CREATE TYPE person AS OBJECT

  ( name VARCHAR2(40),

    dateofbirth DATE,

    homeaddress address,

    manager REF person );

CREATE TABLE persons OF person

  ( homeaddress NOT NULL

      UNIQUE (homeaddress.phone),

      CHECK (homeaddress.zip IS NOT NULL),

      CHECK (homeaddress.city <> 'San Francisco') );