How to Create Composite Primary Key in MySQL

Sometimes you may need to create composite primary key consisting of multiple fields, for database tables. Here’s how to create composite primary key in MySQL.

How to Create Composite Primary Key in MySQL

Here are the steps to create composite primary key in MySQL. You can create composite primary key, either during table creation using CREATE TABLE statement, or after creating tables using ALTER TABLE statement. We will look at both these examples to create composite primary key in MySQL.

MySQL Composite Primary Key Examples

Here’s how to create composite primary key in MySQL during table creation. Let’s say you want to create orders(order_id, product_id, amount) table with composite primary key (order_id, product_id).

mysql> CREATE TABLE orders_list (
          order_id INT,
          product_id INT,
          amount INT,
          PRIMARY KEY (order_id, product_id)
     ) ;

mysql> describe orders_list;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| order_id   | int(11) | NO   | PRI | 0       |       |
| product_id | int(11) | NO   | PRI | 0       |       |
| amount     | int(11) | YES  |     | NULL    |       |
+------------+---------+------+-----+---------+-------+

In the above query, we have created composite primary key (order_id, product_id). In the above output, you will see PRI mentioned in Key column for order_id, product_id indicating that they are both parts of the primary key.

Bonus Read : How to Truncate Table in MySQL

Add Composite Primary Key in Existing Table

Here’s an example of how to add composite primary key in existing table. Let’s say you have the following table

mysql> CREATE TABLE new_orders (
         order_id INT,
         product_id INT,
     amount INT
     ) ;

mysql> describe new_orders;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| order_id   | int(11) | YES  |     | NULL    |       |
| product_id | int(11) | YES  |     | NULL    |       |
| amount     | int(11) | YES  |     | NULL    |       |
+------------+---------+------+-----+---------+-------+

As you can see the new_orders table does not have a primary key. Here’s the SQL query to add composite primary key using ALTER TABLE.

Bonus Read : MySQL DROP VIEW

mysql> alter table new_orders ADD PRIMARY KEY (order_id, product_id);

mysql> describe new_orders;
+------------+---------+------+-----+---------+-------+
| Field      | Type    | Null | Key | Default | Extra |
+------------+---------+------+-----+---------+-------+
| order_id   | int(11) | NO   | PRI | 0       |       |
| product_id | int(11) | NO   | PRI | 0       |       |
| amount     | int(11) | YES  |     | NULL    |       |
+------------+---------+------+-----+---------+-------+

Please note, you cannot create MySQL composite primary key with auto increment.

Bonus Read : How to Create Index in MySQL

MySQL Composite Primary Key Index

You can create an index for composite primary key that uses the same fields present in your composite primary key.

mysql> alter table new_orders ADD INDEX new_index (order_id, product_id);

Hopefully, now you can create composite primary key in MySQL.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

Sreeram Sreenivasan

Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.