ALTER TABLE

When you modify the table structure, the methods for modifying the aggregation model and non-aggregation model are different. The methods for modifying the Key and Value columns are also different. The command parameters are as follows:

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

Aggregation Model Example

The aggregation type of an aggregation column cannot be changed.

Add the new_col column (key column) after the col1 column.
ALTER TABLE example_db.my_table ADD COLUMN new_col INT DEFAULT "0" AFTER col1;
Add the new_col column (sum aggregation type in the Value column) after col1.
ALTER TABLE example_db.my_table ADD COLUMN new_col INT SUM DEFAULT "0" AFTER col1;
Change the type of the col1 column to BIGINT(Key column).
ALTER TABLE example_db.my_table MODIFY COLUMN col1 BIGINT DEFAULT "1";
Change the type of the col1 column to BIGINT(Value column).
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 of a non-aggregation model

Add the new_col column after the col1 column (add the Key column):
ALTER TABLE example_db.my_table ADD COLUMN new_col INT KEY DEFAULT "0" AFTER col1;
Add the new_col column (Value column) after 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 to BIGINT(Key column).
ALTER TABLE example_db.my_table MODIFY COLUMN col1 BIGINT KEY DEFAULT "1";
Change the type of the col1 column to BIGINT(Value column).
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;