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

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

image_1
DESC employee_details;

Output:-

image_2

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

image_3

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

DESC employee_details;

Output:-

image_4

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

image_5
DESC employee_details;

Output:-

image_6

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

image_7
DESC employee_details;

Output:-

image_8

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

image_9

Insert is successful.

SELECT * FROM employee_details WHERE emp_id =8;

Output:-

image_10

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

image_11

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

READ MORE:

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