Modifying a Table Structure

There are different methods for modifying table structures in an aggregate model and a non-aggregate model. The methods for modifying the Key and Value columns are also different.

If AGGREGATE KEY is specified during table creation, the table is in aggregation model. In other scenarios, a non-aggregation model is used.
In the table creation statement, the columns following the keyword 'unique key', 'aggregate key', or 'duplicate key' are the Key column, and the remaining columns are Value columns.

Example for an Aggregate Model

The aggregate type of the aggregate columns cannot be changed.

Add the new_col column (Key column) following the col1 column.
ALTER TABLE example_db.my_table ADD COLUMN new_col INT DEFAULT "0" AFTER col1;
Add the new_col column (sum aggregate on the Value column) following col1.
ALTER TABLE example_db.my_table ADD COLUMN new_col INT SUM DEFAULT "0" AFTER col1;
Change the type of the col1 column (Key column) to BIGINT.
ALTER TABLE example_db.my_table MODIFY COLUMN col1 BIGINT DEFAULT "1";
Change the type of the col1 column (Value column) to BIGINT.
ALTER TABLE example_db.my_table MODIFY COLUMN col1 BIGINT MAX DEFAULT "1";
Delete the col1 column.
ALTER TABLE example_db.my_table DROP COLUMN col1;

Example for a Non-Aggregate Model

Add the new_col column (Key column) following the col1 column:
ALTER TABLE example_db.my_table ADD COLUMN new_col INT KEY DEFAULT "0" AFTER col1;
Add the new_col column (Value column) following the col1 column.
ALTER TABLE example_db.my_table ADD COLUMN new_col INT DEFAULT "0" AFTER col1;
Change the type of the col1 column (Key column) to BIGINT.
ALTER TABLE example_db.my_table MODIFY COLUMN col1 BIGINT KEY DEFAULT "1";
Change the type of the col1 column (Value column) to BIGINT.
ALTER TABLE example_db.my_table MODIFY COLUMN col1 BIGINT DEFAULT "1";
Delete the col1 column.
ALTER TABLE example_db.my_table DROP COLUMN col1;