Primary key is the unique identifier used to uniquely identify each row in a database table. It consists of one or more columns. In most cases, a table is required to have primary key to maintain data integrity and relationship with other tables. But sometimes you may want to remove primary key or remove key constraint in MySQL. In this article, we will learn how to remove primary key in MySQL.
How to Remove Primary Key in MySQL
Let us say you have the following sales_product table that maps sales table to products table in MySQL.
mysql> describe sales_product; +------------------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | sale_id | int(11) | NO | PRI | NULL | | | product_id | int(11) | NO | PRI | NULL | | +------------------+---------+------+-----+---------+----------------+
In the above example, our primary key consists of id, sale_id, and product_id with id column as auto increment. Since id column is already unique, it is not necessary to keep sale_id and product_id in primary key. If you want to do this, first we will drop the entire primary key and then set id column only as our primary key, using the following statement.
ALTER TABLE `sales_product` DROP PRIMARY KEY; ALTER TABLE `sales_product` ADD PRIMARY KEY ( `id` );
You can combine both these SQL queries into a single one as shown below.
ALTER TABLE `sales_product` DROP PRIMARY KEY , ADD PRIMARY KEY ( `id` )
On the other hand, if you want to drop id column from primary key and let sales_id and product_id be part of primary key then you can need to first modify id column to stop auto incrementing first and then drop primary key.
ALTER TABLE sales_product MODIFY id INT NOT NULL; ALTER TABLE sales_product DROP PRIMARY KEY; ALTER TABLE sales_product ADD PRIMARY KEY (sales_id, product_id);
Please note, the last statement is optional since the primary key will be consisting of both sales_id and product_id whose combination will be unique mostly.
In this article, we have learnt how to remove primary key in MySQL. However, it is advisable not to remove primary keys in MySQL.
Also read:
How to Find Most Frequent Column Value in MySQL
How to Truncate Foreign Key Constrained Table
How to Export MySQL Schema Without Data
How to Insert Element After Another Element in JS
How to Sum Values in JS Object