RDB/VMS Relational Database Operator CHANGE_RELATION — VMS RDB_4.0
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, Rdb/VMS 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.
Additional information available:
More
To change a relation with the CHANGE RELATION statement, you need the Rdb/VMS CHANGE privilege for the relation. When you execute this statement, Rdb/VMS 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. You must execute this statement in a read/write transaction. If you issue this statement when there is no active transaction, Rdb/VMS starts a read/write transaction implicitly. Other users are allowed to be attached to the database when you issue the CHANGE RELATION statement.
Format
CHANGE RELATION ──> name ────────────────────────────────┐ ┌───────────────────────────────────────────────────┘ └┬─┬────────────────────>───────────────────┬─┬─┬─┐ │ ├>
D
E
L
E
T
E
C
O
N
S
T
R
A
I
N
T ┬>constraint-name ─┬┘ │ │ │ │ │ └──────── , <──────┘ │ │ │ │ └> relation-constraint-def ────────────────┘ │ │ └──────────────────────<───────────────────────┘ │ ┌───────────────────────<─────────────────────────┘ └────┬─────────────────────────────────┬────> . ───┐ └─>
D
E
S
C
R
I
P
T
I
O
N typebox (I)typebox (S) typebox (/)typebox (*) text */ ────┘ │ ┌──────────────────────────────────────────────────┘ └────┬──┬──>
D
E
F
I
N
E ──> define-clause ───┬─> . ─┬─┐ │ ├──>
C
H
A
N
G
E ──> change-clause ───┤ │ │ │ └──>
D
E
L
E
T
E ──> field-name ──────┘ │ │ └─────────────────────<─────────────────────┘ │ ┌──────────────────────────<───────────────────────┘ └────>
E
N
D ──┬──────────┬──> typebox (R)typebox (E)typebox (L)typebox (A)typebox (T)typebox (I)typebox (O)typebox (N) ───────> . └─> name ──┘
Additional information available:
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. relation-constraint-def = ─┬─>
C
O
N
S
T
R
A
I
N
T constraint-name IS ──┬─┐ └─────────────────────<─────────────┘ │ ┌──────────────────<───────────────────┘ ├┬─>
U
N
I
Q
U
E ──────┬>──┬> unique-field-name ───┬──┬───┐ │└─>
P
R
I
M
A
R
Y
K
E
Y ─┘ └───────── , ────<──────┘ │ │ ├──>
F
O
R
E
I
G
N
K
E
Y ─┬> referencing-field-name ─┬─┐ │ │ │ └───────────── , ────<─────┘ │ │ │ │ ┌────────────────<───────────────────────────┘ │ │ │ └─>
R
E
F
E
R
E
N
C
E
S referenced-relation-name───────┐│ │ │ ┌───────────────────────────────────┘│ │ │ └───┬──────────────────────────┬─────┤ │ │ └┬> referenced-field-name ┬┘ │ │ │ └────────── , ────<──────┘ │ │ └───>
U
S
I
N
G rse REQUIRE conditional-expr ────────┘ │ ┌──────────────────────────────────────<─────────────┘ └┬─────────────────────────┬─> └>
C
H
E
C
K typebox (O)typebox (N) ─┬──>
C
O
M
M
I
T ─┤ └──>
U
P
D
A
T
E ─┘
Additional information available:
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.
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.
define-clause = ──┬───────────────┬───┐
└─> typebox (/)typebox (*) text */ ─┘ │
┌────────────────────────────────────┘
└──┬─> global-field-name ──────────────────────────────────┬─┐
├─> local-field-name ──>
B
A
S
E
D typebox (O)typebox (N) ─> global-field-name ─┘ │
│ ┌────────────────────────────────────────────────────┘
│ └┬─────────────────┬─┬───────────────────────────────┐
│ └─┬> dtr-clause ─┬┘ └──┬> field-constraint-def ─┬───┤
│ └──────<───────┘ └──────────<─────────────┘ │
│ │
└─> local-field-name ──>
C
O
M
P
U
T
E
D
B
Y ─> value-expr ─┐ │
┌─────────────────────────────────────────────────┘ │
└───┬──────────────────┬────────────────────────────────┴─>
└─┬> dtr-clause ─┬─┘
└──────<───────┘
Additional information available:
Arguments
The action of CHANGE RELATION with 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, Rdb/VMS
searches for a field with that name in the list of global field
definitions for the database. If such a field definition exists,
Rdb/VMS adds that field definition to the relation definition.
If no field has that name, Rdb/VMS 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, Rdb/VMS 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
Adds a new virtual field.
The COMPUTED BY qualifier causes Rdb/VMS 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, Rdb/VMS defines the
specified relation-specific constraint.
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. field-constraint-def = ─┬───────────────────────────────────┬─┐ └─>
C
O
N
S
T
R
A
I
N
T constraint-name IS ──┘ │ ┌──────────────────<───────────────────┘ └┬─>
N
O
T
M
I
S
S
I
N
G ───────────────────────────────────────────────────┬─┐ ├─>
U
N
I
Q
U
E ────────────────────────────────────────────────────────┤ │ ├─>
P
R
I
M
A
R
Y
K
E
Y ───────────────────────────────────────────────────┤ │ └┬>
R
E
F
E
R
E
N
C
E
S referenced-relation-name ─┬────────────────────────┬┤ │ │ └> referenced-field-name ┘│ │ │ │ │ └─>
U
S
I
N
G rse REQUIRE conditional-expr ────────────>──────────────┘ │ │ ┌──────────────────────────────────────<──────────────────────────────┘ └┬───────────────────────────┬─> └──>
C
H
E
C
K typebox (O)typebox (N) ─┬──>
C
O
M
M
I
T ─┤ └──>
U
P
D
A
T
E ─┘
Additional information available:
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 help on the relation-constraint-def keyword for more information.
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. change-clause = ──┬─────────>────────┬────┐ └──> typebox (/)typebox (*) text */ ───┘ │ ┌───────────<─────────────┘ └─┬──> global-field-name ─────────────────────>─────────────────┬─┐ ├──> local-field-name ──────────────────────>─────────────────┤ │ └──> local-field-name ───────>
B
A
S
E
D typebox (O)typebox (N) global-field-name ────┘ │ ┌──────────────────────────────────<──────────────────────────────┘ └─┬──────>─────────┬─┬─┬──────────────────────────────────────────┬─┬─> └┬> dtr-clause ─┬┘ │ ├─>
D
E
L
E
T
E
C
O
N
S
T
R
A
I
N
T ─┬> constraint-name─┬┤ │ └─────<────────┘ │ │ └───────── , ──────┘│ │ │ └─> field-constraint-def ──────────────────┘ │ └────────────────────────<─────────────────────┘
Additional information available:
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.
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, Rdb/VMS returns an error when you try to delete it. DELETE FIELD ───┬───> field-name ────┬───> . └────<──── , ────<───┘
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 name:
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.
This 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.