Types of Keys in DBMS

Why keys matter

Keys are columns used to uniquely identify rows or to link tables together. They are what keep a relational database correct and connected. There are several types, and they build on each other — from the broad super key down to the specific primary key.

Super key

A super key is any column or set of columns that can uniquely identify a row — even if it has extra, unneeded columns. For a students table, {roll}, {roll, name} and {roll, name, marks} are all super keys, because each uniquely identifies a row.

Candidate key

A candidate key is a minimal super key — it has no extra columns. From the super keys above, only {roll} is a candidate key (assuming roll is unique), because removing any column would break uniqueness. A table can have several candidate keys.

Primary key

The primary key is the one candidate key you choose as the official identifier. It cannot be null and cannot repeat, and there is only one per table.

CREATE TABLE students (
    roll INT PRIMARY KEY,
    name VARCHAR(50)
);

Here roll is the primary key — every student must have a unique, non-null roll number.

Unique key

A unique key also enforces uniqueness, but it can allow one null, and a table can have several. Use it for columns that must be unique but are not the main identifier, like an email.

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

Foreign key

A foreign key links tables. It is a column that points to the primary key of another table.

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Here orders.customer_id refers to customers.id, connecting each order to a customer and preventing orders for non-existent customers.

Composite key

A composite key uses two or more columns together as the key, when no single column is unique alone.

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);

Neither student_id nor course_id is unique alone, but together they uniquely identify one enrolment.

Summary

Summary

• Keys identify rows uniquely and link tables.
• Super key: identifies a row, possibly with extra columns; candidate: minimal super key.
• Primary key: the chosen identifier, never null, one per table.
• Unique key: enforces uniqueness, allows one null, several allowed.
• Foreign key links tables; composite key uses multiple columns.

Next, compare the two most important in primary key vs foreign key, or learn the table model in RDBMS concepts.