Through this blog, we will learn about composite keys in SQL. In this comprehensive guide, we can easily discover what is composite key in SQL and when to use it in databases. Learn the step-by-step process of creating composite keys and look into the implementation across various SQL platforms like MySQL, SQL Server, and PostgreSQL. Also, learn how to alter or remove a composite key in SQL.
Table of Contents
Watch this video to learn SQL in detail
What is a Composite Key in SQL?
A composite key in SQL is a combination of multiple columns in a table that is used to uniquely identify each record in the table. It is created by combining multiple fields to form a unique identifier for a specific row. Composite keys are essential for complex data structures and relationships in a relational database, ensuring that no two records share the same combination of values in the specified columns. It also acts as a primary key that is created by using multiple columns.
Syntax of creating a composite key:
By combining 3 columns: COL1, COL2, COL3
CONSTRAINT COMPOSITE_KEY_NAME PRIMARY KEY (COL1, COL2, COL3)
- COMPOSITE_KEY_NAME: This is the name of the new composite key created by combining two or more columns.
Note: In the latest version of SQL, we can combine up to 16 columns with the help of a composite key. The datatype of all the columns can be the same or different.
When Do We Use a Composite Key in SQL?
Suppose you are working on a dataset and want to uniquely find the columns, but there might be scenarios where you want to identify more than one column or a group of columns uniquely.
Imagine yourself as a manager, and you have a database of all the employees of a company. You want to search for a particular employee from a particular department. If you are searching for any particular name, there might be a probability that more than one employee can be there with that same name. For that, we think of another unique column, in the employee database we can easily identify a person by employee id as no two people can have the same employee id. To solve this problem, we can create a composite key that identifies the employee id, employee name, and department together as uniquely identifying records from the database.
How Do We Create a Composite Key?
To know how to create a composite key in SQL, firstly we need to understand the syntax to create a table using a composite key, and with the help of an example, we will create a composite key that uniquely identifies a group of columns.
Syntax to create a composite key for a table in SQL:
Create table table_name (
COL1 data_type_1 NOT NULL,
COL2 data_type_2 NOT NULL,
COL3 data_type_3,
COL4 data_type_4,
COLN data_type_n
CONSTRAINT COMP_NAME PRIMARY KEY (COL1, COL3, COL4)
);
Declaration: Here, COL1, COL3, and COL4 represent the combining columns.
As we have already understood the syntax of creating a composite key. Now, we will create a table using the above syntax.
For instance, we create an employee table, insert employee information, and combine two columns.
SQL Query to create a table using a composite key
Step 1: Create a table
CREATE TABLE Employee(
Emp_ID int,
Emp_Name VARCHAR(20),
Emp_Age int,
Designation VARCHAR(20),
Step 2: Declare the Composite Key
CONSTRAINT My_Composite_Key PRIMARY KEY (Emp_Id, Emp_Name)
);
Step 3: Insert into the table
INSERT INTO Employee VALUES (1001, 'Ram Prasad', 45, 'Manager');
INSERT INTO Employee VALUES (2001, 'Saiyyad Sheikh', 32, 'Intern');
INSERT INTO Employee VALUES (3001, 'Himanshu Singh', 22, 'HOD');
INSERT INTO Employee VALUES (4001, 'Rahul Mehta', 25, 'Executive');
Step 4: Execute the Query
Select * from EMPLOYEE
Output: Here we have used an online compiler
We have different visions of SQL, such as Mysql, SQL Server, and Postgresql. We will understand how to create a composite key on these platforms with the help of examples of each.
My SQL
Syntax
Create table table_name (
COL1 data_type_1,
COL2 data_type_2,
COL3 data_type_3,
COLN data_type_n,
PRIMARY KEY (COL1, COL)
);
Example
Create table STUDENT
(
S_ID integer,
S_Age integer,
S_Phone integer,
PRIMARY KEY (S_ID, S_Phone)
);
SQL Server
Syntax
Create table table_name (
COL1 data_type_1,
COL2 data_type_2,
COL3 data_type_3,
COLN data_type_n,
PRIMARY KEY (COL1, COL)
);
Example
Create table STUDENT
(
S_ID integer,
S_Age integer,
S_Phone integer,
PRIMARY KEY (S_ID, S_Phone)
);
Postgresql
Syntax
Create table table_name (
COL1 data_type_1,
COL2 data_type_2,
COL3 data_type_3,
COLN data_type_n,
PRIMARY KEY (COL1, COL)
);
Example
Create table STUDENT
(
S_ID integer,
S_Age integer,
S_Phone integer,
PRIMARY KEY (S_ID, S_Phone)
);
How Do You Alter and Delete a Composite Key in SQL?
Like any other column in a table, composite keys can also be operated with the alter commands. You can easily perform add or drop operations in a table, using the alter command with the composite key in SQL.
ALTER-ADD command: This command adds the columns to the existing set of columns, forming a composite key.
Syntax
ALTER table table_name
ADD CONSTRAINT Constraint_name PRIMARY KEY (COL1, COL2, COLN);
Example
ALTER table STUDENT
ADD CONSTRAINT MyCompKey PRIMARY KEY ( Name, Age );
ALTER-DROP command: To delete a column from the existing set of columns forming the composite key.
Syntax
ALTER table table_name
DROP CONSTRAINT Constraint_Name;
Example
ALTER table STUDENT
DROP CONSTRAINT MyCompKey;
Conclusion
The concept of composite keys in SQL involves combining multiple columns to uniquely identify records in a table. They are useful for scenarios where a single column is not sufficient for unique identification. Syntax and examples across platforms like MySQL, SQL Server, and PostgreSQL demonstrate the creation of a composite key. Additionally, altering composite keys via ADD and DROP operations allows for flexibility in managing table schemas.
FAQs
What is a composite key in SQL?
A composite key in SQL is a combination of two or more columns used to uniquely identify records in a database table. Unlike a single-column primary key, a composite key consists of multiple columns that, when combined, provide unique identification.
When should I use a composite key?
Composite keys are beneficial when a single column cannot guarantee the unique identification of records. They are used in scenarios where a combination of columns is required to ensure identities in a table, such as in many-to-many relationships or when no single attribute can uniquely identify a record.
Can all columns in a composite key be NULL?
No, in a composite key, at least one column should have a non-null constraint. A composite key uniquely identifies rows, and having null values in any part of it would affect its purpose.
How many columns can be combined in a composite key?
Most SQL databases allow combining multiple columns, generally up to a limit (often around 16 columns). However, it is advisable to use composite keys judiciously and only combine columns necessary for unique identification.
Can a composite key be altered or dropped once it is created?
Yes, composite keys can be altered or dropped using SQL’s ALTER TABLE command. Columns can be added or removed from the composite key, providing flexibility in managing table schema without affecting the data.