ALTER TABLE

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
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.

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 ─────────────────────────────────┤
      ├─ 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 ─┘                                 │
      ├─ 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 ─────────────────────────┘

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.

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

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.

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.

Note: 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

Modifying Tables that Contain Data

If the table specified in an ALTER TABLE statement contains one or more rows of data (that is, 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.

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.

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;

More Information

For more information about defining tables, see CREATE TABLE and DROP TABLE.
For more information about implementing indexes, see the CA IDMS Database Design Guide.
For more information about displaying a table, see DISPLAY/PUNCH TABLE.