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
- 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 |
+------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+