MySQL add primary key multiple columns

This article will add a primary key to multiple columns while creating a table and existing tables with data. We will be illustrating the concept using different examples.

Table of Contents:-

• Add primary key multiple columns to new table
• Add primary key multiple columns to existing table with data and existing primary key

Primary keys are the constraint in MySQL to uniquely identify a record in a table. One can apply a primary key on a single column or multiple columns of a table. When more than one attribute is required to identify a column uniquely, the primary key will consist of more than one column and is called the composite primary key.

Add primary keys multiple columns to new table

We will be creating a table named employee_details and will add a composite primary key to it. Observe the below create table statement.

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

Action Output:-

DESC employee_details;

Output:-

The output in image_2 shows that the primary key is added to multiple columns emp_id and emp_enroll_no.

Add primary keys multiple columns to existing table with data and existing primary key

Let us insert some rows to the table employee_details.

INSERT INTO employee_details (emp_id,emp_enroll_no,emp_city,emp_firstName,emp_lastName) 
 VALUES(1,"1-N","Michigan","Henry","Smith"),
 (2,"2-N","Santiago","Richa","Johnson"),
 (3,"3-N","Santiago","Veronica","Brown"),
 (4,"4-N","Washington","George","Jones"),
 (5,"5-N","New York","Harsh","Garcia"),
 (6,"6-N","New York","William","Jones"),
 (7,"7-N","Santiago","Rebecca","Miller"),
 (8,"8-N","Santiago","Paul","Davis"),
 (9,"9-N","New York","Pualou","Miller");

Action Output:-

Execute the below statement to view data in the table employee_details.

SELECT * FROM employee_details;

Output:-

To change the primary key on the table, which already has some data and an existing primary key, we will have to DROP the already present primary key and add the new one using ALTER statement.

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

Action Output:-

Let us confirm if the primary key is changed by executing:

DESC employee_details;

Output:-

The output in image_6 shows that the new primary key is added to three columns emp_id, emp_enroll_no, and emp_city.

READ MORE:

• MySQL add primary key to existing table
• Mysql update set multiple columns

We hope this article helped you add a primary key to multiple columns in a MySQL table. Good Luck!!!