SQL Notes

Hub Advantages and Disadvantages of SQL Features of SQL History of SQL SQL vs NoSQL What is SQL?SQL Comments SQL Data Types SQL Keywords SQL Operators SQL Syntax CHECK Constraint in SQL Composite Keys in SQL DEFAULT Constraint in SQL FOREIGN KEY Constraint in SQL NOT NULL Constraint in SQL PRIMARY KEY Constraint in SQL SQL Constraints UNIQUE Key Constraint in SQL DELETE Data INSERT Data SELECT Data SQL Aliases UPDATE Data BETWEEN Operator DISTINCT Keyword GROUP BY Clause HAVING Clause IN Operator LIKE Operator LIMIT Clause ORDER BY Clause TOP Clause WHERE Clause Wildcard Characters EXCEPT Operator in SQL INTERSECT Operator in SQL MINUS Operator in SQL UNION Operator in SQL UNION ALL Operator in SQL CROSS JOIN in SQL FULL OUTER JOIN INNER JOIN JOIN vs UNION LEFT JOIN RIGHT JOIN SELF JOIN SQL Joins Overview ANY and ALL Operators CASE Expression in SQL Correlated Subquery EXISTS Operator Nested Subqueries in SQL Scalar Subqueries Subquery Introduction Aggregate Functions AVG Function Conversion Functions COUNT Function Date Functions MIN and MAX Functions Numeric Functions String Functions SUM Function ALTER TABLE Create Table DROP TABLE Rename Table Temporary Table TRUNCATE TABLE Backup Database Create Database Drop Database Restore Database CREATE VIEW in SQL DROP VIEW in SQL Materialized Views in SQL Update and Modify Views in SQL Create Stored Procedure in SQL Execute Stored Procedures in SQL Parameters in Stored Procedures Stored Procedure vs Function in SQL Stored Procedure Introduction AFTER Trigger BEFORE Trigger INSTEAD OF Trigger Trigger Examples Trigger Introduction ACID Properties in SQL COMMIT in SQL Isolation Levels in SQL ROLLBACK in SQL SAVEPOINT in SQL Introduction to SQL Transactions Boyce-Codd Normal Form (BCNF)Database Normalization Denormalization First Normal Form (1NF)Second Normal Form (2NF)Third Normal Form (3NF)Clustered Index in SQL Index Performance Non-Clustered Index Introduction to SQL Indexes Unique Index SQL Execution Plans EXPLAIN Plan Indexing Strategies Partitioning Query Optimization SQL Performance Tips Common Table Expressions (CTE)Dynamic SQL Functions in SQL Materialized Views MERGE Statement PIVOT and UNPIVOT RANK(), DENSE_RANK(), and ROW_NUMBER()Recursive CTE Stored Procedures Triggers in SQL Views in SQL Window Functions SQL Challenges SQL Exercises (Advanced)SQL Exercises (Beginner)SQL Exercises (Intermediate)SQL Projects SQL Interview Questions (Advanced)SQL Interview Questions (Beginner)SQL Interview Questions (Intermediate)SQL Output-Based Interview Questions SQL Scenario-Based Interview Questions Create Your First Database Install MySQL on Your Computer Install PostgreSQL Install SQLite SQL Online Compilers SQL Roadmap

Composite Keys in SQL

How to use composite keys in SQL as PRIMARY KEY or UNIQUE constraints spanning multiple columns, with syntax, use cases, and examples for junction tables and multi-column uniqueness.

Sometimes a single column simply cannot uniquely identify a row. Imagine an enrollment table — a student can enroll in multiple courses, and a course has multiple students. Neither StudentID alone nor CourseID alone is unique. But the combination of both together? That is unique. No student enrolls in the same course twice.

A composite key uses two or more columns together to uniquely identify each row in a table. It is one of the most important concepts in relational database design, especially when you work with many-to-many relationships and junction tables.

Creating a Composite Primary Key

The syntax places multiple columns inside the PRIMARY KEY parentheses:

sql example

CREATE TABLE Enrollments (
    StudentID INT,
    CourseID VARCHAR(10),
    EnrollmentDate DATE,
    Grade VARCHAR(5),
    PRIMARY KEY (StudentID, CourseID)
);

This tells the database that no two rows can have the same combination of StudentID and CourseID. Individually, these values can repeat, but together they must be unique.

Composite Key with Foreign Keys

In practice, composite primary keys almost always involve foreign keys pointing back to parent tables. This is the classic junction table pattern:

sql example

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(255)
);

CREATE TABLE Courses (
    CourseID VARCHAR(10) PRIMARY KEY,
    CourseName VARCHAR(100) NOT NULL,
    Credits INT
);

CREATE TABLE Enrollments (
    StudentID INT,
    CourseID VARCHAR(10),
    EnrollmentDate DATE DEFAULT CURRENT_DATE,
    Grade VARCHAR(5),
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

This structure enforces that every enrollment references a valid student and a valid course, while preventing duplicate enrollments.

Composite UNIQUE Constraint

Sometimes you need uniqueness on a column combination but do not want it as the primary key. Use a composite UNIQUE constraint instead:

sql example

CREATE TABLE ExamResults (
    ResultID INT PRIMARY KEY AUTO_INCREMENT,
    StudentID INT,
    ExamID INT,
    Subject VARCHAR(50),
    Score INT,
    UNIQUE (StudentID, ExamID, Subject)
);

Here, ResultID is the primary key for simple identification, but the UNIQUE constraint ensures a student cannot have duplicate scores for the same subject in the same exam.

Real-World Example: E-Commerce Order Items

An online store needs to track which products are in which orders:

sql example

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE DEFAULT CURRENT_DATE
);

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(200),
    Price DECIMAL(10,2)
);

CREATE TABLE OrderItems (
    OrderID INT,
    ProductID INT,
    Quantity INT NOT NULL DEFAULT 1,
    UnitPrice DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (OrderID, ProductID),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

The composite key (OrderID, ProductID) ensures that the same product cannot appear twice in the same order. If a customer wants two units, you increase the Quantity column instead of adding a duplicate row.

Adding a Composite Key to an Existing Table

If the table already exists:

sql example

ALTER TABLE Enrollments
ADD PRIMARY KEY (StudentID, CourseID);

For a composite UNIQUE constraint:

sql example

ALTER TABLE ExamResults
ADD CONSTRAINT uq_student_exam UNIQUE (StudentID, ExamID, Subject);

Column Order Matters

The order of columns in a composite key affects index performance. The database creates an index based on the column order you specify. Place the column you filter on most frequently first.

sql example

-- If you usually search by StudentID first:
PRIMARY KEY (StudentID, CourseID)

-- This query uses the index efficiently:
SELECT * FROM Enrollments WHERE StudentID = 101;

-- This query cannot use the index as efficiently:
SELECT * FROM Enrollments WHERE CourseID = 'CS201';

If you frequently search by CourseID alone, consider adding a separate index on that column.

Composite Key vs Surrogate Key

You have two choices when designing tables:

Approach	Composite Key	Surrogate Key
Structure	Uses natural columns	Adds artificial ID column
Example	PRIMARY KEY (StudentID, CourseID)	EnrollmentID INT AUTO_INCREMENT
Pros	No extra column, natural meaning	Simple joins, shorter foreign keys
Cons	Longer foreign keys, multi-column joins	Extra column, no inherent meaning

Many developers prefer surrogate keys for simplicity, but composite keys are the correct choice when the combination of columns has natural business meaning and you want to enforce that uniqueness at the database level.

Inserting Data with Composite Keys

sql example

-- Valid inserts
INSERT INTO Enrollments (StudentID, CourseID, EnrollmentDate, Grade)
VALUES (101, 'CS201', '2026-01-15', 'A');

INSERT INTO Enrollments (StudentID, CourseID, EnrollmentDate, Grade)
VALUES (101, 'MA101', '2026-01-15', 'B+');

-- This FAILS - duplicate composite key
INSERT INTO Enrollments (StudentID, CourseID, EnrollmentDate, Grade)
VALUES (101, 'CS201', '2026-02-01', 'B');
-- ERROR: Duplicate entry '101-CS201' for key 'PRIMARY'

Common Mistakes to Avoid

Mistake 1: Using too many columns in a composite key. Keep it to two or three columns maximum. More than that usually signals a design problem.

Mistake 2: Forgetting that NULL behaves differently. In most databases, composite keys do not allow NULL in any of their columns since they act as primary keys.

Mistake 3: Not creating additional indexes. If you have PRIMARY KEY (A, B) and frequently query by B alone, you need a separate index on B.

Mistake 4: Making foreign keys overly complex. When another table references a composite primary key, it must include all columns of that key, making relationships more complex.

Best Practices

Use composite keys for junction tables that resolve many-to-many relationships
Place the most selective column first for better index utilization
Keep composite keys short — two or three columns maximum
Consider surrogate keys when composite keys would make joins too complex
Always add foreign key constraints to maintain referential integrity
Document the business rule that the composite key enforces

Composite keys are fundamental to proper relational database design. They naturally enforce business rules like "a student cannot enroll in the same course twice" directly at the database level, making your data inherently more reliable.

Exam Focus

Revise definitions, diagrams, examples, and short-answer points for Composite Keys in SQL.

Interview Use

Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.

Search Terms

sql-complete-guide, sql complete guide, sql, complete, guide, constraints, composite, key

Continue learning this concept

ConstraintsFOREIGN KEY Constraint in SQLHow to use FOREIGN KEY constraints to enforce referential integrity between SQL tables, with examples of ON DELETE and ON UPDATE actions, and real-world relational patterns.ConstraintsPRIMARY KEY Constraint in SQLHow to use PRIMARY KEY constraints in SQL to uniquely identify each row in a table, its rules, real-world uses, and how it combines NOT NULL and UNIQUE behavior.ConstraintsUNIQUE Key Constraint in SQLHow to use UNIQUE constraints in SQL to prevent duplicate values in columns, understand the difference between UNIQUE and PRIMARY KEY, and implement data integrity rules.ConstraintsCHECK Constraint in SQLHow to use the CHECK constraint in SQL to validate column values against custom conditions, enforce business rules, and maintain data correctness with practical examples and detailed rules.ConstraintsDEFAULT Constraint in SQLHow to use the DEFAULT constraint in SQL to provide automatic fallback values when inserting data, keep columns populated, and reduce boilerplate INSERT statements with practical examples.ConstraintsNOT NULL Constraint in SQLHow to use the NOT NULL constraint in SQL to require values in table columns, prevent incomplete records, and enforce mandatory fields with practical examples and real-world scenarios.

PreviousCHECK Constraint in SQL NextDEFAULT Constraint in SQL

SQL Notes

Composite Keys in SQL

How to use composite keys in SQL as PRIMARY KEY or UNIQUE constraints spanning multiple columns, with syntax, use cases, and examples for junction tables and multi-column uniqueness.

Creating a Composite Primary Key

The syntax places multiple columns inside the PRIMARY KEY parentheses:

sql example

CREATE TABLE Enrollments (
    StudentID INT,
    CourseID VARCHAR(10),
    EnrollmentDate DATE,
    Grade VARCHAR(5),
    PRIMARY KEY (StudentID, CourseID)
);

This tells the database that no two rows can have the same combination of StudentID and CourseID. Individually, these values can repeat, but together they must be unique.

Composite Key with Foreign Keys

In practice, composite primary keys almost always involve foreign keys pointing back to parent tables. This is the classic junction table pattern:

sql example

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Email VARCHAR(255)
);

CREATE TABLE Courses (
    CourseID VARCHAR(10) PRIMARY KEY,
    CourseName VARCHAR(100) NOT NULL,
    Credits INT
);

CREATE TABLE Enrollments (
    StudentID INT,
    CourseID VARCHAR(10),
    EnrollmentDate DATE DEFAULT CURRENT_DATE,
    Grade VARCHAR(5),
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

This structure enforces that every enrollment references a valid student and a valid course, while preventing duplicate enrollments.

Composite UNIQUE Constraint

Sometimes you need uniqueness on a column combination but do not want it as the primary key. Use a composite UNIQUE constraint instead:

sql example

CREATE TABLE ExamResults (
    ResultID INT PRIMARY KEY AUTO_INCREMENT,
    StudentID INT,
    ExamID INT,
    Subject VARCHAR(50),
    Score INT,
    UNIQUE (StudentID, ExamID, Subject)
);

Here, ResultID is the primary key for simple identification, but the UNIQUE constraint ensures a student cannot have duplicate scores for the same subject in the same exam.

Real-World Example: E-Commerce Order Items

An online store needs to track which products are in which orders:

sql example

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE DEFAULT CURRENT_DATE
);

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(200),
    Price DECIMAL(10,2)
);

CREATE TABLE OrderItems (
    OrderID INT,
    ProductID INT,
    Quantity INT NOT NULL DEFAULT 1,
    UnitPrice DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (OrderID, ProductID),
    FOREIGN KEY (OrderID) REFERENCES Orders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

Adding a Composite Key to an Existing Table

If the table already exists:

sql example

ALTER TABLE Enrollments
ADD PRIMARY KEY (StudentID, CourseID);

For a composite UNIQUE constraint:

sql example

ALTER TABLE ExamResults
ADD CONSTRAINT uq_student_exam UNIQUE (StudentID, ExamID, Subject);

Column Order Matters

The order of columns in a composite key affects index performance. The database creates an index based on the column order you specify. Place the column you filter on most frequently first.

sql example

-- If you usually search by StudentID first:
PRIMARY KEY (StudentID, CourseID)

-- This query uses the index efficiently:
SELECT * FROM Enrollments WHERE StudentID = 101;

-- This query cannot use the index as efficiently:
SELECT * FROM Enrollments WHERE CourseID = 'CS201';

If you frequently search by CourseID alone, consider adding a separate index on that column.

Composite Key vs Surrogate Key

You have two choices when designing tables:

Approach	Composite Key	Surrogate Key
Structure	Uses natural columns	Adds artificial ID column
Example	PRIMARY KEY (StudentID, CourseID)	EnrollmentID INT AUTO_INCREMENT
Pros	No extra column, natural meaning	Simple joins, shorter foreign keys
Cons	Longer foreign keys, multi-column joins	Extra column, no inherent meaning

Inserting Data with Composite Keys

sql example

-- Valid inserts
INSERT INTO Enrollments (StudentID, CourseID, EnrollmentDate, Grade)
VALUES (101, 'CS201', '2026-01-15', 'A');

INSERT INTO Enrollments (StudentID, CourseID, EnrollmentDate, Grade)
VALUES (101, 'MA101', '2026-01-15', 'B+');

-- This FAILS - duplicate composite key
INSERT INTO Enrollments (StudentID, CourseID, EnrollmentDate, Grade)
VALUES (101, 'CS201', '2026-02-01', 'B');
-- ERROR: Duplicate entry '101-CS201' for key 'PRIMARY'

Common Mistakes to Avoid

Mistake 1: Using too many columns in a composite key. Keep it to two or three columns maximum. More than that usually signals a design problem.

Mistake 2: Forgetting that NULL behaves differently. In most databases, composite keys do not allow NULL in any of their columns since they act as primary keys.

Mistake 3: Not creating additional indexes. If you have PRIMARY KEY (A, B) and frequently query by B alone, you need a separate index on B.

Mistake 4: Making foreign keys overly complex. When another table references a composite primary key, it must include all columns of that key, making relationships more complex.

Best Practices

Use composite keys for junction tables that resolve many-to-many relationships
Place the most selective column first for better index utilization
Keep composite keys short — two or three columns maximum
Consider surrogate keys when composite keys would make joins too complex
Always add foreign key constraints to maintain referential integrity
Document the business rule that the composite key enforces

Exam Focus

Revise definitions, diagrams, examples, and short-answer points for Composite Keys in SQL.

Interview Use

Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.

Search Terms

sql-complete-guide, sql complete guide, sql, complete, guide, constraints, composite, key

Continue learning this concept

PreviousCHECK Constraint in SQL NextDEFAULT Constraint in SQL