Forgot to add auto-increment to the primary key, error reported after adding auto-increment

Translated
1

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 主键忘记添加自增，添加自增后报错

| username: TiDBer_h8KizMa7

[TiDB Usage Environment] Production Environment
[TiDB Version]
[Encountered Problem]
There is a table where I forgot to set the primary key to auto-increment. When I tried to set it again, I received the following error: 8200 - Unsupported modify column: can’t set auto_increment
Here is the alter SQL:
ALTER TABLE usercenter.full_user
MODIFY COLUMN id int(11) NOT NULL AUTO_INCREMENT FIRST;

2
| username: WalterWj | Original post link

It feels like this table needs to be rebuilt.

3
| username: TiDBer_CEVsub | Original post link

Try clearing the data and then adding the primary key.

4
| username: OnTheRoad | Original post link

To rebuild the table, adding an AUTO_INCREMENT attribute column to an existing table via ALTER TABLE MODIFY is not supported. However, it is supported to remove the AUTO_INCREMENT attribute column via ALTER TABLE MODIFY, provided that the SESSION variable @@tidb_allow_remove_auto_inc is set to 1.

5
| username: zhouzeru | Original post link

Create a new table, copy the data over, and then rename it :smile:

6
| username: 张雨齐0720 | Original post link

The auto-increment attribute for the primary key needs to be specified when creating the table.

You can now use the rename command to rename the table, then create a new table, and use insert into select to move data into the new table.

7
| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.