MySQL add primary key to existing table

This article will look into adding a primary key to an existing table with and without data. We will be illustrating the concept using different examples.

Table of Contents:-

• MySQL add primary key to existing empty table
• MySQL add primary key to existing table with data

Let us get started by preparing the sample data. We will be creating a table, employee_name_details, defining emp_id as the primary key.

 CREATE TABLE employee_details(
  emp_id int,
  emp_enroll_no varchar(255) NOT NULL,
  emp_firstName varchar(255) DEFAULT NULL,
  emp_lastName varchar(255) DEFAULT NULL,
  primary key(emp_id)
);

Action Output:-

DESC employee_details;

Output:-

MySQL add primary key to existing empty table

Let us now try to add the primary key to an existing table. We will first have to DROP the existing primary key and ADD the new one. Observe the below ALTER statement for the solution.

ALTER TABLE employee_details DROP PRIMARY KEY, ADD PRIMARY KEY(emp_enroll_no);

Action Output:-

We will again do a DESC on the table to view if the primary key is changed.

DESC employee_details;

Output:-

The output in image_4 shows that the emp_enroll_no is now the new primary key of the table.

MySQL add primary key to existing table with data

Let us now try to add a primary key to an existing table with data.

Note: that we will drop and recreate the table employee_details and insert a few rows to it.

#drop the existing employee_details table
DROP TABLE employee_details;
#create the table employee_details
CREATE TABLE employee_details(
  emp_id int ,
  emp_enroll_no varchar(255) NOT NULL,
  emp_firstName varchar(255) DEFAULT NULL,
  emp_lastName varchar(255) DEFAULT NULL,
  primary key(emp_id)
);
#insert rows to employee_details
INSERT INTO employee_details (emp_id,emp_enroll_no,emp_firstName,emp_lastName) 
 VALUES(1,"1-N","Henry","Smith"),
 (2,"2-N","Richa","Johnson"),
 (3,"3-N","Veronica","Brown"),
 (4,"4-N","George","Jones"),
 (5,"5-N","Harsh","Garcia"),
 (6,"6-N","William","Jones"),
 (7,"7-N","Rebecca","Miller"),
 (8,"8-N","Paul","Davis"),
 (9,"9-N","Pualou","Miller");

To view the table employee_details along with the data in it, execute:

SELECT * FROM employee_details;

Output:-

DESC employee_details;

Output:-

Again, let us try to change the primary key to emp_enroll_no by executing:

ALTER TABLE employee_details DROP PRIMARY KEY, ADD PRIMARY KEY(emp_enroll_no);

Action Output:-

DESC employee_details;

Output:-

The output in image_8 shows that the emp_enroll_no is now the new primary key of the table.

We will now demonstrate inserting another row to the table employee_details with an existing emp_id (8)

INSERT INTO employee_details ( emp_id,emp_enroll_no, emp_firstName, emp_lastName) 
VALUES(8,"10-N","Pamela","Smith");

Action Output:-

Insert is successful.

SELECT * FROM employee_details WHERE emp_id =8;

Output:-

Since emp_id is no longer a primary key, the script allows inserting two emp_id’s with the same value. But, if we try to insert another row with an existing emp_enroll_no, we will get an error. Observe the below query and the output message response.

INSERT INTO employee_details ( emp_id,emp_enroll_no, emp_firstName, emp_lastName) 
VALUES(10,"8-N","Pamela","Smith");

Action Output:-

An error 1062 is returned with a message: “Error Code: 1062. Duplicate entry ‘8-N’ for key ’employee_details.PRIMARY”

READ MORE:

• MySQL get data by string length
• Mysql update set multiple rows
• Mysql update column with value from another table
• MySQL Update with Inner Join
• MySQL Insert with Join
• MySQL: Insert in a loop

We hope this article helped you add a primary key to an existing MySQL table with and without data. Good Luck!!!