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.
1 – 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)0[m[4mDEFINE[m [4mCONSTRAINT[m qqqqq> name qqqqk
lqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqk
mq> [4mDESCRIPTION[m IS /* text */ qqqqj x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqqq [4mFOR[m qqq> rse qqqk
lqqqqqqqqqqqqqqqqqqqqj
mqq> [4mREQUIRE[m qqq> conditional-expr qqqqqqk
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqq> .
mq> [4mCHECK[m ON qqwqqq> [4mCOMMIT[m qqwqqj
mqqq> [4mUPDATE[m qqj
2.1 – 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.
2.2 – 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.
2.3 – rse
A record selection expression that defines which records of which
relations the constraint applies to.
2.4 – 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.
2.5 – 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.
3 – 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."