What is the Composite Primary Key?

We can break the term "composite primary key" into two components $composite\ +\ primary \ key$.

• Composite:
  means a combination of multiple components.
• primary key:
  means a key that can uniquely identify the rows in a table. Also, the key cannot be null.

Therefore by combining the above definitions, we can say that a composite primary key is a primary key formed by combining one or more keys (columns) from the table. These keys may or may not guarantee to the identification of the rows of the table individually. When combined, they become a primary key and can uniquely identify a row.

How to Create Composite Primary Key?

Transform Your Career

Choose from our industry-leading programs designed for career success

NSDC Certified

Modern Software and AI Engineering Program

Master full-stack development with AI integration

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

Modern Data Science and ML with specialisation in AI

Advanced data science techniques with AI specialization

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

Advanced AIML with Specialisation in Agentic AI

Deep dive into AIML with focus on Agentic systems

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

DevOps, Cloud & AI Platform Engineering

Build and manage AI-powered cloud infrastructure

12 MonthsDuration

AI-LedCurriculum

Career SupportSupport

+1000 more

Go to Program

NSDC Certified

AI Engineering Advanced Certification by IIT-Roorkee

Premier AI engineering certification from IIT-Roorkee

3 MonthsDuration

AI-LedCurriculum

Career SupportSupport

Go to Program

Creating Composite Primary Key While Table Creation

Syntax to create a composite key in SQL:

As shown in the syntax, we need to determine at least two columns that don't accept NULL values for defining a composite key. The remaining columns of the table then follow. At last, we can write a constraint to define the composite primary key.

Example:

Creating a database:

Using database:

Creating a table with a composite key:

In the example given below, we have made the composite key the combination of two columns i.e. mobile number and username because all the rows of the table student can be uniquely identified by this composite key.

Inserting records in the table:

Querying the records:

Output :

Adding Composite Primary Key in Existing Table

To add a composite primary key in the existing table we can use the following syntax:

If a primary key already exists we can use the following syntax:

In the above example, we can change the composite primary key as a combination of $mail\_id$ and $password$ as:

Querying the records:

Output:

Composite Primary Key Rules

The following rules should be kept in mind while creating a composite primary key:

• The combination can make a composite primary key of two or more candidate keys.
• A composite key cannot be null.
• A composite Key cannot contain duplicates.
• We can have duplicate values in an individual column, but they must be unique across the columns.
• Null values are not allowed in any columns in the composite primary key.

Learn More

To learn more about primary keys and other relevant articles on Scaler Topics

• Primary Key in SQL