How to Add Primary Key on Multiple Columns in MySQL?

Adding a primary key on multiple columns in MySQL is an important task when designing and managing databases. A primary key ensures data integrity and facilitates efficient data retrieval by serving as the unique identifier for every single row/record in a table. Whether you are creating a new table or modifying an existing one, understanding the process of adding a primary key on multiple columns is essential.

This post explains how to add a primary key to multiple columns of a table in MySQL.

How to Add/Create Primary Key on Multiple Columns in MySQL?

To add a primary key on multiple columns in MySQL, first, log in with the appropriate privileges. After that, users can add the primary key to multiple columns of an existing or newly created table.

Adding Primary Key on Multiple Columns While Table Creation

To understand how to add a primary key on multiple columns while creating a table, you need to learn how to add a primary key on a single column during the table creation. An example of creating a table name “lh_PrimaryKey” is provided below:

The primary key is added to only one column named “id” in the above example.

Output

The output showed that the table has been created with a primary key added.

To confirm if the primary key is added or not, use the “DESCRIBE” keyword with the table name as given below:

Output

The output displayed that the primary key has been added to the “id” column of the “lh_PrimaryKey” table.

Now let’s suppose you want to add the primary key on multiple columns while creating it. To do that, the “PRIMARY KEY” clause can be used followed by the parentheses, and specify the name of the column within the parenthesis, as shown below:

In the above example, the primary key is added to the columns named “id”, “name”, and “email”.

Output

The output depicts that the table has been created and the primary key on multiple columns has been added.

For confirmation, use the DESCRIBE statement with the table name as given below:

Output

In the output, it can be seen that the primary key has been added to the multiple columns of the table.

Adding Primary Key on Multiple Columns of an Already Existing Table

To add a primary key to multiple columns of an existing table, you must have a table without any primary key. For this post, the “lh_PrimaryKey” table will be used whose structure is shown in the following snippet using the “DESCRIBE” command:

Output

The output showed that the given table doesn’t have any primary key.

To add a primary key on multiple columns of an existing table, use the “ALTER TABLE” command with the “ADD PRIMARY KEY” constraint. Here’s an example command that demonstrates adding a primary key on multiple columns to an existing table:

In the above command, the primary key is added to the “id”, “name”, “email”, and “city” columns of a table named “lh_PrimaryKey”.

Output

That’s all about adding a primary key on multiple columns in MySQL.

Conclusion

Adding a primary key on multiple columns in MySQL can be achieved either while table creation or on an existing table using the “PRIMARY KEY” constraint. When creating a table, the “PRIMARY KEY” can be added to the desired columns by using the “PRIMARY KEY (col_1, col_2, col_3, …)” syntax. For an existing table, the “ALTER TABLE” statement is used along with the “ADD PRIMARY KEY” constraint. This blog has explained a detailed procedure for adding a primary key to multiple columns of a table.