CONSTRAINT Clause (Columns)

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

Syntax - In line Constraint:

   CONSTRAINT constrnt_name {UNIQUE|PRIMARY KEY} constrnt_state

   CONSTRAINT constrnt_name CHECK(condition) constrnt_state

   CONSTRAINT constrnt_name[NOT] NULL constrnt_state

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

Syntax - Out of line Constraint:

   CONSTRAINT constrnt_name {UNIQUE|PRIMARY KEY}(column [,column...]) constrnt_state

   CONSTRAINT constrnt_name CHECK(condition) constrnt_state

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

Syntax - Inline Column Referential Constraint:

   SCOPE IS schema.scope_table
   WITH ROWID
  [CONSTRAINT constrnt_name] REFERENCES [schema.]table (column [,column...])
      [ON DELETE {CASCADE|SET NULL}] constrnt_state [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
    Schema.index
    (CREATE INDEX statement)
    PCTFREE int
    INITTRANS int
    MAXTRANS int
    TABLESPACE tablespace_name
    STORAGE storage_clause
    SORT | 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

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