Open In App

MySQL COMPOSITE KEY

Last Updated : 22 Jan, 2024
Improve
Improve
Like Article
Like
Save
Share
Report

In MySQL, a composite key is a combination of two or more columns in a table that uniquely identifies each entry. It is a candidate key made up of many columns. MySQL guarantees column uniqueness only when they are concatenated. If they are extracted separately, the uniqueness cannot be maintained.

Any key, such as the primary key, super key, or candidate key, can be referred to as a composite key if it has more than one characteristic. A composite key is important when the database has to uniquely identify each record that contains more than one characteristic. A composite key column may include a variety of data types. Thus, the columns do not have to have the same data type to form a composite key in MySQL.

A composite key can be added in two ways:

  • Using CREATE Statement
  • Using ALTER Statement

Let us see both ways in detail.

Composite Key Using CREATE Statement

Here, we will look at how composite keys operate in MySQL. Let us first construct a table called “employees” using the following statement:

CREATE TABLE employee (  
employee_id INT,
department_id INT,
employee_name VARCHAR(255),
PRIMARY KEY (employee_id, department_id)
);

In this example, the employee table is created with three columns: employee_id, department_id, and employee_name. The PRIMARY KEY constraint is applied to the combination of employee_id and department_id. This means that each pair of employee_id and department_id values must be unique within the table.

We can verify the same using the command as below:

DESCRIBE employee;  

After the successful execution, It means we have successfully added the composite primary key on employee_id and department_id columns.

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

INSERT INTO employee (employee_id, department_id, employee_name) VALUES
(101, 1, 'John Doe'),
(102, 2, 'Jane Smith'),
(103, 1, 'Bob Johnson'),
(104, 3, 'Alice Brown');

Next, execute the below command to show the table data:

SELECT * FROM employee;  

Output:

OutPut

Output

Composite Key Using ALTER Statement

In some cases, you might need to add a composite key to an existing table. MySQL allows you to achieve this using the ALTER TABLE statement. Let’s consider a scenario where we want to add a composite key to an existing orders table based on the columns order_id and product_id.

Assuming the initial structure of the orders table is as follows:

— Initial table structure without a composite key

CREATE TABLE orders (
order_id INT,
product_id INT,
quantity INT
);

Now, let’s use the ALTER TABLE statement to add a composite key:

— Add a composite key to the existing table

ALTER TABLE orders
ADD PRIMARY KEY (order_id, product_id);

In this example, the ALTER TABLE statement is used to modify the orders table. The ADD PRIMARY KEY clause is then used to define the composite key based on the columns order_id and product_id.

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

INSERT INTO orders (order_id, product_id, quanitity) VALUES
(101, 1, 10),
(102, 2, 12),
(103, 1, 13),
(104, 3, 14);

Next, execute the below command to show the table data:

SELECT * FROM orders;  

Output:

output

Output

Conclusion

In the realm of relational databases, the proper utilization of keys is crucial for maintaining data integrity and ensuring efficient data retrieval. MySQL offers a robust solution in the form of composite keys, allowing us to create unique identifiers based on combinations of multiple columns. Through this article, we explored the creation of composite keys using both the CREATE TABLE and ALTER TABLE statements.


Previous Article
Next Article

Similar Reads

How to Fix MySQL Error Key Specification Without a Key Length
When working with MySQL databases we may encounter the error message "key specification without a key length" This error typically occurs when defining the indexes on the columns without specifying a length for the index. In this article, we will explore what this error means why it occurs, and how to fix it to ensure smooth database operations. Pu
3 min read
MySQL FOREIGN KEY Constraint
FOREIGN KEY is a field in a table that refers to a unique key in another table. It is used for linking one or more than one table together. Another name for FOREIGN KEY is referencing key. It creates a link (relation) between two tables thus creating referential integrity. The FOREIGN KEY creates a relationship between the columns in the current ta
6 min read
MySQL Primary Key
MySQL is an open-source relational database management system that uses Structured Query Language (SQL) to manipulate databases. It stores data in a table format and to uniquely identify each record in a table, we require a Primary Key. In this article, we will learn how to add, modify, and remove the primary key in MySQL tables with examples. Prim
4 min read
How to Reset Primary Key Sequence in MySQL?
In MySQL, the primary key maintains data integrity and uniqueness. However, there are instances where the primary key sequence might fall out of sync, leading to potential issues. In this article, we'll understand the process of resetting the primary key sequence in MySQL databases with different examples. How to Reset Primary Key Sequence in MySQL
4 min read
How to Temporarily Disable a Foreign Key Constraint in MySQL?
MySQL is a popular open-source relational database management system (RDBMS) that is uniquely used to construct expandable and high-productivity databases. MySQL, which was created by MySQL AB and later acquired by its current owner Oracle Corporation, was originally introduced in 1995. MySQL is reputed for its sturdy and quick functioning attribut
4 min read
How to Merge Child Object Value to Parent Key Based on Key Name in JavaScript ?
JavaScript allows us to merge values from child objects into their parent object based on a specific key name. This can be useful for organizing data or simplifying complex structures. There are several approaches available in JavaScript to merge child object values to parent key based on the key name which are as follows. Table of Content Using fo
3 min read
Composite Design Pattern | JavaScript Design Patterns
The Composite Design pattern is a way of organizing objects. It helps us handle different objects in a similar way when they are put together to create a structure with parts and wholes. These parts and wholes are like building blocks that can be split into smaller pieces and then put together to make a tree-like structure. The composite design pat
6 min read
Longest Increasing Subarray of Composite Numbers in Array
Given an array of integers arr[], the task is to find the longest increasing subarray of composite numbers in an array. Examples: Input: arr[] = [1, 4, 7, 6, 8, 10, 12]Output: [6, 8, 10, 12]Explanation: The longest increasing subarray of composite numbers in the given array is [6, 8, 10, 12], which consists of the composite numbers 6, 8, 10, and 12
12 min read
Composite Method | Software Design Pattern
Composite Pattern is a structural design pattern that allows you to compose objects into tree structures to represent part-whole hierarchies. The main idea behind the Composite Pattern is to build a tree structure of objects, where individual objects and composite objects share a common interface. This common interface allows clients to interact wi
9 min read
Nasdaq Composite Index : Measure & Performance
What is Nasdaq Composite Index?The Nasdaq Composite Index combines almost all the stocks listed on the NASDAQ Stock Exchange and depicts the performance of the stock market as a whole. This index is based on the market capitalization of all the stocks listed on the exchange. This market cap-weighted index comprises more than 3000 stocks that are li
8 min read