Changes the definition of the fields and relation-specific
constraints that make up a relation. You can add, delete, or
change fields and relation-specific constraints in an existing
relation. When you execute this statement, Oracle Rdb modifies the
named field and relation-specific constraint definition(s) within
the relation definition. All the fields that you do not mention
remain the same.
Example:
RDO> CHANGE RELATION EMPLOYEES.
cont> DEFINE SALARY.
cont> END EMPLOYEES RELATION.
RDO> !
RDO> CHANGE RELATION DEPARTMENTS.
cont> CHANGE DEPARTMENT_NAME
cont> QUERY_NAME FOR DATATRIEVE IS "DEPT".
cont> END DEPARTMENTS RELATION.
1 – More
To change a relation with the CHANGE RELATION statement, you need
the Oracle Rdb CHANGE privilege for the relation.
When you execute this statement, Oracle Rdb modifies the named
field and relation-specific constraint definition(s) within the
relation definition. All the fields and constraints that you do
not mention remain the same.
When you change a relation definition, other users see the
revised definition only after they invoke the database the next
time. By default, a database can be opened automatically (that
is, by any user who invokes the database and executes a data
manipulation language statement). If the database was modified
so that it must be manually opened, the RMU/OPEN command must be
used to open it.
You must execute the CHANGE RELATION 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 CHANGE RELATION statement.
2 – Format
(B)0[m[4mCHANGE[m [4mRELATION[m qq> name qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqk
lqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqj
mwqwqqqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqwqwqwqk
x tq> [4mDELETE[m [4mCONSTRAINT[m w> constraint-name qwj x x x
x x mqqqqqqqq , <qqqqqqqj x x x
x mq> relation-constraint-def qqqqqqqqqqqqqqqqqj x x
mqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqj x
lqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqqqqwqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqwqqqq> . qqqk
mq> [4mDESCRIPTION[m IS /* text */ qqqqj x
lqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqj
mqwqqqqqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqqqqwqk
mqqwqqwqq> [4mDEFINE[m qq> define-clause qqqwq> . qwqj x
x tqq> [4mCHANGE[m qq> change-clause qqqu x x
x mqq> [4mDELETE[m qq> field-name qqqqqqj x x
mqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqj x
lqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqj
mqqqq> [4mEND[m qqwqqqqq>qqqqwqq> RELATION qqqqqqq> .
mq> name qqj
2.1 – name
The name of the relation that you want to modify.
2.2 – constraint-name
The name of an existing relation-specific constraint.
2.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)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.3.1 – Arguments
Constraint-name must be unique within the database. The
constraint name can be referred to in other statements such as
DEFINE RELATION, SHOW CONSTRAINT, and START_TRANSACTION.
The phrase 'CONSTRAINT constraint-name is' is optional. If you
specify the keyword CONSTRAINT, you must also provide a name for
the constraint.
2.4 – text
A text string that adds a comment. 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 or CHANGE clause.
2.5 – DEFINE
Using the DEFINE option of CHANGE RELATION, you can add a
globally defined field to a relation. You can also use a local
field name to refer to that global field. You can also define a
relation-specific constraint at the field level.
(B)0[mdefine-clause = qqwqqqqqqqqqqqqqqqwqqqk
mq> /* text */ qj x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mqqwq> global-field-name qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqwqk
tq> local-field-name qq> [4mBASED[m ON q> global-field-name qj x
x lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
x mwqqqqqqqqqqqqqqqqqwqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqk
x mqw> dtr-clause qwj mqqw> field-constraint-def qwqqqu
x mqqqqqq<qqqqqqqj mqqqqqqqqqq<qqqqqqqqqqqqqj x
x x
mq> local-field-name qq> [4mCOMPUTED[m [4mBY[m q> value-expr qk x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj x
mqqqwqqqqqqqqqqqqqqqqqqwqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqvq>
mqw> dtr-clause qwqj
mqqqqqq<qqqqqqqj
2.5.1 – Arguments
The action of CHANGE RELATION with the DEFINE-CLAUSE depends on
the define-clause, as follows:
o DEFINE global-field-name-includes an existing global field
definition in the relation
If you include only a name with the DEFINE option, Oracle Rdb
searches for a field with that name in the list of global
field definitions for the database. If such a field definition
exists, Oracle Rdb adds that field definition to the relation
definition. If no field has that name, Oracle Rdb returns an
error message.
o DEFINE local-field-name BASED ON global-field-name-includes
an existing field definition in the relation, but gives it a
local name
If you include the BASED ON qualifier, Oracle Rdb uses the
definition specified by the global-field-name syntax.
However, the name of the new field in the relation is the
one specified by the local-field-name syntax. This local name
does not become part of the global list of field names for the
database.
You can specify local DATATRIEVE support clauses for the field
specified with the local-field-name syntax. If you do, this
local specification overrides the DATATRIEVE clauses attached
to the global-field-name.
o DEFINE local-field-name COMPUTED BY expression-adds a new
virtual field
The COMPUTED BY qualifier causes Oracle Rdb to calculate the
field's value at run time, based on the specified expression.
You can specify local DATATRIEVE support clauses on any of
these fields. For more information on the DATATRIEVE clauses,
ask for HELP on Field_attr.
o If you include the DEFINE CONSTRAINT clause, Oracle Rdb defines
the specified relation-specific constraint.
2.5.2 – 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.2.1 – Arguments
Constraint-name must be unique within the database. The
constraint name can be referred to in other statements such as
DEFINE RELATION, SHOW CONSTRAINT, and START_TRANSACTION.
The phrase 'CONSTRAINT constraint-name is' is optional. If you
specify the keyword CONSTRAINT, you must also provide a name for
the constraint.
A field constraint definition differs from a relation constraint
definition in that you cannot repeat referenced field names. See
the "relation-constraint-def" help subtopic of CHANGE_RELATION
for more information.
2.6 – CHANGE
The CHANGE RELATION statement with the CHANGE option modifies the
local attributes of an existing field. Only the attributes you
specify in the statement change; all others stay as they are.
For more details, see the Arguments.
You can change relation-specific constraints using the DELETE
CONSTRAINT clause of the CHANGE option. Any constraint to be
changed must be first specifically deleted by name and then re-
declared.
(B)0[mchange-clause =
qwqqqqqqqqq>qqqqqqqqwqqqqk
mqq> /* text */ qqqj x
lqqqqqqqqqq<qqqqqqqqqqqqqj
mwqq> global-field-name qqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqwqk
tqq> local-field-name qqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqu x
mqq> local-field-name qqqqq> [4mBASED[m ON global-field-name qqqqqj x
lqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj
mwqqqqqq>qqqqqqqqqwqwqwqqqqqqqqqqqqqqqqqqqq>qqqqqqqqqqqqqqqqqqqqqqwqwq>
mw> dtr-clause qwj x tq> [4mDELETE[m [4mCONSTRAINT[m qw> constraint-name qwu x
mqqqqq<qqqqqqqqj x x mqqqqqqqqq , qqqqqqqjx x
x mq> field-constraint-def qqqqqqqqqqqqqqqqqqqj x
mqqqqqqqqqqqqqqqqqqqqqqqq<qqqqqqqqqqqqqqqqqqqqqqj
2.6.1 – Arguments
The action of the CHANGE RELATION statement with the CHANGE
option depends on the change-clause as follows:
o CHANGE field-name BASED ON global-field-name-gives the
specified field the attributes of another field
o CHANGE field-name dtr-clause-changes DATATRIEVE support
characteristics
o DELETE CONSTRAINT constraint-name-deletes a relation-specific
constraint from the relation definition
You can specify local DATATRIEVE support clauses on any of these
fields. For more information on the DATATRIEVE clauses, ask for
HELP on Field_attr.
2.7 – DELETE
Deletes the field from the relation. This option deletes the
field only from the relation definition. The global field
definition by this name is still defined for the database as a
whole, and other relations can still refer to it.
If an existing view, index, constraint, or computed field refers
to the field, Oracle Rdb returns an error when you try to delete it.
(B)0[m[4mDELETE[m [4mFIELD[m qqqwqqq> field-name qqqqwqqq> .
mqqqq<qqqq , qqqq<qqqj
3 – Examples
Example 1
The following example adds an existing field definition to a
relation:
CHANGE RELATION EMPLOYEES.
DEFINE SALARY.
END EMPLOYEES RELATION.
This example simply names an existing global field, whose
definition becomes part of the definition for the relation.
Example 2
The BASED ON clause adds a local field name to a relation:
CHANGE RELATION EMPLOYEES.
DEFINE CURRENT_SALARY BASED ON SALARY.
END EMPLOYEES RELATION.
This statement performs the same function as in the previous
example, but uses the BASED ON clause to give a local name to the
field. The statement assumes that SALARY is defined globally in
the database.
Example 3
You can change the local attributes for a field definition
inside the CHANGE RELATION statement without changing the global
attributes of the field for other relations that refer to it. The
DATATRIEVE support clauses defined locally override those defined
globally.
CHANGE RELATION DEPARTMENTS.
CHANGE DEPARTMENT_NAME
QUERY_NAME FOR DATATRIEVE IS "DEPT".
END DEPARTMENTS RELATION.
This statement changes QUERY_NAME for the DEPARTMENT_NAME
field, but only for the DEPARTMENTS relation. The definition
of DEPARTMENT_NAME remains the same for any other relations that
use it.
Example 4
You can change a local field so that it is based on a different
global field without changing the the name of the local field:
DEFINE FIELD SALARY
DATATYPE SIGNED LONGWORD SCALE -2
VALID IF SALARY > 8000
MISSING_VALUE IS 0
EDIT_STRING FOR DATATRIEVE "$$$$$$9.99".
DEFINE FIELD MONEY
DATATYPE TEXT SIZE 8
VALID IF SALARY > 8000
MISSING_VALUE IS 0
EDIT_STRING FOR DATATRIEVE "$$$$$$9.99".
CHANGE RELATION EMPLOYEES.
DEFINE SALARY.
END.
CHANGE RELATION EMPLOYEES.
CHANGE SALARY BASED ON MONEY.
END.
This example assumes two fields, SALARY and MONEY, defined
globally. They have different data types.
o The first CHANGE RELATION statement adds a field to EMPLOYEES
using the global SALARY field definition
o The second CHANGE RELATION statement uses the BASED ON clause
to substitute the MONEY definition for the global SALARY. The
local name remains the same, but that name now points to a
different global definition. There are now two fields named
SALARY in the database, one local and one global.
Example 5
A COMPUTED BY field is calculated from another field in the
relation:
CHANGE RELATION SALARY_HISTORY.
DEFINE SS_DEDUCTION
COMPUTED BY (SALARY_AMOUNT * 0.0625).
END SALARY_HISTORY RELATION.
This statement adds a "virtual" field, whose value is computed
from other fields.
Example 6
The following example deletes a field:
CHANGE RELATION COLLEGES.
DELETE CONTACT_NAME.
END COLLEGES RELATION.
This example changes the COLLEGES relation by removing the
CONTACT_NAME field from it. A global field is still defined for
the database as a whole, and other relations can still refer to
it. It may have some other name, if CONTACT_NAME were defined
with the BASED ON qualifier. This statement also makes the data
associated with that field invisible.
Example 7
This example changes the field-level primary key constraint for
the field DEPT_CODE to a field-level unique constraint.
RDO> CHANGE RELATION JOB_HISTORY
cont> DELETE CONSTRAINT JOB_HISTORY_FOREIGN3.
cont> END.
RDO> CHANGE RELATION DEPARTMENTS
cont> DELETE CONSTRAINT DEPARTMENTS_PRIMARY1
cont> CONSTRAINT DEPARTMENTS_UNIQUE UNIQUE DEPARTMENT_CODE.
cont> END.
The example illustrates how constraints can refer to each other.
Before the primary key constraint DEPARTMENTS_PRIMARY1 can be
deleted, you must delete the foreign key constraint JOB_HISTORY_
FOREIGN3.
Example 8
The following example shows that objects in the database with a
dependency on the EMPLOYEES relation must be deleted before the
EMPLOYEES relation can be deleted:
RDO> START_TRANSACTION READ_WRITE
RDO> CHANGE RELATION JOB_HISTORY
cont> DELETE CONSTRAINT JOB_HISTORY_FOREIGN1.
cont> END.
RDO> CHANGE RELATION SALARY_HISTORY
cont> DELETE CONSTRAINT SALARY_HISTORY_FOREIGN1.
cont> END.
RDO> CHANGE RELATION DEGREES
cont> DELETE CONSTRAINT DEGREES_FOREIGN1.
cont> END.
RDO> CHANGE RELATION RESUMES
cont> DELETE CONSTRAINT RESUMES_FOREIGN1.
cont> END.
RDO> CHANGE RELATION RESUMES
cont> DELETE CONSTRAINT RESUMES_UNIQUE_EMPLOYEE_ID.
cont> END.
RDO> DELETE TRIGGER EMPLOYEE_ID_CASCADE_DELETE, STATUS_CODE_CASCADE_UPDATE.
RDO> DELETE VIEW CURRENT_INFO, CURRENT_SALARY, CURRENT_JOB.
RDO>
RDO> DELETE RELATION EMPLOYEES.
RDO> ROLLBACK