Maintaining Tables with Related Constraints

The following sections identify the impact of making changes to a table structure when the table has constraints.
datacom150
The following sections identify the impact of making changes to a table structure when the table has constraints.
Contents
Impact of ALTER TABLE on TEST and HISTory Status Versions
When an SQL ALTER TABLE statement adds a constraint to a table,
Datacom Datadictionary
sets the CONSTRAINT attribute to A on all TEST and HISTory status versions of that table. If you later attempt to copy one of these versions (or a structure containing one of these versions) to PRODuction status,
Datacom Datadictionary
prevents the copy, issues an error message, and identifies the table which has been altered. To recover, do one of the following:
  • If you were copying a structure to PRODuction status and the table identified in the
    Datacom Datadictionary
    message as having been altered was not one of the tables you modified in the TEST status version of the structure, copy only the modified tables from TEST to PRODuction status.
  • If the table altered through SQL is the table you modified in TEST status in
    Datacom Datadictionary
    , use one of the following procedures:
Procedure 1
  1. Restore the TABLE entity-occurrence from PRODuction status to TEST.
  2. Reapply any changes you had made through
    Datacom Datadictionary
    .
  3. Copy the entity-occurrence to PRODuction status.
Procedure 2
  1. Drop the constraints.
  2. Copy the entity-occurrence to PRODuction status.
  3. Re-add the constraints.
Preventing ALTER TABLE
To prevent anyone else from altering a table which you are modifying in
Datacom Datadictionary
, do one of the following:
  • If SQL security is implemented for the database containing the tables, issue the following SQL command:
    REVOKE ALTER ON table-name FROM PUBLIC
    This prevents anyone from issuing an ALTER TABLE against the named table (or tables). However, after you have made your changes in
    Datacom Datadictionary
    and copied the table back to PRODuction status, you must GRANT ALTER to each accessor-ID individually.
  • Instead of using SQL security, you can do the following:
    1. Use online or batch
      Datacom Datadictionary
      to assign a password to the PRODuction status version of any TABLE entity-occurrence which you intend to modify. (The password prevents anyone from performing an SQL ALTER TABLE on a table.)
    2. Restore the structure containing the tables you want to modify from PRODuction status to a TEST status version.
    3. Use
      Datacom Datadictionary
      to make the modifications you need to make to the TEST status version of these TABLE entity-occurrences.
    4. Copy these TABLE entity-occurrences to PRODuction status.
    5. Use online or batch
      Datacom Datadictionary
      to remove the passwords you assigned in Step 1.
Impact of Changing Structure Definitions on Constraints
Certain
Datacom Datadictionary
activity, such as adding columns (FIELD entity-occurrences) to a table, or updating attributes of fields, keys, and tables, affects a constraint. These changes affect different types of constraints in different ways. For example, they could invalidate the constraint or the data in either the modified or a related table.
The following sections are organized by the action you are performing and describe the type of effect the action has on Check, Foreign, and Unique constraints. For Unique constraints, the Foreign keys that reference them can also be affected and when they are, it is so noted in the following discussions.
Adding a Column to a Table
If you add a column to a table that has constraints defined, the constraints become invalid. This applies
only
if the column is not in a Unique or Foreign Key (see Adding a Column to a Key).
Type of Constraint
Effect on Constraint
Check and Foreign
Constraints become invalid because offsets stored in the object will change. The constraints are rebound by SQL the next time the table is accessed.
Unique
Constraints become invalid because the length of the table changes as well as the offsets. The constraints are rebound by SQL the next time the table is accessed.
The referencing constraints also become invalid and are rebound by SQL the next time the table is accessed.
Deleting a Column from a Table
If you delete a column from a table that has constraints defined, the result varies depending on the type of constraint. This applies
only
if the column is not in a Unique or Foreign Key (see Deleting a Column from a Key). For example, Check constraints become obsolete, and Foreign constraints become invalid.
Type of Constraint
Effect on Constraint
Check
Constraints become obsolete if the constraint is defined to the column being deleted, so the delete is not allowed.
Datacom Datadictionary
generates a DSF return code CON when this attempt is made. Drop the constraint before deleting the column from the table.
If the constraint is not defined to the column, the constraints become invalid because the offsets of other columns could change and therefore change the length of the table. The constraints are rebound by SQL the next time the table is accessed.
Foreign
Constraints become invalid because offsets stored in the object change. The constraints are rebound by SQL the next time the table is accessed.
Unique
Constraints become invalid because the offsets stored in the object change and because the length of the table also changes. The constraints are rebound by SQL the next time the table is accessed.
The referencing constraints also become invalid and are rebound by SQL the next time the table is accessed.
Adding a Column to a Key
If you are adding a column to a key, the results vary depending on the type of constraint. This action has no effect on Check constraints. However, Foreign and Unique constraints cannot be updated this way.
Type of Constraint
Effect on Constraint
Check
Not applicable.
Foreign and Unique
Constraints are not allowed because the rebind will re-create the key as it was before causing a new KEY to be added since the source of the constraints reflects the old column list.
Datacom Datadictionary
generates a DSF return code CON when you attempt to add a column to a key defined with these constraints. Drop the constraint before adding the column to the key.
Deleting a Column from a Key
If you delete a column from a key, the result varies depending on the type of constraint. This action has no effect on Check constraints. However, Foreign and Unique constraints cannot be updated in this way.
Type of Constraint
Effect on Constraint
Check
Not applicable.
Foreign and Unique
This action is not allowed because the rebind will re-create the key as before resulting in a new KEY since the source of the constraints reflects the old column list.
Datacom Datadictionary
generates a DSF return code CON when you attempt to delete a column from a key defined with these constraints. Drop the constraint before deleting the column from the key.
Deleting Keys
If you delete a key with constraints defined, there is no effect on Check constraints, however, Foreign and Unique constraints become obsolete.
Type of Constraint
Effect on Constraint
Check
Not applicable.
Foreign and Unique
Datacom Datadictionary
generates a DSF return code CON when you attempt to delete a key related to one of these type of constraints. Drop the constraint before deleting the key.
Updating Table Attributes
When you update TABLE entity-occurrence attributes, changing AUTHID, SQLNAME or DATACOM-NAME attribute-values will affect constraints. Updating other attributes does not affect constraints.
DATACOM-NAME Attribute
Type of Constraint
Effect on Constraint
Check, Foreign,
and Unique
Not allowed if the table has constraints.
Datacom Datadictionary
generates a DSF return code CON when you attempt to update this attribute for a table with these constraints. Drop the constraint before updating this attribute.
AUTHID or SQLNAME Attribute
Type of Constraint
Effect on Constraint
Check, Foreign,
and Unique
An SB9 error is returned if the change is attempted in PRODuction status.
If the change is attempted in TEST status, a CON error occurs if any constraints exist on this table.
Updating Key Attributes
When you update KEY entity-occurrence attributes, changing the DATACOM-ID, DATACOM-NAME, MASTER-KEY, NATIVE-KEY, and UNIQUE attribute-values will affect constraints. All other KEY attributes not listed in the following table may be changed.
DATACOM-ID Attribute
Type of Constraint
Effect on Constraint
Check
Not applicable.
Foreign
When you attempt to change this attribute-value,
Datacom Datadictionary
clears the value.
Unique
Not applicable.
DATACOM-NAME Attribute
Type of Constraint
Effect on Constraint
Check
Not applicable.
Foreign
When you attempt to change this attribute-value,
Datacom Datadictionary
clears the value.
Unique
Constraints become invalid because
Datacom/DB
Datacom/DB
names of keys are stored in the object. The constraints are rebound by SQL the next time the table is accessed.
The referencing constraints also become invalid and are rebound by SQL the next time the table is accessed.
MASTER-KEY Attribute
Type of Constraint
Effect on Constraint
Check
Not applicable.
Foreign
When you attempt to change this attribute-value,
Datacom Datadictionary
sets the value to N.
Unique
Not applicable.
NATIVE-KEY Attribute
Type of Constraint
Effect on Constraint
Check
Not applicable.
Foreign
When you attempt to change this attribute-value,
Datacom Datadictionary
sets the value to N.
Unique
Not applicable.
UNIQUE Attribute
Type of Constraint
Effect on Constraint
Check
Not applicable.
Foreign
It is meaningless to update a Foreign Key.
When you attempt to change this attribute-value,
Datacom Datadictionary
sets the value to N.
Unique
Not allowed if a constraint exists on this key.
Datacom Datadictionary
generates a DSF return code CON when you attempt to change the value of this attribute from Y to N. Drop the constraint before you make this change.
Updating Field Attributes
When you update FIELD entity-occurrence attributes, changing the following attribute-values will affect constraints:
  • AFTER
  • CLASS
  • DECIMALS
  • LENGTH
  • NULL-INDICATOR
  • OFFSET
  • REDEFINES
  • REPEAT
  • SIGN
  • SQLNAME
  • TYPE
  • TYPE-NUMERIC
Updating other attributes does not affect constraints.
LENGTH Attribute
Type of Constraint
Effect on Constraint
Check and Foreign
Constraints become invalid because the offsets stored in the object will change. The constraints are rebound by SQL the next time the table is accessed.
Unique
Constraints become invalid because the offsets stored in the object change and because the length of the table also changes. The constraints are rebound by SQL the next time the table is accessed.
The referencing constraints also become invalid because the offsets stored in the object change. The constraints are rebound by SQL the next time the table is accessed.
OFFSET Attribute
Type of Constraint
Effect on Constraint
Check and Foreign
Constraints become invalid because the offsets stored in the object will change. The constraints are rebound by SQL the next time the table is accessed.
Unique
Constraints become invalid because the offsets stored in the object change and because the length of the table also changes. The constraints are rebound by SQL the next time the table is accessed.
The referencing constraints also become invalid because the offsets stored in the object change. The constraints are rebound by SQL the next time the table is accessed.
AFTER Attribute
Type of Constraint
Effect on Constraint
Check and Foreign
Constraints become invalid because the offsets stored in the object will change. The constraints are rebound by SQL the next time the table is accessed.
Unique
Constraints become invalid because the offsets stored in the object change. The constraints are rebound by SQL the next time the table is accessed.
The referencing constraints also become invalid because the offsets stored in the object change. The constraints are rebound by SQL the next time the table is accessed.
REPEAT Attribute
Type of Constraint
Effect on Constraint
Check and Foreign
Constraints become invalid because the offsets stored in the object will change. The constraints are rebound by SQL the next time the table is accessed.
Unique
Constraints become invalid because the offsets stored in the object change and because the length of the table also changes. The constraints are rebound by SQL the next time the table is accessed.
The referencing constraints also become invalid because the offsets stored in the object change. The constraints are rebound by SQL the next time the table is accessed.
NULL-INDICATOR Attribute
Type of Constraint
Effect on Constraint
Check and Foreign
Constraints become invalid because the offsets stored in the object change. The constraints are rebound by SQL the next time the table is accessed.
Unique
Constraints become invalid because the offsets stored in the object change and because the length of the table also changes. The constraints are rebound by SQL the next time the table is accessed.
The referencing constraints also become invalid because the offsets stored in the object change. The constraints are rebound by SQL the next time the table is accessed.
TYPE
Type of Constraint
Effect on Constraint
Check and Foreign
Constraints become invalid because the offsets stored in the object change. The constraints are rebound by SQL the next time the table is accessed.
Unique
Constraints become invalid because the offsets stored in the object change and because the length of the table also changes. The constraints are rebound by SQL the next time the table is accessed.
The referencing constraints also become invalid. The constraints are rebound by SQL the next time the table is accessed.
DECIMALS Attribute
Type of Constraint
Effect on Constraint
Check, Foreign
and Unique
Constraints become invalid because the data stored in the object may need to be updated as a result of this change. The constraints are rebound by SQL the next time the table is accessed.
The referencing constraints also become invalid and are rebound by SQL the next time the table is accessed.
REDEFINES Attribute
Updating from N to S or P for constraint field.
Type of Constraint
Effect on Constraint
Check
Datacom Datadictionary
generates a DSF return code CON when you attempt to change the value of this attribute from N to S or P. It is the same as deleting a field from the table. Drop the constraint before making the change.
Foreign and Unique
This action is not allowed because the rebind will re-create the key as before adding a new key since the source of the constraints reflects the old column list.
Datacom Datadictionary
generates a DSF return code CON when you attempt to change the value of this attribute from N to S or P. Drop the constraint before making the change.
REDEFINES Attribute
Updating from N to S or P for other fields.
Type of Constraint
Effect on Constraint
Check and Foreign
Constraints become invalid because offsets stored in the object change. The constraints are rebound by SQL the next time the table is accessed.
Unique
Constraints become invalid because offsets stored in the object change and because the length of the table also changes. The constraints are rebound by SQL the next time the table is accessed.
The referencing constraints also become invalid and are rebound by SQL the next time the table is accessed.
REDEFINES Attribute
Updating from S or P to N.
Type of Constraint
Effect on Constraint
Check
Constraints become invalid because offsets stored in the object change. The constraints are rebound by SQL the next time the table is accessed.
Foreign
Datacom Datadictionary
generates a DSF return code CON when you attempt to change this value from S or P to N, if the field is in a key representing a Foreign constraint.
If the field being updated is
not
in the Foreign key, all Foreign constraints become invalid.
Unique
Constraints become invalid because offsets stored in the object change and because the length of the table also changes. The constraints are rebound by SQL the next time the table is accessed.
Since this action is the same as adding a field, it is not allowed if the field is in a key that represents a Unique constraint.
Datacom Datadictionary
generates a DSF return code CON if you attempt to change a field in this manner.
The referencing constraints also become invalid and are rebound by SQL the next time the table is accessed.
CLASS Attribute
Updating from S to another value for constraint field.
Type of Constraint
Effect on Constraint
Check
Datacom Datadictionary
generates a DSF return code CON when you attempt to change the value of this attribute to another value from S, since this is the equivalent of deleting a column for SQL. Drop the constraint before making the change.
Foreign and Unique
This action is not allowed because the rebind will re-create the key as it was before adding a new key since the source of the constraints reflects the old column list.
Datacom Datadictionary
generates a DSF return code CON when you attempt to change the value of this attribute from S to another value. Drop the constraint before making the change.
CLASS Attribute
Updating from S to another value for other fields.
Type of Constraint
Effect on Constraint
Check and Foreign
Constraints become invalid because offsets stored in the object change. The constraints are rebound by SQL the next time the table is accessed.
Unique
Constraints become invalid because offsets stored in the object change and because the length of the table also changes. The constraints are rebound by SQL the next time the table is accessed.
The referencing constraints also become invalid and are rebound by SQL the next time the table is accessed.
CLASS Attribute
Updating to S.
Type of Constraint
Effect on Constraint
Check
Constraints become invalid because offsets stored in the object change. The constraints are rebound by SQL the next time the table is accessed.
Foreign
Datacom Datadictionary
generates a DSF return code CON when you attempt to change this value to an S if the field is in a key representing a Foreign constraint. If the field being updated is
not
in the Foreign key, all Foreign constraints become invalid.
Unique
Constraints become invalid because offsets stored in the object change and because the length of the table also changes. The constraints are rebound by SQL the next time the table is accessed.
Since this action is the same as adding a field, it is not allowed if the field is in a key that represents a Unique constraint.
Datacom Datadictionary
generates a DSF return code CON if you attempt to change a field in this manner.
The referencing constraints also become invalid and are rebound by SQL the next time the table is accessed.
SIGN or TYPE-NUMERIC Attribute
For constraint field.
Type of Constraint
Effect on Constraint
Check, Foreign,
and Unique
Constraints become invalid because the data stored in the object may need to be updated as a result of this change.
The referencing constraints also become invalid and are rebound by SQL the next time the table is accessed.
SQLNAME Attribute
Type of Constraint
Effect on Constraint
Check, Foreign,
and Unique
Not allowed in PRODuction status.
Datacom Datadictionary
generates an error (SB9).
Not allowed in TEST status if the field is related to a constraint of any type.
Datacom Datadictionary
generates a DSF return code CON.