Creating MySQL Composite Primary Keys Simplified

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.

MySQL 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.

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);

In the next section let’s learn about dropping composite keys.

Dropping a Composite Key

You can use the ALTER TABLE… DROP statement to drop the composite key from a table.

Syntax

ALTER TABLE table_name DROP PRIMARY KEY;

Example

ALTER TABLE CUSTOMERS DROP PRIMARY KEY;

Output

The following output will be generated using the above My SQL query.

Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

Verification

After dropping the composite from the CUSTOMERS table, you can enter the duplicate values in the columns.

MySQL Composite Primary Key Examples

Consider a simple example to further understand how to create a composite primary key in MySQL. Assume we have a table called students that contains information about students at a school. Each student is identifiable uniquely by a combination of their student ID and course ID. We can use these two columns to form a composite primary key.

To build a composite primary key, we must specify the columns as primary keys while creating or modifying the table. Here’s an example of building the student’s table using a composite primary key:

CREATE TABLE students (
  student_id INT,
  course_id INT,
  name VARCHAR(50),
  PRIMARY KEY (student_id, course_id)
);

In the example above, the composite primary key constitutes the student_id and course_id. This implies that no two rows in the student’s table can have the same pair of values for both columns. Next, let’s cover some key features of MySQL Composite Key −

Features of Composite Keys

• You can create a composite primary key by combining multiple Candidate Keys.
• Each Candidate Key that consists of a Composite Key might be a foreign key. But if all of the Composite Key’s columns are independent Foreign Keys, the Composite Key is referred to as a Compound Key.
• A Composite Key cannot be NULL, which means that none of its columns can contain NULL values.
• The Composite Key’s individual columns may include duplicate values, but their combination must be unique across the table.

It is important to select the column data types when you are creating a My SQL composite primary key. It helps ensure an accurate representation of the data as well as impact query performance and data requirements.

Selecting the Data Types

Here are the factors you should consider when selecting data types for your columns:

1. Data Precision: The range and precision of the values should be considered when selecting the column’s data type.
2. Storage Size: It’s important to select data types that meet your data storage requirements. Very large storage requirements can cause wastage of your disk space and decrease query performance.
3. Indexing Efficiency: Indexing efficiency is affected by the data types used for the composite primary key columns. Some data types are more efficient at indexing than variable-length types.
4. Consistency across Related Tables: It is crucial to ensure data type consistency, as inconsistent data types can cause compatibility issues.

Benefits of Composite Primary Keys

Here are some advantages of creating Composite Primary Keys in MySQL:

• Enhanced Data Integrity: By combining numerous columns into a composite primary key, you can achieve a better level of data integrity. This helps in preventing duplicate or incorrect data entry into the table because the composite key must have unique values.

• Improved Query Performance: Composite primary keys generate an index on the columns that have the key. This index allows for faster data search and retrieval based on the composite key’s values. Queries that use the primary key columns can be executed more efficiently, resulting in better performance.

• Accurate Representation of Relationships: In complicated database architectures, relationships between entities frequently require numerous attributes to uniquely identify rows. Composite primary keys enable you to precisely capture these relationships by merging the required columns into a single key.

• Simplified Data Model: Adopting a composite main key might sometimes reduce the number of surrogate keys. Instead of adding a new column purely for the purpose of primary keys, you can combine existing columns to create a composite key.

Common Mistakes and Troubleshooting

It’s important to avoid these mistakes and potential issues that could occur while working with MySQL composite primary keys.

1. Incorrect Column Order: The sequence of columns in composite primary keys is important. To avoid conflicts and maintain referential integrity, ensure that the column order is consistent across all connected tables.
2. Handling NULL Values: Composite primary keys frequently perform well when the key columns don’t accept NULL values. However, if you must have NULLable columns, be aware of the uniqueness of the key. Remember that NULL is treated as a distinct value, and different rows with NULLs in the key fields can coexist.
3. Consider Key Modifications: Altering a composite primary key or the columns involved in it can affect data integrity and dependencies. To ensure a smooth transition, you should carefully consider the impact on linked tables, foreign keys, and constraints before making any changes.
4. Indexing Considerations: MySQL can automatically generate an index for the composite primary key, but you should be careful of the index size and associated performance implications.

Let’s take a look at some best practices as well!

Best Practices

These best practices should be followed while working with composite primary keys.

1. Select Meaningful Columns: Choose columns that provide a meaningful and unique identifier for every row. This guaranteed that the key correctly reflects the uniqueness of the data.
2. Avoid Large Keys: Don’t create composite primary keys with excessive columns. It can lead to high storage needs and potential performance impact.
3. Validate Unique Constraints: Ensure the uniqueness of the combination of values you intend to use while creating the composite primary key. Validate the key’s uniqueness by analyzing the data or applying required constraints.
4. Consider Primary Key Constraints: Use foreign key constraints while working with table relationships. This will maintain the referential integrity.

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.

As you know more about using MySQL now, you should also understand how to make it analysis-ready. Here is where Hevo can help you.

Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources (40+ free sources), we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready.

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 Hevo Price, which will assist you in selecting the best plan for your requirements.

FAQ on Creating MySQL Composite Primary Keys

1 -- https://res.cloudinary.com/hevo/image/upload/v1725259861/hevo-blog/ebook-downloadable-papers/ebooks/Database_Replication_ulbjke.pdf --- Download Your EBook For Free - Exit Intent Popup