All About Composite Keys in SQL
In this article, we will learn about the composite keys in sql, and how to implement them in sql with the help of examples.
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: Free Database and SQL Online Course and Certificates
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));
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));
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));
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.
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));
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));
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.
Must Read: SQL Logical Operator
Best-suited Database and SQL courses for you
Learn Database and SQL with these high-rated online courses
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.
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
Top Picks & New Arrivals
