DBMS Keys Interview Questions
Last updated 2026-11-19
Keys look easy until the interviewer asks how many super keys a relation has or which candidate keys follow from a set of dependencies. This page covers the definitions in one pass and then works through the counting and deriving exercises that separate a prepared candidate from one who memorised a diagram.
Keys connect to the rest of the syllabus: normalization depends on knowing candidate keys, indexing uses them as the default index, and the ER model maps them to tables. Start with DBMS interview questions if you want the full overview.
| Key types | Super, candidate, primary, alternate, foreign, composite, surrogate |
|---|---|
| Typical exercise | Derive keys using attribute closure |
| Level | Easy to medium |
The hierarchy in one sentence each
- Super key: any set of attributes that uniquely identifies a tuple.
- Candidate key: a minimal super key, so removing any attribute breaks uniqueness.
- Primary key: the candidate key chosen to identify rows; it cannot be NULL.
- Alternate key: a candidate key not chosen as primary.
- Foreign key: attributes that reference a candidate key of the same or another table.
Counting rule worth memorising
If a relation with n attributes has exactly one candidate key of k attributes, the number of super keys is 2 raised to the power (n - k), because the key attributes are fixed and any subset of the remaining attributes may be added. With more than one candidate key, use inclusion-exclusion rather than a formula.
DBMS Keys Interview Questions: questions with worked answers
1 What is a super key? Give an example for student(roll, email, name, dept).
easyA super key is any set of attributes whose values uniquely identify each tuple. If roll is unique, then roll, (roll, name) and (roll, name, dept) are all super keys, because adding attributes to a key keeps it unique. Name alone is not, since two students can share a name. Every candidate key is a super key, and the set of all attributes is always a super key as long as tuples are distinct.
2 What is a candidate key, and how is it different from a super key?
easyA candidate key is a minimal super key: no proper subset of it is still a super key. In student, roll is a candidate key, and so is email if emails are unique, while (roll, name) is a super key but not a candidate key because roll alone suffices. A relation can have several candidate keys, and all of them contain the attributes that never appear on the right side of any dependency.
3 Difference between primary key and unique key?
easyBoth enforce uniqueness. A table has one primary key, which also forbids NULL and conventionally identifies rows, and often becomes the clustered index. A table may have many unique constraints, and they usually allow NULL, with engine-specific rules about how many NULLs are permitted. Foreign keys normally reference the primary key but may reference any unique column.
4 What is an alternate key?
easyAn alternate key is a candidate key that was not chosen as the primary key. If roll and email are both candidate keys and roll is the primary key, email is the alternate key. In SQL it is declared with a UNIQUE NOT NULL constraint.
5 What is a foreign key? Can it be NULL or duplicate?
mediumA foreign key is a column or set of columns whose values must match an existing value of a referenced candidate key, or be NULL. It can be duplicated, since many child rows may point at one parent. It may be NULL unless declared NOT NULL, which models an optional relationship; with a composite foreign key, SQL by default treats a row with any NULL component as unchecked, unless MATCH FULL is used. It may also reference its own table, as in employee.manager_id.
sqlCREATE TABLE department (dept_id INT PRIMARY KEY, name VARCHAR(50) NOT NULL); CREATE TABLE employee ( emp_id INT PRIMARY KEY, manager_id INT REFERENCES employee(emp_id), dept_id INT NOT NULL REFERENCES department(dept_id) );6 Explain the referential actions CASCADE, SET NULL, RESTRICT and NO ACTION.
mediumThey define what happens to child rows when a referenced parent row is deleted or its key is updated. CASCADE applies the same change to the children: deleting a department deletes its employees. SET NULL sets the child foreign key to NULL, which requires the column to be nullable. RESTRICT rejects the change immediately if children exist. NO ACTION also rejects it but the check may be deferred to the end of the statement or transaction. SET DEFAULT assigns the column default. Choose CASCADE only where children have no meaning without the parent.
7 R(A,B,C) has A to B, B to C, C to A. Find all candidate keys and the number of super keys.
hardCompute closures: A+ includes B, then C, so A+ = {A,B,C}. B+ gets C then A, so B+ = {A,B,C}. C+ gets A then B, so C+ = {A,B,C}. Each single attribute is a key, and since they are all minimal the candidate keys are A, B and C. Every non-empty subset of {A,B,C} contains a candidate key, so the super keys are all 7 non-empty subsets: A, B, C, AB, AC, BC and ABC.
8 R(A,B,C,D) has AB to C, C to D, D to A. Find all candidate keys.
hardB appears on no right-hand side, so every key contains B. B+ = {B}, not enough. AB+: AB gives C, C gives D, so AB+ = {A,B,C,D}: key. BC+: C gives D, D gives A, so BC+ = {A,B,C,D}: key. BD+: D gives A, then AB gives C, so BD+ = all: key. All three are minimal because B alone fails and each pair has no key as a subset. Candidate keys: AB, BC and BD. All attributes are prime, so the relation is in 3NF, and it is not BCNF because C to D has a left side that is not a superkey.
9 What is a composite key? When would you prefer a surrogate key over it?
mediumA composite key uses two or more columns together, as in enrolment(student_id, course_id). It is natural for junction tables because the pair is the identity. A surrogate key, such as an auto-increment id, is preferable when the composite is long, parts can change, or other tables need to reference the row: a single-column foreign key is simpler than copying three columns. Keep a UNIQUE constraint on the natural combination so duplicates are still blocked.
10 What is the difference between a natural key and a surrogate key?
mediumA natural key is a real attribute with business meaning, such as passport number or ISBN. A surrogate key is system-generated and carries no meaning, such as a sequence or UUID. Natural keys can change or be reused, which forces cascading updates; surrogate keys never need to change, but they allow duplicate real-world rows unless a unique constraint exists on the natural key. For performance, narrow sequential surrogates also keep the clustered index compact.
11 Does a foreign key create an index?
mediumIt depends on the engine. MySQL InnoDB requires an index on the referencing columns and creates one automatically if none exists. PostgreSQL and Oracle do not, and a missing index makes joins and parent deletes slow, because each parent delete must scan the child table to check for referencing rows. Index foreign keys by default in those systems. See the indexing page.
12 Can a table have no primary key? What problems does that cause?
hardMost engines allow it, but it is almost always a mistake. Without a key, identical rows can be inserted, there is no reliable way to update or delete exactly one row, replication tools may fail or fall back to full-row comparison, and ORMs cannot identify entities. InnoDB silently creates a hidden 6-byte row id as the clustered key if you define none, which you cannot use in queries. Define a primary key even if it is a surrogate.
13 What is a foreign key violation, and how do deferred constraints help?
mediumIt happens when you insert a child referencing a missing parent or delete a parent that still has children under RESTRICT. Deferred constraints, available in PostgreSQL and Oracle with DEFERRABLE INITIALLY DEFERRED, postpone the check to commit time, which allows inserting two mutually referencing rows in one transaction or reordering bulk loads. MySQL InnoDB does not support deferral; people temporarily disable checks with SET foreign_key_checks = 0, which should be used carefully.
Frequently asked questions
Can a primary key have more than one column?
Yes. That is a composite primary key, and uniqueness applies to the combination while each column is still NOT NULL.
Is every candidate key a super key?
Yes. A candidate key is a minimal super key, so it is a super key by definition. The reverse is false.
Can a foreign key reference a non-primary column?
Yes, provided that column has a UNIQUE constraint or is otherwise a candidate key.
How many primary keys can a table have?
One, though that single primary key may span several columns.
Related guides
More in CS Fundamentals Interview Questions
- Deadlock Interview Questions with Banker's Algorithm Worked Example
- Design Patterns Interview Questions with Code
- Distributed Systems Interview Questions
- DNS Interview Questions: Resolution, Record Types, Caching and Troubleshooting
- ER Model Interview Questions
- File System Interview Questions: Inodes, Allocation and Disk Scheduling
- Git Interview Questions and Answers with Commands
- HTTP and HTTPS Interview Questions: Methods, Status Codes, TLS and Caching
- Inheritance and Polymorphism Interview Questions
- IP Addressing and Subnetting Interview Questions with Worked Solutions
- Linux Commands Interview Questions with Examples
- Linux Interview Questions and Answers: Concepts, Permissions and Processes