Oracle8i SQL Reference
Release 3 (8.1.7)

Part Number A85397-01

Library

Product

Contents

Index

Go to previous page Go to beginning of chapter Go to next page

SQL Statements:
ALTER TABLE to constraint_clause, 14 of 14


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

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

Keywords and Parameters

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.

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:

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:

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:

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.

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.  

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.

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

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:

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:

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:

"Conditions" for additional information and syntax 

Restrictions:

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:

Oracle8i 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  

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 only specify one scope table per REF column. 

 

Restrictions:

  • You can add a SCOPE constraint to an existing column only if 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  

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:

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

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

 

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.

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:

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:

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:

  • CREATE TABLE for information on these parameters

  • CREATE INDEX for a description of LOCAL and global_index_clause, and for a description of NOSORT and LOGGING|NOLOGGING in relation to indexes

 

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.

DISABLE

Specify DISABLE to disable the integrity constraint. If you do not specify this clause when creating a constraint, Oracle automatically enables the constraint.

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:

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.


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:

 

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:

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

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

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:

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:

This example also illustrates the following points about constraint clauses and column definitions:

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


Go to previous page Go to beginning of chapter Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index