Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.
Original topic: tidb支持修改primary key吗？
[TiDB Usage Environment] Production Environment
[TiDB Version] 6.5
Previously, the data was in MySQL, and there is a business need to modify the primary key. Does TiDB support modifying the primary key? Also, after the data is imported, the primary key is CLUSTERED. Rebuilding the table is not an option, as the data would need to be reprocessed… Is there no solution?
I remember there was a parameter limit, right? 
This parameter was deprecated in 6.5. In that case, it should be as you said, modification is not supported.
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 an integer type primary key, 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.
Is the business requirement to modify the primary key referring to changing the value of the primary key or changing the primary key to another field? TiDB does not support modifying or deleting clustered primary keys.
What exactly was changed to what?
You can consider creating the table structure in TiDB first, and then importing the data.
Redesign the table structure, and use clustered and non-clustered tables according to the actual situation.
Clustered index tables should not be supported.
Yes, it seems that the only option is to rebuild the table in the business layer and then migrate the data over.
Well, we can only redesign the table. This issue arose because this point wasn’t covered when importing data from MySQL in the early stages.
Previously, the approach in MySQL was to drop the primary key first and then add it again. After migrating the data to TiDB, it became clustered, so it can’t be dropped. Currently, it seems the only solution is to rebuild the table and then write the data again.
What is your business scenario? Have you migrated your business database from MySQL to TiDB?
Yes, this scenario was overlooked.
Recreate the table and use the insert into syntax to transfer the data.
MySQL does this, ONLINE DDL also requires locking the table.
That’s about all we can do~
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
|
username: liuis
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: 
|
username: