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

SYNTAX:

Column constraint:

[CONSTRAINT constraint]
{ [NOT] NULL
| {UNIQUE | PRIMARY KEY}
|  REFERENCES [schema.]table [(column)]
        [ON DELETE CASCADE]
|  CHECK (condition) }
{ [ USING INDEX [PCTFREE integer]
                [INITRANS integer] [MAXTRANS integer]
                [TABLESPACE tablespace]
                [STORAGE storage_clause] ]
 [ EXCEPTIONS INTO [schema.]table
| DISABLE }

Table constraint:

[CONSTRAINT constraint]
{ {UNIQUE | PRIMARY KEY} (column [,column] ...)
|  FOREIGN KEY (column [,column] ...)
         REFERENCES [schema.]table [(column [,column] ...)]
        [ON DELETE CASCADE]
| CHECK (condition) }
{ [ USING INDEX [PCTFREE integer]
                [INITRANS integer] [MAXTRANS integer]
                [TABLESPACE tablespace]
                [STORAGE storage_clause] ]
  [ EXCEPTIONS INTO [schema.]table[@dblink]
| DISABLE }

where:

CONSTRAINT
    identifies the integrity constraint by the name 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 this form:            SYS_Cn
    where
            n
                   is an integer that makes the name unique
                   within the database.

    For the names and definitions of integrity constraints, query the
    data dictionary.

NULL
    specifies that a column can contain null values.

NOT NULL
    specifies that a column cannot contain null values.

    If you do not specify NULL or NOT NULL in a column definition, NULL
    is the default.

UNIQUE
    designates a column or combination of columns as a unique key.

PRIMARY KEY
    designates a column or combination of columns as the table's primary
    key.

FOREIGN KEY
    designates a column or combination of columns as the foreign key in
    a referential integrity constraint.

REFERENCES
    identifies the primary or unique key that is referenced by a foreign key in
a referential integrity constraint.

ON DELETE CASCADE
    specifies that ORACLE maintains referential integrity by
    automatically removing dependent foreign key values if you remove a
    referenced primary or unique key value.

CHECK
    specifies a condition that each row in the table must satisfy.

USING INDEX
    specifies parameters for the index ORACLE uses to enforce 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.
    For information on these parameters, see the CREATE TABLE command.

    Only use this clause when enabling UNIQUE and PRIMARY KEY
    constraints.

EXCEPTIONS INTO
    identifies a table into which ORACLE places information about rows
    that violate an enabled integrity constraint.  This table must exist
    before you use this option.  If you omit schema, ORACLE assumes the
    exception table is in your own schema.  The exception table must be
    on your local database.

DISABLE
    disables the integrity constraint.  If an integrity constraint is
    disabled, ORACLE does not enforce it.

    If you do not specify this option, ORACLE automatically enables the
    integrity constraint.

    You can also enable and disable integrity constraints with the
    ENABLE and DISABLE clauses of the CREATE TABLE and ALTER TABLE
    commands.

PREREQUISITES:
    CONSTRAINT clauses can appear in either CREATE TABLE or ALTER TABLE
    commands.  To define an integrity constraint, you must have the
    privileges necessary to issue one of these commands.  See the CREATE
     TABLE and ALTER TABLE commands.

    Defining a constraint may also require additional privileges or
    preconditions that depend on the type of constraint.

SEE:
    ALTER TABLE, CREATE TABLE, DISABLE, ENABLE
