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 col name or several col's 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:
These is an expression that evaluate to TRUE, FALSE or unknown.
   e.g.
   emp_name = 'SMITH' 
   emp_name IN ('SMITH', 'JONES', 'FRASER') 
   hiredate > '01-JAN-01'
   t_emp.dept_id_fk = t_dept.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 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