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?

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