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