SQL Keys

In a relational database, keys are columns (or combinations of columns) used to uniquely identify records, establish relationships between tables, and enforce data integrity. Keys are fundamental to how relational databases are designed and how data stays consistent and connected across multiple tables.

Why Keys Are Important

• They uniquely identify each row in a table.
• They link tables to each other, allowing data to be split across multiple tables without losing the connection.
• They prevent duplicate records and orphaned data.
• They improve query performance because indexed key columns are searched faster.

Types of Keys in SQL

1. Primary Key

A primary key is the main identifier for each row in a table. It enforces two rules: the value must be unique (no two rows can have the same primary key) and it must be NOT NULL (it can never be empty). Each table can have only one primary key.

CREATE TABLE Students (
    StudentID   INT AUTO_INCREMENT PRIMARY KEY,
    StudentName VARCHAR(50) NOT NULL,
    Email       VARCHAR(100)
);

StudentID is the primary key. It uniquely identifies every student. No two students will ever have the same StudentID.

2. Foreign Key

A foreign key is a column in one table that refers to the primary key in another table. It creates a link between two tables and enforces referential integrity — ensuring that a referenced record must actually exist.

CREATE TABLE Courses (
    CourseID   INT PRIMARY KEY,
    CourseName VARCHAR(50) NOT NULL
);

CREATE TABLE Enrollments (
    EnrollID  INT AUTO_INCREMENT PRIMARY KEY,
    StudentID INT,
    CourseID  INT,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID)  REFERENCES Courses(CourseID)
);

The Enrollments table links students and courses. StudentID in Enrollments is a foreign key pointing to StudentID in Students. A record cannot be inserted into Enrollments with a StudentID that does not exist in the Students table.

Visual Relationship

3. Unique Key

A unique key ensures that all values in a column are different — no duplicates allowed. Unlike a primary key, a unique key column can contain NULL values (one NULL is typically allowed). A table can have multiple unique keys.

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Email     VARCHAR(100) UNIQUE,
    Phone     VARCHAR(15)  UNIQUE
);

No two students can have the same email or the same phone number, but both email and phone can be left as NULL.

4. Candidate Key

A candidate key is any column (or combination of columns) that could uniquely identify each row in a table. In other words, a candidate key is a potential primary key. From all candidate keys, one is chosen as the actual primary key; the remaining ones become alternate keys.

Example: In the Students table, both StudentID and Email can uniquely identify a student. Both are candidate keys. If StudentID is chosen as the primary key, then Email becomes an alternate key (enforced with a UNIQUE constraint).

5. Composite Key

A composite key is a primary key made up of two or more columns combined. No single column alone can uniquely identify a row — but the combination does.

-- A student can enroll in a course only once
-- The combination (StudentID + CourseID) must be unique
CREATE TABLE Enrollments (
    StudentID   INT,
    CourseID    INT,
    EnrollDate  DATE,
    PRIMARY KEY (StudentID, CourseID)   -- Composite Primary Key
);

Student 1 enrolled in course 101 and course 102 — both rows are allowed because the combinations are different. But student 1 cannot enroll in course 101 twice.

6. Surrogate Key

A surrogate key is an artificially generated key that has no real-world meaning. It is created only for the purpose of uniquely identifying rows in a table. The most common example is an AUTO_INCREMENT integer column.

CREATE TABLE Orders (
    OrderID    INT AUTO_INCREMENT PRIMARY KEY,  -- Surrogate key
    CustomerName VARCHAR(50),
    OrderDate  DATE
);

OrderID (1, 2, 3...) has no business meaning — it is just a number invented by the database to uniquely identify each order. This is a surrogate key.

7. Natural Key

A natural key is a key based on real-world data that already exists and naturally identifies a record. Examples include email address, Aadhaar number, PAN number, or passport number — values that are unique in the real world.

In practice, surrogate keys are often preferred over natural keys because natural keys can change (e.g., someone changes their email), while surrogate keys are stable and never change.

8. Super Key

A super key is any set of columns that can uniquely identify each row in a table — including combinations with extra, unnecessary columns. Every primary key and candidate key is a super key, but not every super key is minimal enough to be a candidate key.

Example: In the Students table with columns StudentID, Email, StudentName:

• {StudentID} — Super key and also candidate key
• {Email} — Super key and also candidate key
• {StudentID, Email} — Super key but NOT a candidate key (it has an extra column)
• {StudentID, StudentName} — Super key but NOT a candidate key

Key Types at a Glance

Key Points to Remember

• Every table should have a primary key — it is the table's unique identifier.
• A foreign key creates a parent-child relationship between two tables.
• A candidate key is a potential primary key — one is chosen as the primary key, others become unique keys.
• A composite key uses two or more columns together to create a unique identifier.
• Surrogate keys (AUTO_INCREMENT) are generally preferred over natural keys because they are stable and never change.

Summary

Keys are the backbone of relational database design. The primary key uniquely identifies rows within a table. The foreign key links tables together. Unique, composite, candidate, surrogate, and natural keys each serve a specific design purpose. Understanding how keys work — and choosing them thoughtfully — is essential for building well-structured, efficient, and reliable databases.