SQL Composite Key

Introduction

In this chapter, we will learn about the COMPOSITE KEY in SQL. A composite key is a primary key that consists of two or more columns. This chapter will cover the syntax, usage, and provide examples to help you understand how to use composite keys effectively.

What is a Composite Key?

A composite key is a combination of two or more columns in a table that uniquely identifies each row in that table. Composite keys are useful when a single column is not sufficient to uniquely identify a row.

Syntax for Composite Key

Basic Syntax

When creating a table, you can define a composite key using the following syntax:

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

Step-by-Step Example

1. Create a Sample Table with a Composite Key

First, we will create a sample table named project_assignments with a composite key.

CREATE TABLE project_assignments (
    project_id INT,
    employee_id INT,
    assigned_date DATE,
    PRIMARY KEY (project_id, employee_id)
);

2. Insert Sample Data

INSERT INTO project_assignments (project_id, employee_id, assigned_date)
VALUES
(1, 101, '2023-01-15'),
(1, 102, '2023-01-16'),
(2, 101, '2023-02-01'),
(3, 103, '2023-03-12');

3. Verify the Composite Key

To verify that the combination of project_id and employee_id is a composite key, we can try to insert a duplicate value:

INSERT INTO project_assignments (project_id, employee_id, assigned_date)
VALUES (1, 101, '2023-04-01');

This command will result in an error because the combination of project_id 1 and employee_id 101 already exists in the table, demonstrating that the composite key enforces unique combinations.

Using Composite Key with Foreign Key

Composite keys can also be used in conjunction with foreign keys to establish relationships between tables.

Example

Assume we have two tables named projects and employees, and we want to create a project_assignments table with a composite key that references both projects and employees.

CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    project_name VARCHAR(100)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50)
);

CREATE TABLE project_assignments (
    project_id INT,
    employee_id INT,
    assigned_date DATE,
    PRIMARY KEY (project_id, employee_id),
    FOREIGN KEY (project_id) REFERENCES projects(project_id),
    FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

Insert Sample Data into projects and employees Tables

INSERT INTO projects (project_id, project_name)
VALUES
(1, 'Project A'),
(2, 'Project B'),
(3, 'Project C');

INSERT INTO employees (employee_id, first_name, last_name)
VALUES
(101, 'Ramesh', 'Kumar'),
(102, 'Sita', 'Patel'),
(103, 'Arjun', 'Singh');

Insert Sample Data into project_assignments Table

INSERT INTO project_assignments (project_id, employee_id, assigned_date)
VALUES
(1, 101, '2023-01-15'),
(1, 102, '2023-01-16'),
(2, 101, '2023-02-01'),
(3, 103, '2023-03-12');

Verify the Foreign Key Constraint with Composite Key

To verify the foreign key constraint, try to insert a record into the project_assignments table with a project_id or employee_id that does not exist in the projects or employees table:

INSERT INTO project_assignments (project_id, employee_id, assigned_date)
VALUES (4, 104, '2023-04-01');

This command will result in an error because the project_id 4 and employee_id 104 do not exist in the projects and employees tables, respectively, demonstrating that the foreign key constraint enforces referential integrity.

Conclusion

The COMPOSITE KEY is used for uniquely identifying rows in a table using a combination of columns. This chapter covered the basic syntax for defining composite keys, inserting data while maintaining unique combinations, and using composite keys with foreign keys to establish relationships between tables. Understanding how to define and use composite keys effectively will enhance your ability to design robust and reliable database schemas.