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
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 – 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.
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.
4 – FOREIGN
This clause names one or more fields that you want to declare as
a foreign key in the relation you are defining.
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.
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.
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.
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.
9 – conditional-expr
An expression that describes the optional conditions that must be
satisfied before the record can be stored in the database.
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.