Creates a relation definition. A relation definition consists
of a list of fields that make up an Oracle Rdb record. When the
DEFINE RELATION statement executes, Oracle Rdb adds any constraints
associated with the relation definition to the physical database.
Example:
RDO> DEFINE RELATION DEPARTMENTS.
cont> DEPARTMENT_CODE
cont> PRIMARY KEY.
cont> DEPARTMENT_NAME.
cont> MANAGER_ID
cont> BASED ON ID_NUMBER.
cont> BUDGET_PROJECTED
cont> BASED ON BUDGET.
cont> BUDGET_ACTUAL
cont> BASED ON BUDGET.
cont> END DEPARTMENTS RELATION.
1 – More
You need the Oracle Rdb DEFINE privilege for the database to define
a relation. If you are defining a constraint as part of the
relation definition, you must also have the Oracle Rdb DEFINE
privilege for the relation referenced by the constraint.
You can copy a shareable relation definition from the data
dictionary into the database using the FROM PATHNAME clause of
the DEFINE RELATION statement.
When the DEFINE RELATION statement executes, Oracle Rdb:
o Adds the relation definition to the physical database. If you
have invoked the database with the PATHNAME specification, the
definition is also added to the data dictionary.
o Creates a default access control list (ACL) for the relation.
There are several ways to use the DEFINE RELATION statement to
specify the attributes of fields:
o When you define a relation, you can simply list the names of
fields defined globally for the database, or you can define
fields explicitly.
o You can also base the local field definitions on existing
global definitions, but give them local attributes. For
example, when you use the BASED ON qualifier, the field takes
the local name you give it in the DEFINE RELATION statement,
but it derives its other attributes from the global field on
which it is based.
o You can also use the DATATRIEVE clauses both globally and
locally. When you use the DATATRIEVE clauses within the
DEFINE RELATION statement, they override the DATATRIEVE
characteristics specified for the global definition.
You must execute this statement in a read/write transaction. If
you issue this statement when there is no active transaction,
Oracle Rdb starts a read/write transaction implicitly.
Other users are allowed to be attached to the database when you
issue the DEFINE RELATION statement.
You can have a maximum of 2000 fields in a relation.
If you define a relation using an Oracle CDD/Repository path name, the
relation name must match the record name. For example, the following
statement contains an error. (The statement can be processed, but
problems will occur later.)
RDO> DEFINE RELATION AAAA FROM PATHNAME 'CDD$TOP.TEST.XXXX'.
The correct form of the statement is as follows:
RDO> DEFINE RELATION AAAA FROM PATHNAME 'CDD$TOP.TEST.AAAA'.
2 – Format
(B)0[m[4mDEFINE[m [4mRELATION[m qq> name qqwqwqqqqqqqqqqqqqq>qqqqqqqqqqqqqqwqqqqqqqk
x mq> [4mFROM[m [4mPATHNAME[m path-name qqj x
mqwqqqqqqqqqqqqqq>qqqqqqqqqqqqqqwqqk x
tq> [4mENABLE[m qqwq> [4mCOMPRESSION[m qj x x
mq> [4mDISABLE[m qj x x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x
mwwqqqqqqqqqqqq>qqqqqqqqqqqqqqwwwqqqqqqqqqqqqqq>qqqqqqqqqqqwq> . k x
xmq relation-constraint-def qjxmq[4mDESCRIPTION[m [4mIS[m /* text */j x x
mqqqqqqqqqqqqqq<qqqqqqqqqqqqqqj x x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x
mwqwqqqqqq>qqqqqwq field-def qqqqqw> [4mEND[m qwqqqqq>qqqqw> RELATION qqvq> .
x m> /*text*/ qj x mqq> name qj
mqqqqqqqqqqqqqqqq .<qqqqqqqqqqqqqj
2.1 – name
The name of the relation definition you want to create. When
choosing a name, follow these rules:
o Use a name that is unique among all relation and view names in
the database.
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 – path-name
A full or relative data dictionary path name specifying the
source of the shareable relation definition. If you use a
relative path name, the current default directory must include
all the path name segments that precede the relative path name.
You must invoke the database by path name if you plan to copy
a shareable relation definition from the dictionary. You
must specify a CDO path name and refer to a relation that was
created with CDO. The relation definition that you copy from
the dictionary must be a simple definition, that is, one that
contains no nested records, no repeating groups (arrays),
and no variants. Also, the data types of the individual field
definitions that make up the dictionary record being copied must
be compatible with supported Oracle Rdb data types.
2.3 – text
A text string that adds a comment to the relation definition or
the field definition.
2.4 – COMPRESSION clause
The ENABLE/DISABLE COMPRESSION option in the DEFINE RELATION
statement is obsolete. Use the DEFINE STORAGE MAP and CHANGE
STORAGE MAP statements to control data compression. The
COMPRESSION option in the DEFINE RELATION statement is maintained
for compatibility with applications that used previous versions
of Oracle Rdb. However, Rdb recommends you use the DEFINE STORAGE
MAP statement rather than the DEFINE RELATION statement to control
data compression.
2.5 – field-def
The name of a field that is part of the set of generic field
definitions for the database.
(B)0[mfield-def=
qwq> global-field-name qqqqqqqqqqwqwwqqqqqqqqqq>qqqqqqqqqqqqqqqwqwq>
tq> global-field-def qqqqqqqqqqqu xmq> field-constraint-def qu x
tq> local-based-on-def qqqqqqqqqj mqqqqqqqqqqqqqqqqqqqqqqqqqqqj x
mq> local-computed-by-def qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
You can use a field name in any one of three ways in the DEFINE
RELATION statement:
o Refer to an existing global field by name. This includes the
global definition in the relation.
o Refer to a new global field name and include a complete
definition, including a DATATYPE clause. This includes the
field definition in the relation and also enters the field
definition in the global set of definitions for the database.
o Refer to an existing global field in a BASED ON clause. This
causes the field to have a local name and a global definition.
2.5.1 – field-constraint-def
Using the field-constraint-def clause you can name or specify the
type of field-level constraints to be defined within a specific
relation definition.
(B)0[mfield-constraint-def =
qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqk
mq> [4mCONSTRAINT[m constraint-name IS qqj x
lqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqj
mwq> [4mNOT[m [4mMISSING[m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqk
tq> [4mUNIQUE[m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x
tq> [4mPRIMARY[m [4mKEY[m qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x
mw> [4mREFERENCES[m referenced-relation-name qwqqqqqqqqqqqqqqqqqqqqqqqqwu x
x m> referenced-field-name jx x
x x x
mq> [4mUSING[m rse [4mREQUIRE[m conditional-expr qqqqqqqqqqqq>qqqqqqqqqqqqqqj x
x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mwqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>
mqq> [4mCHECK[m ON qwqq> [4mCOMMIT[m qu
mqq> [4mUPDATE[m qj
2.5.1.1 – constraint-name
The name of a field constraint associated with the relation that
is being defined. This name must be unique within the database.
The constraint name can be referred to in other statements such
as CHANGE RELATION, SHOW CONSTRAINT, and START_TRANSACTION.
The clause 'CONSTRAINT constraint-name IS' is optional. If you
do not specify the keyword CONSTRAINT, Oracle Rdb provides a name
for the constraint. However, Rdb recommends that you always
name field and relation constraints. The alternative is to have
constraints named by the database system with names such as
LAST_NAME_REQUIRE_0001.
2.5.1.2 – NOT_MISSING
Restricts field values such that none of the values for the
specified field can assume either the defined or default missing
value for that field. You can only explicitly declare the NOT
MISSING clause at the field level.
2.5.1.3 – UNIQUE
This clause names a field in the relation which is a part of
a unique key. This field name can appear only once in the key
definition.
The UNIQUE clause limits field values such that no two rows in
the associated relation can have the same non-missing values for
the specified field or fields.
2.5.1.4 – PRIMARY
This clause names a field in the relation which is a part of a
primary key. This field name can appear only once in the base
relation. Oracle Rdb requires that the values in a primary key
be unique and not missing; therefore, you need not specify the
UNIQUE and NOT MISSING field constraints for a field that you
designate a primary key. Only one primary key can be declared for
a relation.
2.5.1.5 – referenced-relation-name
The name of the relation that defines the unique or primary
key definition which is referred to by a foreign key of this
relation. If there are no referenced-field-names specified with
this relation-name, then the referenced-relation must have an
associated constraint which specifies a primary key. If there
are referenced-field-names, the referenced-relation must have a
unique or primary key constraint defined which specifies a list
of unique-field-names. These names have to be the same names as
in the referenced-relation.
2.5.1.6 – referenced-field-name
Specifies the name of a field in the foreign key relation that
corresponds to the field with the same ordinal position within
the list of fields referred to by the primary key relation.
2.5.1.7 – rse
A record selection expression that defines which records of which
relations will be tested against the conditional expression. This
rse cannot refer to any host variables.
2.5.1.8 – conditional-expression
An expression that describes the optional conditions that must be
satisfied before the record can be stored in the database.
2.5.1.9 – CHECK
Declares the time when the constraint is evaluated. The
referential constraint can be evaluated when the update occurs
(CHECK ON UPDATE) or when a COMMIT is issued (CHECK ON COMMIT).
The EVALUATING clause of the START_TRANSACTION statement can
override the CHECK ON clause.
2.6 – relation-constraint-def
Using the relation-constraint-def clause you can name or specify
the type of relation-level constraints to be defined within a
specific relation definition.
(B)0[mrelation-constraint-def =
qwq> [4mCONSTRAINT[m constraint-name IS qqwqk
mqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqj x
lqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqj
twq> [4mUNIQUE[m qqqqqqw>qqw> unique-field-name qqqwqqwqqqk
xmq> [4mPRIMARY[m [4mKEY[m qj mqqqqqqqqq , qqqq<qqqqqqj x x
tqq> [4mFOREIGN[m [4mKEY[m qw> referencing-field-name qwqk x x
x mqqqqqqqqqqqqq , qqqq<qqqqqj x x x
x lqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqj x x
x mq> [4mREFERENCES[m referenced-relation-nameqqqqqqqkx x
x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqjx x
x mqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqwqqqqqu x
x mw> referenced-field-name wj x x
x mqqqqqqqqqq , qqqq<qqqqqqj x x
mqqq> [4mUSING[m rse [4mREQUIRE[m conditional-expr qqqqqqqqj x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqj
mwqqqqqqqqqqqqqqqqqqqqqqqqqwq>
m> [4mCHECK[m ON qwqq> [4mCOMMIT[m qu
mqq> [4mUPDATE[m qj
2.6.1 – constraint-name
The name of a relation or field constraint associated with the
relation that is being defined. This name must be unique within
the database. The constraint name can be referred to in other
statements such as CHANGE RELATION, SHOW CONSTRAINT, and START_
TRANSACTION.
The clause 'CONSTRAINT constraint-name IS' is optional. If you
do not specify the keyword CONSTRAINT, Oracle Rdb provides a name
for the constraint. However, Rdb recommends that you always name
field and relation constraints. The alternative is to have
constraints named by the database system with names such as
LAST_NAME_REQUIRE_0001.
2.6.2 – UNIQUE
This clause names a field in the relation which is a part of
a unique key. This field name can appear only once in the key
definition.
A UNIQUE clause or a PRIMARY KEY clause appearing at the relation
level declares one or more fields to comprise a single unique or
primary key.
The UNIQUE clause limits field values such that no two rows in
the associated relation can have the same non-missing values for
the specified field or fields.
2.6.3 – PRIMARY
This clause names a field in the relation which is a part of a
primary key. This field name can appear only once in the base
relation. Oracle Rdb requires that the values in a primary key
be unique and not missing; therefore, you need not specify the
UNIQUE and NOT MISSING field constraints for a field that you
designate a primary key. Only one primary key can be declared for
a relation.
2.6.4 – FOREIGN
This clause names one or more fields that you want to declare as
a foreign key in the relation you are defining.
2.6.5 – referencing-field-name
The name of a field in the relation which is part of a foreign
key. This name can appear only once in the referencing
definition, and must correspond to a field having the same
ordinal position in any list of referenced-fields. The names
can be different but the fields must be of the same data type,
length, and scale.
At the relation level, a constraint can have one or more
referencing-field-names that correspond to a matching list of
referenced-field-names.
2.6.6 – referenced-relation-name
The name of the relation that defines the unique or primary
key definition which is referred to by a foreign key of this
relation. If there are no referenced-field-names specified with
this relation-name, then the referenced-relation must have an
associated constraint which specifies a primary key. If there
are referenced-field-names, the referenced-relation must have a
unique or primary key constraint defined which specifies a list
of unique-field-names. These names have to be the same names as
in the referenced-relation.
2.6.7 – referenced-field-name
Specifies the name of a field in the foreign key relation that
corresponds to the field with the same ordinal position within
the list of fields referred to by the primary key relation.
In a relation constraint definition you can repeat referenced
field names.
2.6.8 – rse
A record selection expression that defines which records of which
relations will be tested against the conditional expression. This
rse cannot refer to any host variables.
2.6.9 – conditional-expr
An expression that describes the optional conditions that must be
satisfied before the record can be stored in the database.
2.6.10 – CHECK
Declares the time when the constraint is evaluated. The
referential constraint can be evaluated when the update occurs
(CHECK ON UPDATE) or when a COMMIT is issued (CHECK ON COMMIT).
The EVALUATING clause of the START_TRANSACTION statement can
override the CHECK ON clause.
3 – Examples
Example 1
The following example uses DEFINE RELATION to create a relation:
DEFINE RELATION DEPARTMENTS.
DEPARTMENT_CODE.
DEPARTMENT_NAME.
MANAGER_ID BASED ON ID_NUMBER.
END DEPARTMENTS RELATION.
This statement names the new relation, DEPARTMENTS, and specifies
its fields.
o DEPARTMENT_CODE and DEPARTMENT_NAME are already defined. The
relation definition simply uses their names.
o MANAGER_ID is a local name, but it points to an existing
global field definition. If the definition of ID_NUMBER
changes, MANAGER_ID changes also.
Example 2
The following example defines global fields in the DEFINE
RELATION statement:
DEFINE RELATION FAMILY
DESCRIPTION IS /* Family information */.
/* Employee ID * /
EMPLOYEE_ID BASED ON ID_NUMBER
QUERY_NAME FOR DTR IS "EMP".
/* Married? M or S */
MARITAL_STATUS
DATATYPE TEXT SIZE 1
VALID IF MARITAL_STATUS = "M" OR
MARITAL_STATUS = "S".
/* Number of dependents */
NUMBER_DEPENDENTS
DATATYPE SIGNED WORD SCALE 0.
/* Amount of IRS withholding */
WITHHOLDING
COMPUTED BY 0.20 / NUMBER_DEPENDENTS.
END FAMILY RELATION.
This DEFINE RELATION statement defines several new fields:
o The DESCRIPTION clause and the other text fields provide
commentary for the relation definition and for each field.
o EMPLOYEE_ID is a local name for the global ID_NUMBER field.
The QUERY_NAME clause overrides any QUERY_NAME clause on ID_
NUMBER.
o MARITAL_STATUS uses the DATATYPE clause. Therefore, MARITAL_
STATUS becomes a global field definition. MARITAL_STATUS is
entered in the list of global fields for the database, and
other relations can use it by name.
o NUMBER_DEPENDENTS also becomes a global field definition.
o WITHHOLDING is a local field, defined in terms of NUMBER_
DEPENDENTS.
Example 3
The following example copies a shareable relation definition from
the data dictionary into the database:
DEFINE RELATION EMP_INFO
FROM PATHNAME 'DISK1:[DICTIONARY]CORP.PERS.EMP_INFO'.