content_image/761527.jpg
ALTER TABLE tbl_name  DROP INDEX i1, ADD INDEX i1( key_part,... ) USING BTREE, ALGORITHM=INSTANT; 

## Primary Key Operations 

The following table provides an overview of online DDL support for primary key operations. An asterisk indicates additional information, an exception, or a dependency. See Syntax and Usage Notes. 

## Table 15.17 Online DDL Support for Primary Key Operations 

<img src='content_image/761527.jpg'>

## Syntax and Usage Notes 

•Adding a primary key 

ALTER TABLE tbl_name  ADD PRIMARY KEY ( column ), ALGORITHM=INPLACE, LOCK=NONE; 

Rebuilds the table in place. Data is reorganized substantially, making it an expensive operation. ALGORITHM=INPLACE  is not permitted under certain conditions if columns have to be converted to NOT NULL . 

Restructuring the clustered index always requires copying of table data. Thus, it is best to define the primary key when you create a table, rather than issuing ALTER TABLE ... ADD PRIMARY KEY  later. 

When you create a UNIQUE  or PRIMARY KEY  index, MySQL must do some extra work. For UNIQUE indexes, MySQL checks that the table contains no duplicate values for the key. For a PRIMARY KEY index, MySQL also checks that none of the PRIMARY KEY  columns contains a NULL . 

When you add a primary key using the ALGORITHM=COPY  clause, MySQL converts NULL  values in the associated columns to default values: 0 for numbers, an empty string for character-based columns and BLOBs, and 0000-00-00 00:00:00 for DATETIME . This is a non-standard behavior that Oracle recommends you not rely on. Adding a primary key using ALGORITHM=INPLACE  is only permitted when the SQL_MODE  setting includes the strict_trans_tables  or strict_all_tables  flags; when the SQL_MODE  setting is strict, ALGORITHM=INPLACE  is permitted, but the statement can still fail if the requested primary key columns contain NULL  values. The ALGORITHM=INPLACE  behavior is more standard-compliant. 

If you create a table without a primary key, InnoDB  chooses one for you, which can be the first UNIQUE key defined on NOT NULL  columns, or a system-generated key. To avoid uncertainty and the potential space requirement for an extra hidden column, specify the PRIMARY KEY  clause as part of the CREATE TABLE  statement. 

MySQL creates a new clustered index by copying the existing data from the original table to a temporary table that has the desired index structure. Once the data is completely copied to the temporary table, the original table is renamed with a different temporary table name. The temporary table comprising the new 

