All About Composite Keys in SQL

All About Composite Keys in SQL

4 mins read364 Views Comment
clickHere
Updated on Feb 17, 2023 11:56 IST

In this article, we will learn about the composite keys in sql, and how to implement them in sql with the help of examples.

2023_02_MicrosoftTeams-image-175.jpg

A composite key in SQL is a combination of two or more columns that is used to identify a row in a table uniquely. It is an alternative to using a single-column primary key, and it is often used when a single column is not sufficient to identify a row uniquely.

Composite keys are created using the PRIMARY KEY constraint in the CREATE TABLE statement. They are used to ensure the integrity of the data in a database by ensuring that no two rows have the same combination of values in the key columns.

Must Check: SQL Tutorial

Here’s an example of how you might create a composite key in SQL table:

 
CREATE TABLE my_table (
col_1 INT NOT NULL,
col_2 VARCHAR(255) NOT NULL,
col_3 VARCHAR(255),
PRIMARY KEY (col_1, col_2)
);
Copy code

This creates a table with a composite key consisting of the columns col_1 and col_2. The values in these two columns must be unique across all rows in the table.

You can also specify that a composite key should be used as a foreign key in another table. For example:

 
CREATE TABLE other_table (
col_1 INT NOT NULL,
col_2 VARCHAR(255) NOT NULL,
FOREIGN KEY (col_1, col_2) REFERENCES my_table(col_1, col_2)
);
Copy code

This creates a foreign key in the other_table that references the composite key in the my_table.

Let’s understand this clearly with an example. 
Imagine you have a table that stores information about different people, and you want to make sure that no two people have the same first and last name. In this case, you can create a composite key that consists of the first_name and last_name columns.

You can also explore: All About DML Commands in SQL

Implementing Composite Key in SQL

Here’s an example of how you might create a composite key in SQL:

 
CREATE TABLE people (
id INT NOT NULL AUTO_INCREMENT,
first_name VARCHAR(255) NOT NULL,
last_name VARCHAR(255) NOT NULL,
PRIMARY KEY (first_name, last_name)
);
Copy code

This creates a table with a composite primary key consisting of the first_name and last_name columns. The values in these two columns must be unique across all rows in the table.

SQL Joins: Inner, Left, Right, and Full Inner Joins in SQL
SQL Left Join SQL Right Join

Composite keys can be used to ensure that no two rows in a table have the same combination of values in certain columns. They can also be used as foreign keys in other tables, which helps to ensure that the relationships between different tables are maintained.

Still confused? Let’s consider one more scenario to understand it better.

You have a table that stores the information about employees and you want to ensure that no two employees have the same name and date of birth. In this case, you can create a composite key that consists of the name and d o b columns.

Must Read: Types of Keys in Database

To ensure that the data in these two tables is consistent, we can create a composite key in the “departments” table that consists of the “name” and “id” columns. This composite key is called the “primary key” because it is the main way of identifying each row in the table.

To create a composite key in SQL, you can use the PRIMARY KEY constraint in the CREATE TABLE statement. Here’s an example:

 
CREATE TABLE employees (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
dob DATE NOT NULL,
PRIMARY KEY (name, dob)
);
Copy code

Must Read: SQL Logical Operator

This creates a table with a composite primary key consisting of the name and dob columns. The values in these two columns must be unique across all rows in the table.

You can also use a composite key as a foreign key in another table. A foreign key is a field in a table that references the primary key of another table. For example:

In the “employees” table, we can use the “department_name” and “department_id” columns as a “foreign key”. A foreign key is a column in one table that references the primary key of another table.

 
CREATE TABLE departments (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
PRIMARY KEY (id)
);
CREATE TABLE employees (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
dob DATE NOT NULL,
department_name VARCHAR(255) NOT NULL,
department_id INT NOT NULL,
PRIMARY KEY (id),
FOREIGN KEY (department_name, department_id) REFERENCES departments(name, id)
);
Copy code

In this example, the  “employees” table has a foreign key that consists of the “department_name” and “department_id” columns. This foreign key references the composite primary key in the departments table, which consists of the name and id columns.

Primary Keys in SQL Foreign Keys in SQL
Primary Key vs Foreign Key Primary Key vs Unique Key

Must Read: SQL Logical Operator

Conclusion

In this article, we have discussed about composite keys in sql with the help of examples, hope you will like the article.

Keep Learning!!

Keep Sharing!!

Contributed By: Prerna Singh

Must Read: All about DML Command in SQL

Must Read: SQL ACID Properties

FAQs

What is a composite key?

A composite key is a primary key made up of two or more columns. It is used to uniquely identify a row in a table, just like a primary key, but it can use multiple columns rather than just one.

When should I use a composite key?

You should use a composite key when you have a table where the combination of multiple columns is unique and you want to use this combination as the primary key for the table. For example, you might use a composite key if you have a table of orders and you want to use both the customer ID and the order ID to uniquely identify each order.

Can a composite key be null?

No, a composite key cannot be null. All columns in a composite key must have a non-null value in order for the key to be valid.

Can a composite key be used as a foreign key?

Yes, a composite key can be used as a foreign key. A foreign key is a column or set of columns in one table that refers to the primary key of another table. You can use a composite key as a foreign key in the same way you would use a single-column primary key.

About the Author

This is a collection of insightful articles from domain experts in the fields of Cloud Computing, DevOps, AWS, Data Science, Machine Learning, AI, and Natural Language Processing. The range of topics caters to upski... Read Full Bio