Is a unique index in TiDB necessarily a non-secondary index?

Translated
1

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

Original topic: TiDB中唯一索引一定是非二级索引吗

| username: alfred

[TiDB Usage Environment] Production Environment / Testing / PoC
[TiDB Version]
[Encountered Issues]
[Reproduction Path] What operations were performed to encounter the issue
[Issue Phenomenon and Impact]

[Attachments]

Please provide the version information of each component, such as cdc/tikv, which can be obtained by executing cdc version/tikv-server --version.

2
| username: 数据小黑 | Original post link

In my impression, unique indexes created separately are all secondary indexes, right? Does the so-called non-secondary index refer to the index created when the primary key is an int during table creation?

3
| username: BraveChen | Original post link

Of course not. A unique index is just an index with the unique keyword added, and it is not directly related to whether it is a secondary index or not, but…

4
| username: h5n1 | Original post link

Except for clustered indexes, all others are secondary indexes.

5
| username: alfred | Original post link

I see. If the secondary index is a unique index, its value stores the primary key index. If the secondary index is a non-unique index, its value stores null, which means that apart from the primary key index, everything else is a secondary index.

6
| username: forever | Original post link

This clustered table is also different from a non-clustered table.

7
| username: 人如其名 | Original post link

This is not entirely correct. In TiDB, the primary key can also be a secondary index. According to h5n1’s statement, except for the clustered index, all others are secondary indexes.

8
| username: alfred | Original post link

So, does that mean the indexes of non-clustered tables are all secondary indexes? Even if there is a primary key index?

9
| username: 人如其名 | Original post link

Yes.

10
| username: Raymond | Original post link

Then the unique index of the clustered index is also a secondary index, right? A unique index, whether clustered or not, is a secondary index, right?

11
| username: alfred | Original post link

Yes, in TiDB, apart from the primary key index of the clustered table, all others are secondary indexes.

12
| username: system | Original post link

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