ALTER TABLE

The ALTER TABLE data description statement modifies the definition of a base table in the dictionary.
idmscu19
The ALTER TABLE data description statement modifies the definition of a base table in the dictionary.
Using ALTER TABLE, you can perform the following tasks:
  • Add one or more columns to a table
  • Alter a column’s data type or null attribute
  • Drop or change a column’s default clause
  • Rename a column
  • Drop a column
  • Add a constraint
  • Specify additional restrictions on the data that can be stored in a table
  • Remove all restrictions on the data that can be stored in a table
  • Add or delete the default index associated with a table
  • Revise the estimated row count for a table
  • Update the table's timestamp
The ability to revise the estimated row count and to update the table's timestamp is a CA IDMS extension of the SQL standard.
This article describes the following information:
 
 
2
 
2
 
 
Authorization
To issue an ALTER TABLE statement, you must hold the ALTER privilege on or own the table named in the statement.
Syntax
►►─ ALTER TABLE ─┬─────────────────┬─ table-identifier ───────────────► └─ schema-name ─.─┘ ►────┬─ ADD CHECK ( search-condition ) ─────────────────────┬────────►◄ ├─ DROP CHECK ────────────────────────────────────────┤ ├─ ADD DEFAULT INDEX ──────────────────────────────────┤ ├─ DROP DEFAULT INDEX ─────────────────────────────────┤ ├─ DROP CONSTRAINT ─ constraint-name───┬────────────┬──┤ │ ├─ CASCADE ──┤ │ │ └─ RESTRICT ─┘ │ ├─ ADD ───┬──────────┬─ column-definition ─────────────┤ │ └─ COLUMN ─┘ │ │ ┌──────── , ─────────┐ │ ├─ ADD ───┬──────────┬─ (─▼─ column-definition ┴─)─────┤ │ └─ COLUMN ─┘ │ ├─ ALTER ─┬──────────┬─ column-alteration ─────────────┤ │ └─ COLUMN ─┘ │ ├─ DROP ──┬──────────┬─ column-name ─┬───────────┬─────┤ │ └─ COLUMN ─┘ └─ CASCADE ─┘ │ ├─ RENAME ┬──────────┬ column-name TO new-column-name ─┤ │ └─ COLUMN ─┘ │ ├─ ADD ───┬ referential_constraint ┬───────────────────┤ │ └── unique_constraint ───┘ | ├─ ESTIMATED ROWS estimated-row-count ─────────────────┤ └─ TIMESTAMP timestamp-value ──────────────────────────┘
Expansion of column-definition
►►────column-name data-type ──┬──────────────┬─┬──────────────────┬──►◄ └── NOT NULL ──┘ └── WITH DEFAULT ──┘
Expansion of column-alteration
►►────column-name ───┬─ SET ─┬─ DATA TYPE ─ data-type ────┬───┬──────►◄ │ ├─┬─ ALLOW ─┬── NULL ────────┤ │ │ │ └─ NOT ───┘ │ │ │ └─ WITH DEFAULT ─────────────┘ │ └─ DROP DEFAULT ─────────────────────────┘
Expansion of unique-constraint
Following is an example of the syntax for Expansion of unique-constraint: ┌────── , ──────┐ ►►────┬──────────────────────────────┬──┬─── UNIQUE ────┬──── ( ─▼─
column-name ─┴─
)─────►◄ └─CONSTRAINT
constraint-name
──┘ └─ PRIMARY KEY ─┘
Expansion of referential-constraint
Following is an example of the syntax for Expansion of referential-constraint: ┌──────── , ───────────┐ ►►──┬──────────────────────────────┬── FOREIGN KEY ──( ─▼─
foreign-key-column
─┴─
)──
references-clause
─►◄ └─CONSTRAINT
constraint-name
──┘
Expansion of references-clause
Following is an example of the syntax for Expansion of references-clause: ►►──── REFERENCES ─┬────────────────┬──
referenced-table
──┬─────────────────────────────────┬──────────►◄ └─
schema-name
. ─┘ │ ┌───────── , ─────────┐ │ └── ( ─▼─
referenced-column
─┴─
)─┘
Parameters
  • table-identifier
     
    Specifies the name of the table being modified. 
    Table-identifier
     must identify a base table defined in the dictionary.
  • schema-name
     
    Identifies the schema associated with the named table.
    If not specified, 
    schema-name
     defaults to:
    • The current schema associated with your SQL session, if the statement is entered through the Command Facility or executed dynamically
    • The schema associated with the access module used at runtime, if the statement is embedded in an application program
  • column-definition
    Defines one or more new columns to be included in the table. New columns are added after the last existing column.
    Expanded syntax for 
    column-definition
     is shown immediately following the ALTER TABLE syntax. Descriptions of 
    column-definition
     parameters follow description of ALTER TABLE parameters.
  • ADD CHECK (search-condition)
    Specifies additional restrictions on the data that can be stored in the table.
    If the table definition already includes data restrictions in a search condition, CA IDMS appends the search condition specified in the ADD CHECK parameter to the existing search condition with the binary operator AND. CA IDMS stores a new row in the table only if the value of the entire expression formed by the concatenation of the search conditions is true.
    Restrictions on the use of 
    search-condition
     with ADD CHECK are discussed under "Usage" following these parameter descriptions. For expanded 
    search-condition
     syntax, see Expansion of Search-condition.
  • DROP CHECK
    Removes any existing restrictions on the data that can be stored in the table.
  • ADD DEFAULT INDEX 
    Creates a default index for the named table.
    Note:
     The table must not have a default index already ssociated with it.
  •  
    DROP DEFAULT INDEX
    Deletes the default index associated with the table.
  • column-alteration
    Specifies the changes to be made to the attributes of a column.
    Note:
     The expanded syntax for column-alteration is shown after the ALTER TABLE syntax. Descriptions of column-alteration parameters follow the description of ALTER TABLE parameters.
  • DROP COLUMN 
    column-name
     
    Identifies the column to be removed from the table. Column-name must be the name of a column in the table.
    Note:
     You cannot drop columns that are part of a CALC key of a populated table or that are named in a check constraint.
  • CASCADE
    Drops the following entities:
    • The CALC key if it includes the column.
    • All referential constraints in which the named column is a referenced or a foreign key column.
    • All linked constraints in which the named column is a sort column.
    • All indexes in which the named column is an indexed column.
    • All views in which the column is named.
    Note:
     If CASCADE is not specified, the column must not participate in a referential constraint or index, or be named in a view.
  • RENAME COLUMN 
    column-name
     
    Identifies the column name to be changed. Column-name must be the name of a column in the table.
    Note:
     You cannot rename a column if the column is named in a check constraint or in a view.
  • TO 
    new-column-name
     
    Specifies the new name for the identified column.
    Limit: 1 - 32 characters that follows the SQL identifier standard.
    Note:
     The new column name must be distinct from the name of any existing column in the table.
  • ESTIMATED ROWS 
    estimated-row-count
     
    Indicates the number of rows expected to be stored for the table. 
    Estimated-row-count
     must be an integer that does not exceed 16,777,214. The specified value replaces any previous estimated row count for the table.
  • TIMESTAMP 
    timestamp-value
     
    Specifies the value of the synchronization stamp to be assigned to the table. 
    Timestamp-value
     must be a valid external representation of a timestamp.
  • unique-constraint
    Identifies the UNIQUE or PRIMARY KEY Constraint to be added to the table.
  • referential-constraint
    Identifies the Referential Constraint to be added to the table.
  • DROP CONSTRAINT 
    constraint-name
     
    Identifies the constraint to be removed from the table. Constraint-name must be the name of a constraint in the table.
Parameters for Expansion of column-definition
  • column-name
     
    Specifies the name of a column to be included in the table being created. 
    Column-name
     must be a one- through 32-character name that follows the conventions for SQL identifiers.
    Column-name
     must be unique within the table being defined.
  • data-type
    Defines the data type for the named column. For expanded 
    data-type
     syntax, see Expansion of Data-type.
  • NOT NULL
    Indicates that the column cannot contain null values.
    If NOT NULL is specified 
    without
     WITH DEFAULT, the table being altered must be empty.
    If NOT NULL is not specified, the column is defined to allow null values.
  • WITH DEFAULT
    Directs CA IDMS to establish a default value for the column being added.
    The default value is based on the data type of the column:
Column data type
 
Default value
 
CHARACTER
Blanks
VARCHAR
A character string literal with a length of zero (that is, '')
GRAPHIC
Double-byte blanks
VARGRAPHIC
A double-byte character string literal with a length of zero
DATE
'0001-01-01' for existing rows
The value in the CURRENT DATE special
register for newly inserted rows
TIME
'00.00.00' for existing rows
The value in the CURRENT TIME special register
for newly inserted rows
TIMESTAMP
'0001-01-01-00.00.00.000000' for existing rows
The value in the CURRENT TIMESTAMP special register for newly inserted rows
All numeric data types
0 (zero)
If you do not specify WITH DEFAULT, then:
  • If you specify NOT NULL, the table must be empty
  • If you do 
    not
     specify NOT NULL, the default value for the column is NULL
Parameters for Expansion of column-alteration
Following is a list of parameters for column-alteration:
  • column-name
    Identifies the column whose attributes are to be changed. Column-name must be the name of a column in the table.
  • data-type
    Defines the new data type for the named column. The specified data type must be compatible for assignment with the column’s existing data type. For expanded data-type syntax, see Expansion of Data-type.
    You cannot change the data type of a column that is part of a CALC key of a populated table or that is a referenced or foreign key column in a constraint.
  • ALLOW NULL
    Indicates that the column can contain null values. You cannot change the null attribute of a column that is part of a CALC key of a populated table or a referenced key.
  • NOT NULL
    Indicates that the column cannot contain null values. You cannot change the null attribute of a column that is part of a CALC key of a populated table or a referenced key.
  • WITH DEFAULT
    Sets the column’s value to a default if no value for the column is specified when a row is inserted.
  • DROP DEFAULT
    Does not set the column’s value to a default when a row is inserted.
Parameters for Expansion of unique-constraint
Following is a list of parameters for Expansion of unique-constraints:
  • CONSTRAINT
    (Optional). Defines the constraint-name. If the constraint-name is not coded, a system-created value is generated. A constraint name has to be unique in the schema, whether it is unique, primary key, or relational constraint.
  • UNIQUE
    All unique values in the columns that make up the unique key. A UNIQUE key must be defined as NOT NULL. A table can have multiple UNIQUE keys.
  • PRIMARY KEY
    All unique values in the columns that make up the primary key. A PRIMARY KEY must be defined as NOT NULL. A table can only have one PRIMARY KEY.
  • (column-name)
    Must be the name of a column in the current TABLE.
Parameters for Expansion of referential-constraint
Following is a list of parameters for Expansion of referential-constraint:
  • CONSTRAINT
    (Optional). Defines the constraint-name. If the constraint-name is not coded, a system-created value is generated. A constraint name has to be unique in the schema, whether it is unique, primary key, or relational constraint.
  • (
    foreign-key-column
    )
    Specifies one or more columns that make up the foreign key in the referencing table. It must identify a column in the referencing table and must be unique within the list of column names.
    Limits:
     1 to 32 columns
Parameters for Expansion of references-clause
Following is a list of parameters for Expansion of references-clause:
  • schema-name
     
     
     
     
     
    The schema associated with the referenced table.
    If you do not specify schema-name, it defaults to the following:
    • The current schema associated with your SQL session, if the statement is entered through the Command Facility or executed dynamically
    • The schema associated with the access module used at runtime, if the statement is embedded in an application program
  •  
    referenced-table
     
     
     
    The referenced table in the constraint. It must identify a base table defined in the catalog.
  • (
    referenced-column
    )
    Specifies one or more non-null columns that make up a unique key in the referenced table, as defined by a CREATE CALC or CREATE INDEX statement.
    It must identify a column in the referenced table and must be unique within the list of column names. The columns must be named in the same order in which they are named in the CREATE CALC or CREATE INDEX statement that defines the unique key.
    You must specify the same number of referenced columns as the number of columns included in the foreign key of the referencing table. The corresponding referenced and foreign-key columns must have the same data type, length, precision, and scale.
    When (referenced-column) is omitted the referenced table must be defined with a primary key, and the primary key column list of the referenced table will be used as the referenced columns. 
Usage
Tables in System Schemas
 
You cannot modify the definition of a table in the SYSTEM schema.
Maximum Row Length
 
When adding a column to a table, you must ensure that the total number of bytes required for all columns in the table does not exceed the maximum allowed. For more information about maximum row length, see CREATE TABLE.
Restrictions on search-condition
 
In the ADD CHECK parameter of an ALTER TABLE statement:
  • Search-condition
     cannot include any host variables, local variables, routine parameters, aggregate or user-defined functions, EXISTS predicates, quantified predicates, or subqueries
  • Each column reference in 
    search condition
     must identify a column in the table being modified
Modify a Generated Index
 
You cannot change a generated index to make it a non-unique index.
Modify Tables that Contain Data
 
If the table specified in an ALTER TABLE statement contains one or more rows of data (the table is not empty), and the ALTER TABLE statement specifies:
  • ADD 
    column-definition
    , you must supply a default value in the DEFAULT parameter of the column definition if you specify NOT NULL
  • ADD CHECK, the value of the search condition specified in the ADD CHECK parameter must be true for each existing row in the table
 
Add a Default to a Column
 
Allowing a column to have a default value affects only the table’s definition; existing table rows are not affected.
Remove a Column’s Default
 
If the table is populated and the column does not allow null values, every existing row must contain a value in the changed column. To ensure this, each row is accessed and updated if it does not contain a value for the column.
Rename a Column
 
A column that is named in a check constraint or a view cannot be renamed.
The definition of all referential constraints, sort keys, CALC keys and indexes in which the column participates are updated to show the new column name.
Drop a Column
 
Every row in the table is updated to remove the column value.
If a column is named in a check constraint or is part of the CALC key of a populated table, you cannot drop the column.
If you do not specify CASCADE, the column must not be one of the following types of columns:
  • A column in a CALC key
  • A referenced or foreign key column in a referential constraint
  • An indexed column
  • A sort column of a linked constraint
  • Named in a view
If you specify CASCADE, how the column is used determines what other items are dropped:
  • Dropping a CALC key column also drops the CALC key
  • Dropping a referenced or foreign key column in a referential constraint also drops the constraint
  • Dropping an indexed column also drops the index
  • Dropping a sort column of a linked constraint also drops the constraint
  • Dropping a column named in a view also drops the view
Change a Column’s Null Attribute
 
The following situations apply when you change a column's null attribute:
  • When the column is part of a CALC key of a populated table, or is a referenced column in a constraint, the ALTER statement fails.
  • When you change a null attribute, every row in the table is updated to add or remove the null attribute byte for that column.
  • When the changed column is a sort column, every index and linked indexed constraint is automatically rebuilt.
  • When disallowing nulls and the value of the column is null for a row in the table, the ALTER statement fails.
Change a Column’s Data Type
 
The following situations apply when you change a column's data type:
  • When the column is part of a CALC key of a populated table, or is a referenced column in a constraint, the ALTER statement fails.
  • When changing a column’s data type, the new data type you enter must be compatible for assignment with the original data type.
  • Every row in the table is restructured to convert the column value to the new type. This might involve increasing or decreasing the length of the row.
  • The ALTER statement will fail if a loss of data (such as truncation of a non-blank character or numeric overflow) would occur as part of the conversion.
  • When you change data type, every index and linked indexed constraint in which the column is a sort column is rebuilt.
Defining a Referential Constraint
 
When defining a Referential Constraint through ALTER TABLE the same rules must be followed as defining one through CREATE CONSTRAINT
On creation of a referential constraint, an index will automatically be generated on the foreign key columns.
Referenced keys specified must be NOT NULL.
Enforcing Indexes
 
When a unique or primary key constraint is defined on an Alter Table, a unique index is created to enforce the constraint, as follows:
  • It is marked as an enforcing generated index.
  • It is created with default options.
  • If a constraint name is not specified one is generated.
If more than one unique constraint is defined on the same columns, only one enforcing index is created. A primary key constraint is used over a unique constraint, whether named or unnamed. A named unique constraint is used over an unnamed constraint. Otherwise the first constraint name encountered is used.
If the enforcing index is for a primary key, the replacement index is marked as only a primary key index when the original primary key is dropped.
An enforcing index can be altered, but cannot be changed to non-Unique.
An enforcing index can only be dropped after a replacement enforcing index has been defined. A replacement enforcing index may be defined by creating a unique index on the same columns as an existing enforcing index. When the columns match, the new index is also marked as an enforcing index, but is not a generated index. Either index may then be dropped, but not both. When a replacement index is created for a generated index it is automatically be dropped.
An enforcing index can only be dropped after the constraint is dropped, using Alter Table Drop Constraint.  If the index is generated it is dropped as part of the drop constraint command.   If the index is not generated it is marked as not enforcing, and can be dropped using Drop Index.
Examining Check Constraints on a Table
 
You can examine the current check constraint on a table by using the DISPLAY TABLE statement.
Examining existing check constraints is useful if you are planning to change a constraint by dropping it and adding the changed constraint.
Adding Columns with Multiple ALTER TABLE Statements
 
When columns are added with the ALTER TABLE statement, the first column in the column definition list is aligned on a full word boundary in the physical data structure that represents table rows. Since each individual ALTER TABLE statement will cause alignment, columns added in separated ALTERs versus one ALTER can result in different row lengths and column offsets within the physical row data structure.
Specifying a Synchronization Stamp
 
When defining or altering a table, you can specify a value for its synchronization stamp. You should use care when doing so because the purpose of the stamp is to enable the detection of discrepancies between an entity and its definition. If explicitly specified, you must set the synchronization stamp to a new value following a change so that the change is detectable by the runtime system.
If not specified, the synchronization stamp is automatically set to the current date and time.
Dropping a Constraint
 
When dropping a constraint, the associated index is also modified, as follows:
  • If the index is automatically generated, it is dropped.
  • If the index is user-created, the enforcing flag is removed.
Effect on Access Modules
          
When the definition of a table is modified, the stamp associated with the table is updated. The stamping level of the area determines which access modules need to be recompiled.
  • For areas with STAMP BY TABLE, only access modules which directly reference the table must be recompiled.
  • For areas with STAMP BY AREA, a change to any table within the area will update the stamp. This requires an access module that references any table in that area must be recompiled.  
Examples
Adding a Column to a Table
The following ALTER TABLE statement adds a new column, STATUS, to the CONSULTANT table. The value of STATUS in all existing rows is blank because the statement specifies WITH DEFAULT.
alter table consultant add status character(1) not null with default;
Further Restricting Data in a Table
The following ALTER TABLE statement defines an additional restriction on the data that can be stored in the CONSULTANT table. CA IDMS adds the constraint only if each existing row of the CONSULTANT table already has 'A' or 'I' in the STATUS column.
alter table consultant add check (status in ('A', 'I');
Change a column’s data type:
alter table demo.empl alter column city set data type varchar(20);
Drop a column using the CASCADE option:
alter table demo.empl drop column status cascade;
Rename a column:
alter table demo.empl rename column proj_id to project_id;
Adding a default index:
alter table emp.dept add default index;
Adding a Unique Constraint:
alter table demo.empl add CONSTRAINT uniqueID UNIQUE (proj_id);