What is a Composite Key?

A Composite Key, in SQL, 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 leverages the synergy of multiple columns to create a distinctive identifier for a record.

Why Choose Composite Keys?

Enhanced Search Capabilities

Consider this scenario: you're managing employee data, and you want to find all records for an employee named Rahul. With a single column, like the employee name, you might encounter multiple entries with the same name. By combining columns, such as employee name and employee number, you create a composite key that streamlines your search, making it more efficient.

Syntax to Create a Composite Key

CREATE TABLE Employee_Detail (
    EmployeeID INT NOT NULL,
    EmployeeName VARCHAR(50) NOT NULL,
    Department VARCHAR(50),
    CONSTRAINT pk_composite_key PRIMARY KEY (EmployeeID, EmployeeName)
);

This SQL code creates a table named Employee_Detail with the following columns:

• EmployeeID (Data Type: INT):

This column represents the unique identifier for each employee. It is defined as an integer (INT) and is marked as "NOT NULL," meaning that every record must have a value for the EmployeeID.

• EmployeeName (Data Type: VARCHAR(50)):

This column stores the name of the employee. It is defined as a variable character string (VARCHAR) with a maximum length of 50 characters. Similar to EmployeeID, it is marked as "NOT NULL."

• Department (Data Type: VARCHAR(50)):

This column represents the department in which the employee works. It is defined as a variable character string (VARCHAR) with a maximum length of 50 characters. Unlike the previous columns, it is not marked as "NOT NULL," allowing records to have a null value for the department.

• CONSTRAINT pk_composite_key PRIMARY KEY (EmployeeID, EmployeeName):

This line establishes a composite primary key for the table. The primary key is named pk_composite_key and is formed by combining the EmployeeID and EmployeeName columns. The primary key ensures that each combination of EmployeeID and EmployeeName is unique in the table, preventing duplicate entries.

Altering and Dropping Composite Keys

In SQL, you can modify composite keys using the ALTER command. To add columns, use ALTER-ADD, and to delete columns, use ALTER-DROP.

Syntax for Altering Composite Keys

Adding columns to an existing composite key

ALTER TABLE TableName
ADD CONSTRAINT NewConstraint PRIMARY KEY (Column1, Column2, ColumnN);

• ALTER TABLE TableName:

This part specifies the name of the table that you want to alter. Replace TableName with the actual name of the table you are modifying.

• ADD CONSTRAINT NewConstraint:

This part adds a new constraint to the table. A constraint is a rule that is enforced to maintain the integrity of the data in the table. In this case, a new constraint is being added, and it is named NewConstraint. You can replace NewConstraint with a more descriptive name that reflects the purpose of the constraint.

• PRIMARY KEY (Column1, Column2, ColumnN):

This part defines the type of constraint being added. It specifies that a primary key constraint is being added to the table. The primary key uniquely identifies each record in the table. The columns Column1, Column2, and ColumnN are the columns that together form the composite primary key. This means that the combination of values in these columns must be unique for each record in the table.

In summary, this SQL code alters an existing table by adding a new composite primary key constraint named NewConstraint. The primary key is formed by combining the specified columns (Column1, Column2, and ColumnN), ensuring unique identification for each record in the table.

Deleting a column from an existing composite key

ALTER TABLE TableName
DROP CONSTRAINT ExistingConstraint;

This SQL code is an "ALTER TABLE" statement, which is used to modify an existing table. Here's a breakdown of each part of the code:

• ALTER TABLE TableName:

This part specifies the name of the table that you want to alter. Replace TableName with the actual name of the table you are modifying.

• DROP CONSTRAINT ExistingConstraint:

This part instructs the database to drop or remove a specific constraint from the table. In this case, the constraint being dropped is named ExistingConstraint. You should replace ExistingConstraint with the actual name of the constraint you want to remove.

In summary, this SQL code alters an existing table by dropping a constraint named ExistingConstraint. The DROP CONSTRAINT statement removes the specified constraint from the table, allowing you to modify the table's structure or add a different constraint if needed.