----------  Create Referential ----------

Two tables are involved in a referential constraint.  The   constrained  table
is the table on which operations are to be performed.  The  constraining table
is the table which is referred to before operations can be  performed  on  the
constrained table.

To establish a referential constraint one  or  more  attributes  in  the  con-
strained  table,  the constrained attributes , are associated with one or more
attributes in the constraining table, the constraining attributes .

The data types and number of constraining attributes must be identical to  the
data type and number of the corresponding constrained attributes.

 Syntax:

create | referential | [| constraint |] [constraint name]
       | refer       |  | check      |

       [on] CONSTRAINT_LIST  | insert |  CONSTRAINT_LIST;
                             | delete |


where CONSTRAINT_LIST is any of:

            | TABLE (ATTR_NAME {,ATTR_NAME})   |
            | TABLE set ATTR_NAME {,ATTR_NAME} |

An  insert constraint implies that for a record to be inserted into  the  con-
strained  table, the values of the constrained attributes must be equal to the
values of the corresponding attributes in at least  one  record  of  the  con-
straining table.

A delete constraint implies that for a record to  be  deleted  from  the  con-
strained  table, the values of the constrained attributes must not be equal to
the values of the corresponding attributes in any record of  the  constraining
table.

If more than one entry in the constrained table matches the  constraining  at-
tributes, all but one of these entries can be deleted without reference to the
constraining table.  For the last entry to be deleted, the  criterion  of  the
delete constraint must be satisfied.

An update statement can be considered as a delete followed by an insert.   Re-
ferential constraints are evaluated accordingly.

Constrained attributes with a null value are not used in the evaluation  of  a
constraint.   If  only  a single attribute has been constrained and in a given
instance this attribute takes on a null value, the effect is  as  if  no  con-
straint had been specified for that instance.

The effects of referential constraints are cumulative.  Creating  a  new  con-
straint does not over-ride the effects of previously defined constraints.

More than one constraint can be imposed on a pair of tables.  A table  can  be
both constrained and constraining within a constraint pair.

You can view a constraint using the DISPLAY TABLE command.

During operations that involve referential constraints, tables will be  locked
at the level specified in the data dictionary.

For further information, enter:

help range_check;
help drop_referential;
