Does TiDB support modifying the primary key?

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

Original topic: tidb支持修改primary key吗?

| username: liuis

[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?

| username: Kongdom | Original post link

I remember there was a parameter limit, right? :joy: 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.

| username: wzf0072 | Original post link

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.

| username: 啦啦啦啦啦 | Original post link

What exactly was changed to what?

| username: wzf0072 | Original post link

You can consider creating the table structure in TiDB first, and then importing the data.

| username: Jolyne | Original post link

Redesign the table structure, and use clustered and non-clustered tables according to the actual situation.

| username: 胡杨树旁 | Original post link

Clustered index tables should not be supported.

| username: liuis | Original post link

Yes, it seems that the only option is to rebuild the table in the business layer and then migrate the data over.

| username: liuis | Original post link

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.

| username: liuis | Original post link

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.

| username: wzf0072 | Original post link

What is your business scenario? Have you migrated your business database from MySQL to TiDB?

| username: liuis | Original post link

Yes, this scenario was overlooked.

| username: Running | Original post link

Recreate the table and use the insert into syntax to transfer the data.

| username: xingzhenxiang | Original post link

MySQL does this, ONLINE DDL also requires locking the table.

| username: liuis | Original post link

That’s about all we can do~

| username: system | Original post link

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