Keys in DBMS: Super, Candidate, Primary and Foreign Key

A table is only useful if you can point at one row and say "that one". Keys are the columns that let you do it, and the columns that tie one table to another. Interviewers love this topic because the definitions sound alike (super, candidate, primary, alternate) and differ in exactly one word. This note defines each, works through one table to decide which column sets are which, and then covers the integrity constraints that keys make possible.

The keys at a glance

Two more words you will need in normalization: an attribute that belongs to any candidate key is a prime attribute; every other attribute is non-prime.

Super key and candidate key

A super key is any set of attributes that no two rows can share. If {roll_no} is unique, then so is {roll_no, name}, {roll_no, name, batch} and every other set that contains roll_no: adding columns to a unique set keeps it unique. Super keys therefore come in families.

A candidate key is a super key with nothing extra in it: remove any one attribute and it stops being unique. "Minimal" means no proper subset is a super key; it does not mean "fewest columns". A table can have several candidate keys of different sizes.

A key is a rule about every row the table may ever hold, decided from the meaning of the data, not from the rows present today. If today's rows happen to be unique on (name, dept_id), that is a coincidence, not a key.

Worked example: which sets are keys?

Take a student table with five attributes. The college's rules: every student has a unique roll number and a unique college email; names repeat; a department and a batch hold many students.

So the candidate keys are {roll_no} and {email}. We choose roll_no as the primary key: it is short, never NULL, and never changes, whereas a student may change an email address. Email becomes an alternate key, and we declare it UNIQUE so the DBMS enforces it.

How many super keys are there? A set is a super key exactly when it contains roll_no or email. Of the 2^5 = 32 subsets of the five attributes, 2^3 = 8 contain neither (they are subsets of {name, dept_id, batch}), so there are 32 − 8 = 24 super keys. With a single one-attribute candidate key the count would be 2^4 = 16.

1. Five attributes make 2^5 = 32 sets, drawn here by size. A set is a super key when its closure is all five attributes: knowing its values fixes the whole row.
2. Every set containing roll_no or email determines the row: 24 super keys. The 8 grey sets are the subsets of {name, dept_id, batch}, which never identify a student.
3. Candidate keys are the minimal super keys: remove any attribute and they stop identifying rows. Only R and E qualify; RN is a super key, but its N can go.
4. The designer picks one candidate key as the primary key: roll_no, short and never changing. email becomes an alternate key, declared UNIQUE. Every candidate key is a super key, but 22 of the 24 super keys are not candidate keys.

Primary, alternate and unique keys

The primary key is the candidate key the designer picks to identify rows. The DBMS enforces two things on it: uniqueness and NOT NULL (entity integrity). A table has at most one primary key, though it may span several columns. Good primary keys are short, stable and meaningless outside the database; this is why many teams use surrogate keys.

Every other candidate key is an alternate key. Declaring it UNIQUE turns the rule into a constraint. The differences interviewers ask for:

If an InnoDB table has no primary key, InnoDB clusters it on the first UNIQUE index whose columns are all NOT NULL, or failing that on a hidden row id it generates. Declaring a primary key yourself is better.

Foreign keys and composite keys

A foreign key is a set of columns in one table (the child, or referencing table) whose values must match a primary or unique key in another table (the parent, or referenced table). It is how relationships from the ER model become tables. Facts that surprise people:

• A foreign key may be NULL (meaning "not linked") unless declared NOT NULL.
• It may repeat: many students share dept_id CSE.
• It may refer to its own table: employee.manager_id references employee.emp_id.
• The column names need not match, but the types must be compatible.

1. student.dept_id is a foreign key referencing department.dept_id. Every value must exist in the parent table; it may repeat, as CSE does for 2 students.
2. INSERT student 105 with dept_id 'CIV': there is no CIV in department, so referential integrity fails and the DBMS rejects the row. Nothing is stored.
3. INSERT the same student with dept_id NULL instead: it is accepted, linked to no department. A foreign key may be NULL unless the column is also declared NOT NULL.

A composite key is any key with more than one column. In an enrolment table neither roll_no nor course_id is unique alone, but each pair appears once, so (roll_no, course_id) is the primary key, and each part is also a foreign key. If a student may repeat a course in a later semester, the key widens to (roll_no, course_id, semester).

1. {roll_no} alone is not a key: 101 appears on 2 rows. One counterexample is enough to rule a key out.
2. {course_id} alone is not a key: CS301 appears on 2 rows. One counterexample is enough to rule a key out.
3. {roll_no, course_id} is different on every row, which matches the college's rule that a student takes a course once: the composite primary key. The rows agree with the rule; they could never prove it.

CREATE TABLE department (
  dept_id   CHAR(3)     PRIMARY KEY,
  dept_name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE student (
  roll_no INT          PRIMARY KEY,
  email   VARCHAR(100) NOT NULL UNIQUE,
  name    VARCHAR(50)  NOT NULL,
  dept_id CHAR(3),
  batch   SMALLINT     CHECK (batch BETWEEN 2000 AND 2100),
  FOREIGN KEY (dept_id) REFERENCES department (dept_id)
    ON DELETE SET NULL ON UPDATE CASCADE
);

CREATE TABLE enrollment (
  roll_no   INT,
  course_id CHAR(5),
  grade     CHAR(2),
  PRIMARY KEY (roll_no, course_id),
  FOREIGN KEY (roll_no) REFERENCES student (roll_no) ON DELETE CASCADE
);

In a full schema enrollment.course_id would reference a course table the same way.

Natural and surrogate keys

The common practice is a surrogate primary key plus a UNIQUE constraint on the natural key, which gives you stable joins and real-world uniqueness.

Integrity constraints

Integrity constraints are rules the DBMS checks on every insert, update and delete:

MySQL parsed but ignored CHECK constraints before version 8.0.16; from 8.0.16 it enforces them.

What happens on delete

Referential integrity can be broken from the child side (the rejected insert above) or from the parent side (deleting a department that students still reference). For the parent side, the foreign key's referential action decides: RESTRICT or NO ACTION, CASCADE, SET NULL, or SET DEFAULT (standard SQL sets the column default; MySQL's InnoDB rejects this action). Suppose department ECE is deleted while students 103 and 104 reference it:

1. DELETE FROM department WHERE dept_id = 'ECE'. Students 103 and 104 still reference it, so the foreign key's ON DELETE action decides what happens to them.
2. RESTRICT (and NO ACTION): the delete fails with an error and nothing changes. It is the safe choice wherever losing history by accident would be worse than an error.
3. CASCADE: ECE is deleted and so are students 103 and 104, 2 rows removed by one statement. Right for rows that mean nothing without their parent, like enrolments.
4. SET NULL: ECE is deleted, and students 103 and 104 keep their rows with dept_id NULL, which needs a nullable column. Right when the child outlives its parent, as a student does a merged department.

ON UPDATE takes the same actions when the parent key value changes; ON UPDATE CASCADE copies a renamed key into every child. With no action written, MySQL uses NO ACTION, which in MySQL is the same as RESTRICT. In standard SQL the two differ slightly: RESTRICT checks at once, while NO ACTION checks at the end of the statement (PostgreSQL can defer it to commit).

Choose by meaning: CASCADE for enrolments, which are meaningless without their student; SET NULL for students, who survive a department being merged away; RESTRICT for financial records, where deleting history by accident is worse than an error.

Common mistakes

• Calling a candidate key "the key with the fewest columns": minimal means no removable attribute, not smallest.
• Deciding keys from sample rows: rows can disprove a key, never prove one.
• Saying a foreign key must be unique or NOT NULL: it may repeat and may be NULL.
• Saying a unique key allows exactly one NULL everywhere: that is SQL Server's rule, not MySQL's or PostgreSQL's.
• Forgetting that every candidate key is also a super key.
• Using CASCADE everywhere: one delete can silently remove thousands of rows.

Interview questions

What is the difference between a super key, a candidate key and a primary key? A super key is any uniquely identifying set of attributes. A candidate key is a super key with no removable attribute. The primary key is the candidate key chosen to identify rows, and it cannot be NULL.

Can a table have more than one primary key? No. It has at most one primary key, which may be composite. It can have several candidate keys; the ones not chosen are alternate keys, usually declared UNIQUE.

Can a table have no primary key? SQL allows it, but the table then accepts duplicate rows and nothing can reference it reliably. InnoDB still clusters it, on the first NOT NULL unique index or a hidden generated row id.

What is referential integrity? Every non-NULL foreign key value must match an existing value of the referenced key. The DBMS rejects child rows that break it and applies the foreign key's ON DELETE or ON UPDATE action when a parent row changes.

What is the difference between ON DELETE CASCADE and ON DELETE SET NULL? CASCADE deletes the child rows along with the parent. SET NULL keeps the child rows and sets their foreign key to NULL, which requires the column to be nullable.

What is a prime attribute? An attribute that is part of at least one candidate key. In the student example, roll_no and email are prime and the rest are non-prime; 2NF and 3NF are defined in these terms.

Relation R(A, B, C, D) has candidate key A only. How many super keys does it have? Every subset that contains A is a super key, and there are 2^3 = 8 such subsets. They are A, AB, AC, AD, ABC, ABD, ACD and ABCD.

Why prefer a surrogate key? Why not always? It is short, stable and makes joins cheap, and it does not leak business data into other tables. But it does not stop the same real-world entity being inserted twice, so you keep a UNIQUE constraint on the natural key as well.

Next, the rules that decide which columns determine which: Functional Dependencies in DBMS. Practise keys and constraints in the SQL (Basic) skill test.