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

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

image_1
DESC employee_details;

Output:-

image_2

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

image_3

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

SELECT * FROM employee_details;

Output:-

image_4

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

image_5

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

DESC employee_details;

Output:-

image_6

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:

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