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.)

[ghost]

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

[ghost]

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

No reason to differ from MySQL here:

DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (pk INT NOT NULL auto_increment, b INT, PRIMARY KEY(pk));
ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY (pk, b);

MySQL supports adding a primary key online, but dropping a primary key is not. See Table 14.11 Online DDL Support for Primary Key Operations. It would be nice of course if TiDB can support online for both aspects.

Note: For the case @tirsen mentions, MySQL supports the ability to rebuild a table using what the manual describes as a "null" alter table:

Running ALTER TABLE tbl_name ENGINE=INNODB on an existing InnoDB table performs a “null” ALTER TABLE operation, which can be used to defragment an InnoDB table, as described in Section 14.12.4, “Defragmenting a Table”. Running ALTER TABLE tbl_name FORCE on an InnoDB table performs the same function.

MySQL had a feature to show if temporals were in an old format at one point, which made it easy to identify tables that needed upgrades. I expect that similar could be done to expose that a table is not using a clustered index. It would be nice to show this info in information_schema too.

[devhaozi]

I'm waiting for support this.

[dveeden]

I'm waiting for support this.

• Please give the description of this issue a 👍 as that might help others when sorting issues by popularity.
• I think everyone agrees that implementing this would be very useful, but maybe you can share some more details about your situation and why/how this is causing issues for you.

[devhaozi]

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.)

In my needs, sometimes it is necessary to add new columns to existing tables. In order to improve performance, it may be necessary to delete the primary key, and then create a compound index with several columns.

Currently, TiDB does not support deleting primary keys, so it is impossible to create a compound index specifically.