Unable to Modify Primary Key

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

Original topic: 修改主键修改不了

| username: rw12306

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version] 6.5.0
[Reproduction Path] What operations were performed to encounter the issue
[Encountered Issue: Problem Phenomenon and Impact]
I want to modify a composite primary key, but it can’t be modified. The previous primary key fields were MEDICAL_INSTITUT_CODE and REPORT_NUMBER. Now I want to add a field but it doesn’t work. I want to add APPLY_NUMBER. Is there any way to do this?
[Resource Configuration]
[Attachment: Screenshot/Log/Monitoring]

| username: Kongdom | Original post link

There is no other way, you can only rebuild the table.

| username: rw12306 | Original post link

There are millions of data… Why is this upgrade incompatible? The previous versions were all fine.

| username: Kongdom | Original post link

That’s fine, the data volume is not large. Create a new table, rename to replace the old table, new data is written normally, and then import the historical data.

| username: rw12306 | Original post link

There are mainly several tables.

| username: Kongdom | Original post link

Are you talking about this? :joy: This parameter was deprecated in version 6.5. In that case, it should be as you said, not supporting modification.

Modifying integer type primary keys is not supported because when the primary key is of integer type, TiDB uses it as the index for data organization.

alter-primary-key (Deprecated)

  • Used to control the addition or deletion of primary keys.
  • Default value: false
  • By default, adding or deleting primary keys is not supported. After setting this variable to true, adding or deleting primary keys is supported. However, for tables that existed before this switch was enabled and have integer type primary keys, deleting the primary key is not supported even after enabling this switch.

Note

This configuration item has been deprecated and currently only takes effect when @@tidb_enable_clustered_index is set to INT_ONLY. If you need to add or delete primary keys, please use the NONCLUSTERED keyword when creating the table. For detailed information about CLUSTERED primary keys, please refer to Clustered Indexes.

| username: Kongdom | Original post link

If it’s just for uniqueness, you can add a unique index.

| username: rw12306 | Original post link

I used to use CLUSTERED for everything, but now I have to rebuild the table to add one, which is very troublesome. I saw that it was possible in 6.1, but after upgrading to 6.5, I found that it can’t be changed now. The main issue is that there are several tables, and each place has to be rebuilt one by one.

| username: Kongdom | Original post link

Yes, we have also encountered this problem, and it is indeed a bit troublesome. This requires even more adherence to table creation standards. Alternatively, you can use NONCLUSTERED instead.

| username: rw12306 | Original post link

That also requires rebuilding the table to specify. This is too troublesome.

| username: Kongdom | Original post link

:yum: I can only say, make sure to review the upgrade logs carefully before the next upgrade.

| username: zhanggame1 | Original post link

It only takes a few seconds to rebuild millions.

| username: system | Original post link

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