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

To constrain the maximum value stored in a NUMBER column, a simple alternative is to set a PRECISION on the table column, this restricts the length (i.e. number of digits) that can be inserted.

This page does not cover the syntax for 'Object Table' Constraints.

Related Oracle 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


Copyright © SS64.com 1999-2018
Some rights reserved