Error when changing primary key id to auto_increment says it cannot be modified. How to resolve this?

Translated
1

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

Original topic: 把主键id改成auto_increment报错说这是住建不能改，这个怎么弄？

| username: emerson_cai

[TiDB Usage Environment] Production Environment / Testing / Poc
[TiDB Version]
[Reproduction Path] What operations were performed to encounter the issue
[Encountered Issue: Issue Phenomenon and Impact]
[Resource Configuration]
[Attachments: Screenshots / Logs / Monitoring]

The original table is:

CREATE TABLE `model` (
  `id` bigint(20) unsigned NOT NULL /*T![auto_rand] AUTO_RANDOM(5) */,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_model_group_id` (`model_group_id`),
  UNIQUE KEY `uk_unique_identification` (`unique_identification`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T![auto_rand_base] AUTO_RANDOM_BASE=150001 */

When I used Navicat’s data synchronization feature to sync data to another database, it became:

CREATE TABLE `model` (
  `id` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */,
  KEY `idx_model_group_id` (`model_group_id`),
  UNIQUE KEY `uk_unique_identification` (`unique_identification`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

The AUTO_RANDOM attribute in the table was lost, causing data addition to fail with an error saying the id is missing (it can no longer be auto-generated).
Using ALTER TABLE model MODIFY COLUMN id bigint AUTO_RANDOM(5); to modify it results in an error:

b9f7acf29605cdb0ff633e21faab0b9
b9f7acf29605cdb0ff633e21faab0b91380×158 13.2 KB

How can this be resolved?

2
| username: 芮芮是产品 | Original post link

Create a new table using select into; drop the old table; rename the new table to the old table’s name.

3
| username: emerson_cai | Original post link

– SELECT * INTO unique_check_backup FROM unique_check; is not supported

INSERT INTO unique_check_backup SELECT * FROM unique_check;

4
| username: zhanggame1 | Original post link

Indeed, it cannot be changed; you can only rebuild the table and then insert the data into it.

5
| username: 大飞哥online | Original post link

You can first create the table structure on the target side, and then use Navicat for data synchronization. This way, it will work. As for now, since it has already been generated, it cannot be changed and needs to be rebuilt.

6
| username: 大飞哥online | Original post link

The official documentation also provides explanations on the usage limitations of auto_random, take a look.

7
| username: Fly-bird | Original post link

This really cannot be changed.

8
| username: dba-kit | Original post link

There’s no way to directly modify it; you can only change it by rebuilding the table. However, you can check whether the driver used by Navicat is the official MySQL driver. The MariaDB driver will ignore comments by default, causing attributes like /*T![auto_rand] AUTO_RANDOM(5) */ to be ignored and resulting in the creation of a regular table.

9
| username: zxgaa | Original post link

Clustered index cannot be changed.

10
| username: 随缘天空 | Original post link

Check if the primary key is referenced by other tables. If it is, first remove the reference relationship, then try modifying it.

11
| username: Kongdom | Original post link

The only solution is to rebuild the table.

12
| username: Hacker007 | Original post link

The primary key cannot be changed. The primary key and table ID are used to locate a unique piece of data.

13
| username: 随缘天空 | Original post link

It is used to locate unique data. Now I just want to modify its auto-increment type without changing the field name.

14
| username: heiwandou | Original post link

The primary key cannot be changed.

15
| username: zxgaa | Original post link

You can only rebuild it.

16
| username: Jolyne | Original post link

This is a CLUSTERED table, and CLUSTERED tables do not support modifications.