René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -
 

Constraint clause [Oracle SQL]

Four constraint clauses

Inline constraint

[ constraint constraint-name ] 
{ reference-clause | not null | unique | primary key | check (condition) }
[ constraint-state ]
The inline constraint clause appears as part within the
A foreign key constraint must be established with the reference-clause.
It is not possible to establish a foreign key on a global temporary table. If tried, Oracle issues a ORA-14455: attempt to create referential integrity constraint on temporary table.

Example for inline constraints

create table inline_constraints_pk (
  id     number       primary key,
  data   varchar2(20) constraint ck_init_cap check (data=initcap(data))
);

create table inline_constraints_fk (
  id     constraint fk_named references inline_constraints_pk,
  str    varchar2(20) not null
);

Inline ref constraint

{ scope is scope-table } |
{ with rowid } |
{ [ constraint constraint-name ] references-clause [ constraint-state ] }
An inline ref constraint clause appears as part of the column definition clause or the object properties clause.
As far as I can see, out of line ref constraints are only possible for columns containing a ref to an object.

Example for an inline ref constraint

create type item_obj as object (
  description varchar2(20),
  price       number
)
/

create table item_tab of item_obj;

create table sales (
  customer   varchar2(20),
  item_ref   ref item_obj scope is item_tab
);

Out of line constraint

An out of line constraint appears as part within a relational properties clause or the object properties clause. 
[ constraint constraint-name ] unique 
(col-name_1 [, col_name_n ...] )
[ constraint-state ]
or 
[ constraint constraint-name ] primary key
(col-name_1 [, col_name_n ... ] ) 
[ constraint-state ]
or 
[ constraint constraint-name ] check (condition)
[ constraint-state ]
or 
[ constraint constraint-name ] foreign key (col-name_1 [, col_name_n ... ] ) reference-clause
[ constraint-state ]
A not null constraint, unlike in inline constraints, can not be specified in an out of line constraint.
A foreign key constraint must be established with the reference-clause.
It is not possible to establish a foreign key on a global temporary table. If tried, Oracle issues a ORA-14455: attempt to create referential integrity constraint on temporary table.

Example for an out of line constraint

create table out_of_line_constraint_tab (
  id   number,
  str  varchar2(10),
  dt   date,
  p_id,
  p_dt,
  constraint out_of_line_constraint_pk primary key (id, str),
  foreign key (p_id, p_dt) references out_of_line_constraint_tab,
  check (dt > to_date('01.01.2000','dd.mm.yyyy'))
);

Out of line ref constraint

{ scope for ( ref-column | ref-attribute ) is scope-table } |
{ ref ( ref-column | ref-attribute ) with rowid } |
{ [ constraint constraint-name ] foreign key ( [ ref-column | ref-attribute ] ) reference-clause [ constraint-state ] }
An out of line constraint appears as part within a relational properties clause or the object properties clause.
As far as I can see, out of line ref constraints are only possible for columns containing a ref to an object.

Constraint state

[ [ not ] deferrable ]
[ initially { immediate | deferred } ]
[ [ { enable | disable } ] [ { validate | novalidate } ] ]
[ rely | norely ]
[ using-index-clause ]
[ exception-clause ]
The constraint state clause appears as part within the
The rely and norely clauses are only valid in alter table statements to modify an existing constraint.