How to Create MySQL Composite Primary Keys Simplified 101?

MySQL is a Relational Database Management System. This Open-source tool is one of the best RDBMS available in the market that is being used to develop web-based software applications among others. MySQL is scalable, intuitive, and swift when compared to its contemporaries. It houses a Client-Server architecture. At the core of the MySQL Database lies the MySQL Server. This Server is availed as a separate program and handles all the database instructions, commands, and statements.

In this article, you will gain information about MySQL Composite Primary Keys. You will also gain a holistic understanding of MySQL, its key features, Primary Key, Composite Primary Key, and the different methods of creating MySQL Composite Primary Keys. Read along to find out in-depth information about MySQL Composite Primary Keys.

Table of Contents

• Prerequisites
• What is MySQL?
  • Key Features of MySQL
• What is a Primary Key?
• What is a Composite Primary Key?
• How to Create MySQL Composite Primary Key?
  • Method 1: Creating MySQL Composite Primary Key while Table Creation
  • Method 2: Adding MySQL Composite Primary Key in Existing Table
• Conclusion

Prerequisites

• Working Knowledge of RDBMS
• Working Knowledge of MySQL

What is MySQL?

MySQL is a reliable Database Management System (DBMS) that makes use of Structured Query Language (SQL) to carry out data-associated operations. Its well-known version offers an open-source platform that you could get admission to without problems and perform your Data Management duties. However, the superior MySQL variations, which can be tailored to fulfill your unique requirements, are offered for a month-to-month fee. Companies these days depend on the MySQL platform to have a scalable, and dependable answer for their business needs.

MySQL’s simplicity has given it an advantage over its peer DBMS systems such as Microsoft SQL Server and Oracle Databases. Moreover, you could seamlessly collaborate with MySQL and any programming language used in your niche. This platform additionally integrates seamlessly with Linux, Unix, Windows, and different operating systems. Furthermore, as per your configuration requirements, you could use MySQL after loading it to your local machine or immediately from a server.

Key Features of MySQL

The following features make MySQL a popular DBMS in the market: 

• High performance: MySQL database allows for fast data processing and has an easy-to-use consumer interface. It can also accommodate a number of users simultaneously and you may log into this platform from any location.
•  Compatibility: With MySQL, you may revel in excessive velocity and coffee latency statistics transactions. Furthermore, it’s also viable to apply internet improvement gear in this platform.
• Scalability: MySQL platform permits you to seamlessly upscale or downscale your commercial enterprise anytime. It additionally adapts to the maximum famous working structures like Linux, OS X, Windows, etc.

To get more details regarding MySQL, visit here.

What is a Primary key?

Each entry in a table is uniquely identified by the PRIMARY KEY constraint. Primary keys must have UNIQUE values and should not have NULL values. A table can only have ONE primary key, and this primary key might be made up of single or many columns (fields).

What is a Composite Primary Key?

A Composite Primary Key is created by combining two or more columns in a table that can be used to uniquely identify each row in the table when the columns are combined, but it does not guarantee uniqueness when taken individually, or it can also be understood as a primary key created by combining two or more attributes to uniquely identify every row in a table.

Note: 

• A Composite Primary key can also be made by the combination of more than one candidate key.
• A Composite Primary Key cannot be null.

How to Create MySQL Composite Primary Key?

You can create a MySQL composite Primary Key in the following two ways:

• During table creation using CREATE TABLE statement.
• After creating tables using ALTER TABLE statement.

We will look at both these examples to create MySQL composite primary key.

The following ways are:

• Method 1: Creating MySQL Composite Primary Key while Table Creation
• Method 2: Adding MySQL Composite Primary Key in Existing Table

1) Creating MySQL Composite Primary Key while Table Creation

To create MySQL composite primary key during table creation you can follow the steps.

Suppose you want to create Customers with the fields (order_id, product_id, amount) table with a MySQL Composite primary key (order_id, product_id).

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

In the above snippet, a MySQL Composite primary is created with the column names order_id and product_id.

You can verify the same using the command as below:

mysql> Describe Customers;

Output:

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

After the successful execution of the above query, you can see that the Key column has two PRI. It means you have successfully added the MySQL Composite Primary key on order_id and product_id columns.

Next, you need to insert the values into this table as given below:

INSERT INTO Customers (order_id, product_id, amount)  
VALUES (101, 509, 800),  
(102, 610, 799),  
(103, 702, 650);

Next, you can execute the following command to display the data in the table:

SELECT * FROM Customers;  

Output:

+---------------+---------------+--------+
| order_id      | product_id    | amount |
+---------------+---------------+--------+
| 101           | 509           | 800    |
| 102           | 610           | 799    | 
| 103           | 702           | 650    |   
+---------------+---------------+--------+

Again you can execute the below INSERT statement to understand MySQL Composite Primary key with better clarity.

INSERT INTO Customers (order_id, product_id, amount)  
VALUES (103, 702, 699);  
  
INSERT INTO Customers (order_id, product_id, amount)
VALUES (103, 870, 599);  

In the output given below, you can see that if you try to add the same order id and product id, it will throw an error saying: “Duplicate entry for consumers.primary“.

If you execute the second INSERT statement, it will be added successfully into the table. It is because you can insert any number of 103 in the order_id column, but the product_id column should be different.

As a result, the MySQL Composite Primary key always guarantees the uniqueness of the columns of that table, which has two keys.

2) Adding MySQL Composite Primary Key in Existing Table

The ALTER statement is always used to make modifications table. To uniquely identify each record in the database with more than one attribute, the MySQL Composite Primary Key is sometimes necessary. In that case, an ALTER TABLE statement can be used.

Below is given an example of how to add MySQL Composite Primary Key in the existing table. Let’s say you have the following table.

First you can create a table “Orders” using the below statement.

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

Next, you can execute the following command to display the data in the table:

mysql> Describe Orders;

Output:

+------------+---------+------+-----+---------+-------+
| 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 Orders table lacks a Primary Key because there is no mention of PRI in the Key column in the prior table.

Now, you can execute the ALTER TABLE statement to add a MySQL Composite Primary key as follows:

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

You can verify the MySQL Composite Primary key is added into a table or not using the following command:

You can use the following command to verify if the MySQL Composite Primary key has been added to the table.

mysql> Describe Orders;

Output:

+------------+---------+------+-----+---------+-------+
| 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 output, we can see that the key column has 2 PRI, which means we have successfully added the composite primary key to order_id and product_id columns.

A) MySQL Composite Primary Key Index

You may create a MySQL Composite Primary key index that utilises the same columns as your MySQL Composite Primary key.

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

Conclusion

In this article, you have learned about MySQL Composite Primary Keys. This article also provided information on MySQL, its key features, Primary Key, Composite Primary Key, and the different methods of creating MySQL Primary Key in detail. For further information on MySQL Schema, MySQL Views, MySQL JSON Connection, you can visit the former links.

Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations with a few clicks.

Visit our Website to Explore Hevo

Hevo Data with its strong integration with 100+ data sources (including 40+ Free Sources) allows you to not only export data from your desired data sources & load it to the destination of your choice but also transform & enrich your data to make it analysis-ready. Hevo also allows integrating data from non-native sources using Hevo’s in-built Webhooks Connector. You can then focus on your key business needs and perform insightful analysis using BI tools. 

Want to give Hevo a try?

Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You may also have a look at the amazing price, which will assist you in selecting the best plan for your requirements.

Share your experience of understanding MySQL Composite Primary Keys in the comment section below! We would love to hear your thoughts.