The following rules and restrictions apply to adding columns to any table.
- You cannot modify a column more than once in an ALTER TABLE request.
- When a column is added to a base table that already contains data, the system generally needs to access each row of the table to add a field for the new column. The following principles apply when adding a column to a base table that already contains rows.
IF this phrase … IS … THEN all rows initially … DEFAULT specified for the new column contain the specified constant values in the field. WITH DEFAULT contain the system default in the field. DEFAULT
not specified for the new column are null for the column, and you cannot specify the NOT NULL phrase.
- A column that is defined as NOT NULL can only be added to an empty base table if it has no DEFAULT or WITH DEFAULT phrase.
- You cannot change the data type, nullability, or name of an indexed column.
- You cannot change the data type, nullability, or name of a partitioning column for a partitioned table.
- You cannot add an identity column to an existing base table, nor can you add the identity column attribute to an existing column.
- You can add BLOB and CLOB columns to a maximum of 32 per base table. See Rules and Restrictions for Large Object Data Types.
- You cannot add a CHARACTER, VARCHAR, or CLOB column with a server character set of KANJI1 to a table. Otherwise, the system returns an error to the requestor.