1  DEFINE_CONSTRAINT
   Creates a constraint for a relation or relations. A constraint
   defines a set of conditions that restrict the values stored in
   relations. When you store and modify field values, the constraint
   checks the validity of the values and generates an error message
   if the constraint is violated.

   Example:

      RDO>  DEFINE CONSTRAINT EMPLOYEE_ID_REQUIRED
      cont>  FOR E IN EMPLOYEES
      cont>  REQUIRE E.EMPLOYEE_ID NOT MISSING.
 

2  More
   To define a constraint, you must have Oracle Rdb READ access to the
   database and Oracle Rdb READ and DEFINE access to all relations to
   which the constraint refers.

   When the DEFINE CONSTRAINT statement executes, the constraint
   definition is added to the physical database. If you have
   invoked the database using the PATHNAME argument, the constraint
   definition is also stored in the data dictionary.

   The DEFINE CONSTRAINT statement includes a record selection
   expression. Therefore, it is more flexible for checking values
   on input than the VALID IF clause. For example:

   o  VALID IF must be part of a DEFINE FIELD statement. Therefore,
      a VALID IF criterion applies to all the fields that use the
      definition. You can define a constraint that refers to only
      one of several relations that use a global field definition.

   o  VALID IF refers only to a range of literal values. You cannot
      use VALID IF to check a value against values of fields stored
      in the database. With DEFINE CONSTRAINT, you can check values
      from one relation against other database values, either in the
      same or another relation.

   o  You can use DEFINE CONSTRAINT to check for such conditions as
      existence, uniqueness, and nonexistence.

   Oracle Rdb evaluates constraints by validating existing data against
   the RSE specified by the constraint. If there is no existing data
   for Oracle Rdb to validate, the constraint will be defined without
   being evaluated.

   Oracle Rdb evaluates constraints at definition time; therefore,
   you cannot define a new constraint that violates an existing
   constraint.

   You can also specify that a constraint be checked when the
   STORE or MODIFY statement executes (CHECK ON UPDATE) or when
   the COMMIT statement executes (CHECK ON COMMIT). In this way,
   you can include interlocking constraints. For example, you might
   define two constraints to ensure that a department cannot exist
   without employees and an employee must belong to a department.
   However, if these two constraints were checked on UPDATE, there
   would be no way to create a new department. Instead, they should
   be checked on COMMIT. You can then create a department and give
   it members inside a single transaction, and check the constraints
   when the COMMIT statement ends the transaction.

   You can define a constraint only after you have invoked the
   database. See the INVOKE statement.

   You must execute this statement in a read/write transaction.
   If there is no active transaction and you issue this statement,
   Oracle Rdb starts a read/write transaction implicitly.

   Other users are allowed to be attached to the database when you
   issue the DEFINE CONSTRAINT statement.
 

2  Format

 (B)0DEFINE CONSTRAINT qqqqq> name qqqqk
       lqqqqqqqqqqqqqqqqqqqqqqqqqqqj
       mqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqk
            mq> DESCRIPTION IS /* text */ qqqqj    x
       lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
       mqqq FOR qqq> rse qqqk
       lqqqqqqqqqqqqqqqqqqqqj
       mqq> REQUIRE qqq> conditional-expr qqqqqqk
       lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
       mqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqq> .
            mq> CHECK ON qqwqqq> COMMIT qqwqqj
                           mqqq> UPDATE qqj

 

3  name
   The name of the constraint that you can refer to in other
   statements. When choosing a name, follow these rules:

   o  Use a name that is unique among all constraint names.

   o  Use any valid OpenVMS name. However, the name cannot end in a
      dollar sign ($) or underscore (_).

   o  Do not use any Oracle Rdb reserved words.
 

3  text
   A text string that adds a comment to the field definition. You
   can apply the description to the entire relation definition
   using the DESCRIPTION keyword. You can also apply a separate
   description to each field using the text markers before the
   DEFINE, CHANGE, or DELETE clause.
 

3  rse
   A record selection expression that defines which records of which
   relations the constraint applies to.
 

3  conditional-expression
   A conditional expression that describes the constraint to be
   placed on the records and relations defined by the RSE. For more
   information on Oracle Rdb conditional expressions, ask for HELP on
   Cond_expr.
 

3  CHECK
   Specifies whether the constraint is to be evaluated when you
   issue a statement such as STORE or MODIFY that updates the
   relation (UPDATE), or when you issue the COMMIT statement to
   write the change to the database (COMMIT). You can override this
   qualifier with the EVALUATING clause of the START_TRANSACTION
   statement. The default is UPDATE.
 

2  Example
   Check for the existence of a field value in another relation:

   DEFINE CONSTRAINT DEPT_CODE_EXISTS
     FOR JH IN JOB_HISTORY
       REQUIRE ANY D IN DEPARTMENTS WITH
       D.DEPARTMENT_CODE = JH.DEPARTMENT_CODE.

   The ANY operator is equivalent to saying "there exists". This
   constraint therefore means "For every record in JOB_HISTORY,
   require that there exists a record in DEPARTMENTS where the
   DEPARTMENT_CODE values match."