1  DEFINE_RELATION
   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.
 

2  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)0DEFINE RELATION qq> name qqwqwqqqqqqqqqqqqqq>qqqqqqqqqqqqqqwqqqqqqqk
                            x mq> FROM PATHNAME path-name qqj       x
                            mqwqqqqqqqqqqqqqq>qqqqqqqqqqqqqqwqqk    x
                              tq> ENABLE qqwq> COMPRESSION qj  x    x
                              mq> DISABLE qj                   x    x
 lqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj    x
 mwwqqqqqqqqqqqq>qqqqqqqqqqqqqqwwwqqqqqqqqqqqqqq>qqqqqqqqqqqwq> . k x
  xmq relation-constraint-def qjxmqDESCRIPTION IS /* text */j     x x
  mqqqqqqqqqqqqqq<qqqqqqqqqqqqqqj                                 x x
 lqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x
 mwqwqqqqqq>qqqqqwq field-def qqqqqw> END qwqqqqq>qqqqw> RELATION qqvq> .
  x m> /*text*/ qj                 x       mqq> name qj
  mqqqqqqqqqqqqqqqq .<qqqqqqqqqqqqqj

 

3  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.
 

3  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.
 

3  text
   A text string that adds a comment to the relation definition or
   the field definition.
 

3  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.
 

3  field-def
   The name of a field that is part of the set of generic field
   definitions for the database.

 (B)0field-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.
 

4  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)0field-constraint-def =
 qwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqk
  mq> CONSTRAINT constraint-name IS qqj x
 lqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqj
 mwq> NOT MISSING qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqk
  tq> UNIQUE qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x
  tq> PRIMARY KEY qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqu x
  mw> REFERENCES referenced-relation-name qwqqqqqqqqqqqqqqqqqqqqqqqqwu x
   x                                       m> referenced-field-name jx x
   x                                                                 x x
   mq> USING rse REQUIRE conditional-expr qqqqqqqqqqqq>qqqqqqqqqqqqqqj x
                                                                       x
 lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
 mwqqqqqqqqqqqqqqqqqqqqqqqqqqqwq>
  mqq> CHECK ON qwqq> COMMIT qu
                 mqq> UPDATE qj

 

5  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.
 

5  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.
 

5  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.
 

5  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.
 

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.
 

5  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.
 

5  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.
 

5  conditional-expression
   An expression that describes the optional conditions that must be
   satisfied before the record can be stored in the database.
 

5  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  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)0relation-constraint-def =
 qwq> CONSTRAINT constraint-name IS qqwqk
  mqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqj x
 lqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqj
 twq> UNIQUE qqqqqqw>qqw> unique-field-name qqqwqqwqqqk
 xmq> PRIMARY KEY qj   mqqqqqqqqq , qqqq<qqqqqqj  x   x
 tqq> FOREIGN KEY qw> referencing-field-name qwqk x   x
 x                 mqqqqqqqqqqqqq , qqqq<qqqqqj x x   x
 x lqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqj x   x
 x mq> REFERENCES 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> USING rse REQUIRE conditional-expr qqqqqqqqj   x
 lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqj
 mwqqqqqqqqqqqqqqqqqqqqqqqqqwq>
  m> CHECK ON qwqq> COMMIT qu
               mqq> UPDATE qj

 

4  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.
 

4  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.
 

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.
 

4  FOREIGN
   This clause names one or more fields that you want to declare as
   a foreign key in the relation you are defining.
 

4  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.
 

4  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.
 

4  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.
 

4  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.
 

4  conditional-expr
   An expression that describes the optional conditions that must be
   satisfied before the record can be stored in the database.
 

4  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  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'.