Keys in DBMS Explained

A key in a DBMS is a column (or set of columns) used to uniquely identify rows or to link tables together. Keys are what make the relational model work: they guarantee every row is identifiable and they connect related tables by matching values. Get keys right and your data stays consistent; get them wrong and duplicates and orphans creep in.

Here is the whole family of keys, from the broadest to the most specific.

Super key

A super key is any set of columns that uniquely identifies a row. It may contain extra, unnecessary columns. For a student table, {student_id} is a super key — and so is {student_id, name}, because adding name does not break uniqueness. Super keys are the loosest category.

Candidate key

A candidate key is a minimal super key — remove any column and it stops being unique. {student_id} is a candidate key; {student_id, name} is not, because name is redundant. A table can have several candidate keys. For example, a student might be uniquely identified by either student_id or email — both are candidate keys.

Primary key

The primary key is the one candidate key you choose as the official row identifier. Rules:

• It must be unique across all rows.
• It can never be NULL (this is entity integrity).
• A table has exactly one primary key (though it can span multiple columns).

Alternate key

Any candidate key not chosen as the primary key is an alternate key. If you pick student_id as primary, then email becomes an alternate key — and you typically enforce it with a UNIQUE constraint.

Composite key

A composite key is a primary (or candidate) key made of two or more columns together. No single column is unique on its own, but the combination is. The classic case is a junction table for a many-to-many relationship.

Foreign key

A foreign key is a column in one table whose values must match a primary key in another table (or be NULL). It enforces referential integrity — you cannot reference a row that does not exist. This is how tables link.

Surrogate vs natural key

• A natural key comes from real-world data (e.g. a student's email or PAN).
• A surrogate key is an artificial identifier with no business meaning (e.g. an auto-incrementing id).

Surrogate keys are popular because they never change, are compact, and avoid leaking business data. Natural keys can change (people change email addresses), which causes cascading updates.

All the keys in one schema

-- PostgreSQL syntax shown; MySQL notes inline

CREATE TABLE student (
    -- Surrogate PRIMARY KEY (auto-generated, no business meaning)
    student_id  INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    -- In MySQL this would be:  student_id INT AUTO_INCREMENT PRIMARY KEY

    -- email is a CANDIDATE KEY we did not pick as primary,
    -- so it becomes an ALTERNATE KEY, enforced with UNIQUE
    email       VARCHAR(160) NOT NULL UNIQUE,

    full_name   VARCHAR(120) NOT NULL
);

CREATE TABLE course (
    course_id   INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    title       VARCHAR(150) NOT NULL
);

CREATE TABLE enrolment (
    -- Two FOREIGN KEYs, each pointing at a primary key elsewhere
    student_id  INTEGER NOT NULL REFERENCES student(student_id),
    course_id   INTEGER NOT NULL REFERENCES course(course_id),
    enrolled_on DATE NOT NULL,

    -- COMPOSITE PRIMARY KEY: the pair is unique, neither column is alone
    PRIMARY KEY (student_id, course_id)
);

Run this and the database will: auto-generate student_id values, reject a duplicate email, reject an enrolment for a non-existent student or course, and reject the same student-course pair twice.

ON DELETE / ON UPDATE actions

Foreign keys can specify what happens when the referenced row changes:

CREATE TABLE enrolment (
    student_id  INTEGER NOT NULL
        REFERENCES student(student_id)
        ON DELETE CASCADE,    -- delete enrolments when the student is deleted
    course_id   INTEGER NOT NULL
        REFERENCES course(course_id)
        ON DELETE RESTRICT,   -- block deleting a course that still has enrolments
    PRIMARY KEY (student_id, course_id)
);

CASCADE, RESTRICT, SET NULL, and NO ACTION are standard SQL and supported by both PostgreSQL and MySQL (with InnoDB).

Common mistakes

• Using a mutable natural key as primary. If you make email the primary key and someone changes their email, every foreign key referencing it must update too. Prefer a stable surrogate.
• Forgetting NOT NULL on a primary key column. In standard SQL a primary key implies NOT NULL, but relying on a column merely being UNIQUE is not the same — UNIQUE allows one NULL in most databases.
• No foreign keys at all. Skipping them lets orphaned rows accumulate. The integrity guarantee is worth the tiny write cost.
• Composite key column order confusion. In a composite key the column order matters for some index uses; put the most selective / most-queried column first when it helps.

FAQ

Can a table have no primary key? Technically yes, but you should always define one. Without it, you cannot reliably identify or update a specific row, and replication and tooling misbehave.

Is a primary key the same as a unique index? A primary key creates a unique index and enforces NOT NULL and marks the column as the table's identifier. A plain unique index does only the uniqueness part.

Can a foreign key be NULL? Yes — a NULL foreign key means "no relationship yet," which is valid. It only must match an existing key when it is not NULL.

Keep learning

Keys are central to the relational model and to normalization. See keys in action when you write SQL joins.

Want to practise designing keyed schemas that hold up in production? Join the waitlist for the DBMS & SQL course at Infoplanet, Jalgaon.