CONSTRAINT Clause

Restrict the data values that can be added to a table. Also see Constraint Clause (column)

Syntax - Table Constraint:

   CONSTRAINT constrnt_name {UNIQUE|PRIMARY KEY} column constrnt_state

   CONSTRAINT constrnt_name CHECK(condition) constrnt_state

Syntax - Table Referential Constraint:

   CONSTRAINT constrnt_name FOREIGN KEY (column,…)
       REFERENCES [schema.]table (column,…)
          [ON DELETE {CASCADE|SET NULL}] constrnt_state

'column' can be either a single column name or several columns separated with commas,

Options:

constrnt_state   
    [[NOT] DEFERRABLE] [INITIALLY {IMMEDIATE|DEFERRED}]
       [RELY | NORELY] [USING INDEX using_index_clause]
          [ENABLE|DISABLE] [VALIDATE|NOVALIDATE]
              [EXCEPTIONS INTO [schema.]table]

using_index_clause
    PCTFREE int
    INITTRANS int
    MAXTRANS int
    TABLESPACE tablespace_name
    STORAGE storage_clause
    NOSORT
    LOGGING|NOLOGGING
    {LOCAL|GLOBAL} PARTITION BY RANGE(column_list)( partition_clause,…)}

partition_clause:
   PARTITION partition VALUES LESS THAN (values list) ptn_storage

   ptn_storage:
      PCTFREE int
      PCTUSED int
      INITTRANS int
      MAXTRANS int
      STORAGE storage_clause
      TABLESPACE tablespace
      LOGGING|NOLOGGING

condition:
An expression that evaluate to TRUE, FALSE or unknown.
   Some examples:
   emp_name = 'SMITH' 
   emp_name IN ('SMITH', 'JONES', 'FRASER') 
   hiredate > '01-JAN-01'
   employees.dept_id = departments.dept_id_pk
   EMP_sal >5000 AND emp_commission IS NULL

notes:
A referential constraint with ON DELETE CASCADE will cascade deletes - so deleting a primary key row will delete all related foreign keys.
e.g. delete a customer and all that customer's orders will disappear.

This page does not cover the syntax for Object Table constraints.

Related Oracle Commands:

disable constraint - clause
drop constraint - clause
Syntax for Oracle constraints
ORA-00001 - Unique constraint violated. (Invalid data has been rejected)

Related Views:

DBA_CONSTRAINTS    ALL_CONSTRAINTS   USER_CONSTRAINTS
DBA_CONS_COLUMNS   ALL_CONS_COLUMNS  USER_CONS_COLUMNS
CONSTRAINT_COLUMNS
CONSTRAINT_DEFS
DBA_CROSS_REFS                       USER_CROSS_REFS

Valid constraint_types are:

Primary key = P
Unique Key = U
Foreign Key = R
Check, not null = C
Check (view) = V