CONSTRAINT Clause

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

Syntax - Column Constraint:

   CONSTRAINT constrnt_name {UNIQUE|PRIMARY KEY} constrnt_state

   CONSTRAINT constrnt_name CHECK(condition) constrnt_state

   CONSTRAINT constrnt_name [NOT] NULL constrnt_state

Syntax - Column Referential Constraint:

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

A referential column 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

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