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