Full support for altering primary key

[scsldb]

Description

Full support for altering primary key. The current "alter-primary-key" can only be treated as a workaround: it is with a lot of limitations and the semantic of it is confusing:

If an existing table defined a single integer column as its primary key, the primary key cannot be altered.

Altering integer primary key for is challenging in TiDB because it is special: the row id of the data is mapped from the integers, so it determines where the actual row data is stored. When Cluster Index is supported, such limitation will be extended to all explicit primary keys.

Category

Feature, Usability

Value

The ability to change the primary key is an important feature of a maintainable database. Users usually starts their deployments in a simple scenario, and with the growth of the application there will be likely the requirement to change their primary keys.

Workload Estimation

Seems the only way to implement this feature is the 'COPY' algorithm, if we want to make it available online:

1. Create a hidden table that is with the changed(new) primary key.
2. All updates of the existing table are applied to the changed table as well.
3. Backfill all rows from existing table to the new table.

75

[tirsen]

This will be able to convert a non-clustered table to a clustered table as well? Even if there are no other changes to the table? What would be the SQL syntax for doing that?

[zz-jason]

This will be able to convert a non-clustered table to a clustered table as well? Even if there are no other changes to the table?

If tidb_enable_clustered_index is true, the correct behavior of this feature is to cluster the new primary key with the table records. So I think the answer should be yes if tidb_enable_clustered_index is true before altering the primary key.

[zz-jason]

What would be the SQL syntax for doing that?

Seems MySQL doesn't provide the direct syntax to alter a primary key, users have to drop the originally primary key firstly, then add a new primary key.

@nullnotnil do you have any suggestions on the syntax to alter the primary key? or should we keep the same behavior with MySQL?

[tirsen]

No I actually don't want to alter the primary key. I want to rebuild a table that is using a composite key from non-clustered to clustered.

For example if I have a table like this:

create table order (
    id bigint,
    customer_id bigint,
    -- other columns
    primary key (customer_id, id)
)

On TiDB 4.0 this table will be non-clustered with a hidden _tidb_rowid column.

Then I upgrade to TiDB 5.0. Now I want to rebuild the table to become clustered without changing anything else about the schema. What would be the syntax for that?

(I think we also need a syntax for changing primary key btw, but that's a separate issue. Probably the syntax should be similar.)

Currently, it's also not possible to either add or remove partitioning from a table. Presumably this will require the COPY algorithm as well:

mysql> ALTER TABLE t1 PARTITION BY RANGE ( YEAR(`start_date`) ) (
    ->   PARTITION `p2010` VALUES LESS THAN (2011),
    ->   PARTITION `p2011` VALUES LESS THAN (2012),
    ->   PARTITION `p2012` VALUES LESS THAN (2013),
    ->   PARTITION `p2013` VALUES LESS THAN (2014),
    ->   PARTITION `p2014` VALUES LESS THAN (2015),
    ->   PARTITION `p2015` VALUES LESS THAN (2016),
    ->   PARTITION `p2016` VALUES LESS THAN (2017),
    ->   PARTITION `p2017` VALUES LESS THAN (2018),
    ->   PARTITION `p2018` VALUES LESS THAN (2019),
    ->   PARTITION `p2019` VALUES LESS THAN (2020),
    ->   PARTITION `pmax` VALUES LESS THAN (MAXVALUE)
    -> );
ERROR 1105 (HY000): alter table partition is unsupported

mysql> ALTER TABLE trips_partitioned REMOVE PARTITIONING;
ERROR 8200 (HY000): Unsupported remove partitioning