When altering the table books to add a primary key, CLUSTERED cannot be specified

Translated
1

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

Original topic: alter table books add primary key时不能指定CLUSTERED

| username: TiDBer_sJAqARVl

Test environment, newly installed.

Server version: 5.7.25-TiDB-v6.1.0 TiDB Server (Apache License 2.0) Community Edition, MySQL 5.7 compatible

mysql> show create table books\G
*************************** 1. row ***************************
       Table: books
Create Table: CREATE TABLE `books` (
  `id` bigint(20) NOT NULL,
  `title` varchar(100) DEFAULT NULL,
  `type` enum('Magazine','Novel','Life','Arts','Comics','Education & Reference','Humanities & Social Sciences','Science & Technology','Kids','Sports') DEFAULT NULL,
  `published_at` datetime DEFAULT NULL,
  `stock` int(11) DEFAULT NULL,
  `price` decimal(15,2) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

# Syntax error
mysql> alter table books add primary key(id) CLUSTERED;
ERROR 8200 (HY000): Adding clustered primary key is not supported. Please consider adding NONCLUSTERED primary key instead

# Dropping and recreating the table with primary key specified as CLUSTERED works fine
mysql> drop table books;
Query OK, 0 rows affected (0.21 sec)

mysql> CREATE TABLE `books` (
    ->   `id` bigint NOT NULL,
    ->   `title` varchar(100) DEFAULT NULL,
    ->   `type` enum('Magazine','Novel','Life','Arts','Comics','Education & Reference','Humanities & Social Sciences','Science & Technology','Kids','Sports') DEFAULT NULL,
    ->   `published_at` datetime DEFAULT NULL,
    ->   `stock` int DEFAULT NULL,
    ->   `price` decimal(15,2) DEFAULT NULL,
    -> primary key(id) CLUSTERED
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> show create table books\G
*************************** 1. row ***************************
       Table: books
Create Table: CREATE TABLE `books` (
  `id` bigint(20) NOT NULL,
  `title` varchar(100) DEFAULT NULL,
  `type` enum('Magazine','Novel','Life','Arts','Comics','Education & Reference','Humanities & Social Sciences','Science & Technology','Kids','Sports') DEFAULT NULL,
  `published_at` datetime DEFAULT NULL,
  `stock` int(11) DEFAULT NULL,
  `price` decimal(15,2) DEFAULT NULL,
  PRIMARY KEY (`id`) /*T![clustered_index] CLUSTERED */
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
2
| username: 裤衩儿飞上天 | Original post link

Cannot.

3
| username: TiDBer_sJAqARVl | Original post link

If it means that you cannot specify it when using alter table add primary key, then the error message should be changed to indicate that it needs to be specified in the create table DDL, rather than indicating that the CLUSTERED attribute is not supported.

4
| username: 裤衩儿飞上天 | Original post link

You can refer to: Clustered Index | PingCAP Docs

As for the prompt message you mentioned, you can report it to the official team and ask them to make some changes. :thinking:

5
| username: TiDBer_sJAqARVl | Original post link

Okay, thank you.

6
| username: forever | Original post link

I think the prompt is fine, it just says “clustered primary key is not supported when adding” :joy:

7
| username: Jiawei | Original post link

I don’t think there’s any issue. From understanding the underlying KV structure of clustered and non-clustered tables, it’s clear that it’s not supported. The error message makes sense to me. From your intention, it seems you want to add a primary key, but since clustered doesn’t support it, the error prompts you to change to non-clustered, which allows adding a primary key. Logically, it seems fine.

8
| username: 胡杨树旁 | Original post link

I don’t quite understand this part. If the primary key is a clustered index, then the key is the primary key, and the value is all the other columns in the table except the primary key. I don’t understand why creating a clustered index after table creation is not supported?

9
| username: Jiawei | Original post link

The image you provided is not visible. Please provide the text you need translated.

10
| username: 胡杨树旁 | Original post link

I understand this part, but I don’t know why clustered indexes cannot be created after the table is created. This point is a bit unclear to me.

11
| username: 裤衩儿飞上天 | Original post link

Physical storage needs to be sorted according to the key. Currently, creating a clustered index after table creation has not been implemented, but it is said that it will be supported in the future.

12
| username: Jiawei | Original post link

I understand that it’s not that it’s unsupported, but rather that for clustered tables, creating a primary key is not allowed because the primary key is already a key. Creating another one would disrupt the underlying data storage format. For non-clustered tables, you can create a primary key index after the table is created. Here, the primary key is similar to a secondary index in MySQL, consisting of the primary key value and rowid.

13
| username: 胡杨树旁 | Original post link

Now I understand what you’re saying. If you create it, does that mean this KEY appears twice?

14
| username: Jiawei | Original post link

Yes, just like retaining the habit of MySQL, where only one primary key index can be created for a table.

15
| username: 胡杨树旁 | Original post link

Okay, I actually never really understood this principle and always memorized it mechanically. This time I got it. :pinched_fingers: