SQL - Composite Key

In SQL, a Composite Key, also known as a Composite Primary Key, is a combination of two or more columns that uniquely identifies each row in a table. Unlike a single-column primary key, a composite key uses multiple columns to define uniqueness. Together, these columns form a composite key.

A composite key is used when a single column cannot uniquely identify a row in a table. By combining multiple columns into a composite key, you can ensure the uniqueness of a record based on the combination of values in those columns.

Syntax:

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    ...
    PRIMARY KEY (column1, column2, ...)
);

In this syntax, table_name is the name of the table, and column1, column2, and so on are the columns that form the composite key.

Example:

Let's consider a table called Employees with the following columns:

CREATE TABLE Employees (
    EmployeeID INT,
    DepartmentID INT,
    Name VARCHAR(50),
    PRIMARY KEY (EmployeeID, DepartmentID)
);

In this example, the Employees table has a composite key consisting of two columns: EmployeeID and DepartmentID. The combination of these two columns ensures the uniqueness of each row in the table.

By defining a composite key, we enforce the following rules:

• Each row must have a unique combination of EmployeeID and DepartmentID values.
• The EmployeeID and DepartmentID values can be duplicated individually, but their combination must be unique.

The composite key allows you to uniquely identify employees based on both their ID and the department they belong to. This is useful when you have scenarios where multiple employees can have the same ID but work in different departments.

By using a composite key, you can perform operations such as:

• Ensuring data integrity: The composite key constraint prevents duplicate combinations of EmployeeID and DepartmentID values.
• Establishing relationships: The composite key can be used as a reference in other tables to establish relationships, such as foreign key constraints.
• Data retrieval: You can efficiently retrieve records based on the composite key, allowing for quick and accurate data retrieval.

It's important to note that when referencing a table with a composite key in foreign key constraints, you must ensure that the referencing columns match the composite key's column order and data types.

Composite keys are particularly useful in scenarios where a combination of columns is required to uniquely identify a row. They provide flexibility in defining uniqueness based on specific combinations of values.