Create and Drop the Primary index in MySQL / MariaDB

Note: For Saver side, Before drop and create primary index we should lock the tables in read write mode , So one not using that table.

Lock and unlock the table

-- Lock the table 
LOCK TABLES table_name WRITE;
-- unlock the table:
UNLOCK TABLES;
-- Check the use of table or lock
show open tables

1. Get the DDL of the table or index present in tables:

MariaDB [test]> create table sales_test (salesid varchar(36) not null, name varchar(36), PRIMARY KEY(SALESID));
Query OK, 0 rows affected (0.055 sec)

MariaDB [test]> SHOW INDEX FROM sales_test;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| sales_test |          0 | PRIMARY  |            1 | salesid     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
1 row in set (0.031 sec)

2. Drop the Primary index which will automatically delete the Primary key constraint.

MariaDB [test]> DROP index `PRIMARY` on sales_test;
Query OK, 0 rows affected (0.129 sec)
Records: 0  Duplicates: 0  Warnings: 0

MariaDB [test]> SHOW INDEX FROM sales_test;
Empty set (0.022 sec)

MariaDB [test]> desc sales_Test;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| salesid | varchar(36) | NO   |     | NULL    |       |
| name    | varchar(36) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.037 sec)

3. Create the Primary index again

MariaDB [test]> ALTER TABLE sales_test ADD PRIMARY KEY (`salesid`);
Query OK, 0 rows affected (0.109 sec)
Records: 0  Duplicates: 0  Warnings: 0

4. Verify the Primary index created.

MariaDB [test]> desc sales_Test;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| salesid | varchar(36) | NO   | PRI | NULL    |       |
| name    | varchar(36) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
2 rows in set (0.045 sec)

MariaDB [test]> SHOW INDEX FROM sales_test;
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| Table      | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
| sales_test |          0 | PRIMARY  |            1 | salesid     | A         |           0 |     NULL | NULL   |      | BTREE      |         |               | NO      |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+