ALTER TABLE

Updated on 2024-10-09 GMT+08:00

View PDF

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;

Parent topic: Typical SQL Syntax of Doris

Previous topic: INSERT INTO

Next topic: DROP TABLE

Feedback

Was this page helpful?

Helpful Not helpful

Provide feedback

Thank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.

The system is busy. Please try again later.

Which of the following issues have you encountered?

Content is inconsistent with the product UI

Unclear descriptions

Lack of examples or code

Incorrect steps

Can't find what I need

Lack of best practices

Feedback (optional)

0/500

Select at least one type of issue, and enter your comments or suggestions.

Enter a maximum of 500 characters.

Submit Cancel

For any further questions, feel free to contact us through the chatbot.

Chatbot