If I create a table without a primary key then add a primary key, is the primary key a clustered index?

joseph

From Getting Started with Indexes: Primary Key it says:

In InnoDB tables, all indexes contain the primary key as a suffix. Thus, when using this storage engine, keeping the primary key as small as possible is particularly important. If a primary key does not exist and there are no UNIQUE indexes, InnoDB creates a 6-bytes clustered index which is invisible to the user.

So without a primary key, we'll create a table with a hidden clustered index. Then later on the same section a few paragraphs later says:

You cannot create a primary key with the CREATE INDEX command. If you do want to add one after the table has already been created, use ALTER TABLE, for example:

but doesn't say if the new primary key will become a clustered index.

Also ALTER TABLE: ADD PRIMARY KEY only says:

For PRIMARY KEY indexes, you can specify a name for the index, but it is silently ignored, and the name of the index is always PRIMARY.

but does not comment on the clustered index.

My confusion results in these questions:

  1. If I add a primary key later on, will the primary key be clustered?
  2. If so, what happens to the storage layout? Does it have to copy all the data to the new clustered index and then delete the previous hidden clustered index?
  3. Is the primary key always clustered?

Minimum reproducing example:

create database test
use test
create table test_table ( id bigint(20) NULL );

describe test_table;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | bigint(20) | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.003 sec)

show index from test_table;
Empty set (0.000 sec)


alter table test_table add primary key(id);
Query OK, 0 rows affected (0.067 sec)
Records: 0  Duplicates: 0  Warnings: 0

describe test_table;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| id    | bigint(20) | NO   | PRI | NULL    |       |
+-------+------------+------+-----+---------+-------+
1 row in set (0.007 sec)

show index from test_table;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test_table |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.001 sec)
Bill Karwin

If the table has a primary key, then that is always the clustered index (assuming it's in the InnoDB engine).

If you alter a table to add a primary key, then that will become the clustered index.

Adding (or changing) the primary key does need to restructure the table, because the layout will be completely changed.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

Relationship of Primary Key and Clustered Index

How to add a primary key to a mysql table without a primary key

create primary key on existing table without primary key in SQL Server

Table without a primary key

Create an index, or add to the primary key and create a new table?

EF Core Entity with Primary Key but without Clustered Index

Clustered Index on NON-Primary Key Column

Is it possible to create a table in the Room without a primary key?

Should I add ID (primary key) to composite index of a table?

Primary key -Primary Index in DB2 Vs Primary Key - Clustered index in SQL

Add primary key to an existing table

Create a table with a varchar as primary key

Could not create constraint or index or foreign key on primary key of existing table

create a foreign key on a primary key of another table

If primary key is clustered index in a table then the other columns are eg any unique column is a table is non clustered?

Realm index and primary key

Is primary key also index?

sqlite without primary key?

add data into primary key

Cannot add primary key

Set Primary Key for Table?

InnoDB clustered index performance when using random values as primary key

DataTable.PrimaryKey is populated with unique clustered index, not primary key

stored data sorting: nonclustered primary key overrides clustered index

How to create table without primary key in Entity Framework Core?

It's possible, create a table without primary key in TypeOrm?

Mysql table with composite index but not primary key

Partitioned index for primary key on partitioned table

Create an UPDATE statement without primary key