ALTER TABLE

Use the ALTER TABLE command to change the characteristics of an existing table and its columns. You can add, drop, and alter columns, and manually set the value of fields of type INCR.

Restriction: You cannot alter system tables.

Syntax

ALTER TABLE [database_name.]table_name
 SET INCR value
 ADD [COLUMN] column_name data_type [ NODEFAULT | NOMODIFY | HIDDEN ]
 DROP [COLUMN] column_name
 ALTER [COLUMN] column_name SET NOMODIFY { TRUE | FALSE }
 ALTER [COLUMN] column_name SET HIDDEN { TRUE | FALSE }
 ALTER [COLUMN] column_name SET NODEFAULT { TRUE | FALSE }
 ALTER [COLUMN] column_name SET WIDTH value;

You can specify more than one ADD, DROP, or ALTER setting in a single ALTER TABLE command.

Example

alter table mytab add col3 real;

Manually setting increment values

A column of type INCR is used to give each row in a table a unique number, such as the Serial column in the alerts.status table. On each insert, the current increment value is copied to the new row and is then increased for the next row. You can use the SET INCR (set increment) clause to set the increment value for subsequent rows. This is useful, for example, when you want to change the Serial value that is assigned to new alerts in the alerts.status table.

The syntax is:

ALTER TABLE table_name SET INCR value

Where table_name is the name of a table that contains a column of type INCR and value is an integer.

For example, to run the procedure with the SQL interactive interface, use the following commands:

1> alter table alerts.status set incr 20100
2> go

Note: To avoid duplicate values, you cannot set an increment value that is the same as the value of an existing row. In addition, you cannot set an INCR value that causes future inserts to have the same values as rows that already exist. The new INCR value cannot be between the minimum and maximum existing rows and must be 1,000,000,000 below the existing minimum value in the table or 1,000,000,000 above the existing maximum value.

Note:

Functionality delivered in fix pack
23 In Fix Pack 23, the behavior was changed to prevent the new INCR value being set to a value that is within 1,000,000 of any existing value in the table. INCR value wrapping is also now taken into consideration.