SQL Statements (continued), 5 of 6

constraint_clause

Syntax

table_constraint::=

column_constraint::=

table_ref_constraint::=

column_ref_constraint::=

references_clause::=

constraint_state::=

using_index_clause::=

global_index_clause::=

global_partition_clause::=

foreign_key_clause::=

physical_attributes_clause::=

storage_clause: See the "storage_clause".

Purpose

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.

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 either CREATE TABLE or ALTER TABLE statements. To define an integrity constraint, you must have the privileges necessary to issue one of these statements. See "CREATE TABLE" and "ALTER TABLE".

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.

Keywords and Parameters

Examples

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

Composite Unique Key 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 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_KEYS_IN_SHIP_CONT table about any rows currently in the CENSUS table that violate the constraint.

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

Composite Primary Key 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 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.

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