Keys in DBMS interview questions, with answers

The questions, with answers

1. 1.What are super keys, candidate keys, primary keys and alternate keys, and how do they relate?

   A super key is any set of columns whose values are unique across rows — it identifies a row, possibly with columns to spare. A candidate key is a minimal super key: remove any column and it stops being unique. A table can have several candidate keys; the designer picks one as the primary key, and the ones not picked are alternate keys. For Students(roll_no, email, name), both roll_no and email are unique, so {roll_no}, {email}, {roll_no, name} and {roll_no, email} are all super keys; the candidate keys are {roll_no} and {email}; choose roll_no as the primary key and email becomes an alternate key. The nesting is the answer: primary ⊆ candidate ⊆ super.

2. 2.What is a foreign key, and what does referential integrity mean in practice?

   A foreign key is a column (or set of columns) in one table whose values must match a primary or unique key in another table — dept_id in Employees pointing at Departments. Referential integrity is the guarantee the database enforces around it: you cannot insert an employee with a dept_id that does not exist, and you cannot delete or change a department that employees still reference — unless you have declared what should happen. That declaration is the ON DELETE / ON UPDATE action: RESTRICT or NO ACTION refuses the change, CASCADE applies it to the child rows too, and SET NULL (or SET DEFAULT) detaches them. CASCADE on a delete is the one to be careful with in an interview answer: it can silently remove a lot of rows.

   CREATE TABLE Employees (
       emp_id  INT PRIMARY KEY,
       name    VARCHAR(100) NOT NULL,
       dept_id INT,
       FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
           ON DELETE SET NULL
           ON UPDATE CASCADE
   );

3. 3.What is the difference between a primary key and a UNIQUE constraint?

   Both guarantee that no two rows share a value. The differences: a primary key column cannot be NULL, while a UNIQUE column can (and in most databases can hold several NULLs, since NULL never equals NULL); a table has exactly one primary key but any number of UNIQUE constraints; and the primary key is what foreign keys point to by default and, in InnoDB and SQL Server, what the table is physically ordered by. Semantically, the primary key is the row's identity; a UNIQUE constraint is a business rule — one account per email — that happens to also make the column a candidate key. When someone asks "can a table have two primary keys", the answer is no, but it can have one primary key and several unique keys.

4. 4.What is a composite key, and when should you use one instead of a surrogate id?

   A composite key is a primary key made of more than one column, because no single column is unique on its own. Enrollment(student_id, course_id) is the standard example: a student appears many times, a course appears many times, but the pair appears once. Use a composite key when the combination is the natural identity of the row and the table is mostly looked up by that combination — junction tables for many-to-many relationships are the classic case. Add a surrogate id instead when other tables need to reference the row (a three-column foreign key is painful), when the natural key can change, or when the key would be long and slow to index. Many teams do both: a surrogate primary key plus a UNIQUE constraint on the natural combination.

5. 5.Natural key or surrogate key — which should you choose?

   A natural key is a real-world identifier — email, ISBN, PAN number — and a surrogate is a meaningless generated value such as an auto-increment integer or a UUID. Natural keys are self-describing and save a join when you only need the identifier, but they can change (people change emails), they may be reused, and they are often long and composite. Surrogates never change, are compact for indexes and foreign keys, and keep identity separate from data, at the price of one more column and a lookup to find a row from its business value. The mainstream answer: a surrogate primary key plus a UNIQUE constraint on the natural key, so you get stable references and still enforce the business rule. Mention that UUIDs are random and fragment a clustered index, so auto-increment or time-ordered ids are preferred for InnoDB.

6. 6.How do you find the candidate keys of a relation from its functional dependencies?

   Compute attribute closures. Start with any attribute that never appears on the right-hand side of a dependency — nothing determines it, so it must be in every key. Then take closures of candidate sets: repeatedly add every attribute that a subset of your set determines, until nothing changes; if the closure is all attributes, the set is a super key, and it is a candidate key if no proper subset also works. Example: R(A, B, C, D) with AB → C, C → D, D → A. B is on no right-hand side, so B is in every key. Closure of AB is ABCD (AB gives C, C gives D), so AB is a key. Closure of BC is BCDA, so BC is a key too, and closure of BD is BDAC, so BD is a key. Three candidate keys: AB, BC and BD — which is why 'the key' is the wrong phrase for a relation until you have checked.

7. 7.Can a foreign key be NULL, and can it reference something other than a primary key?

   Yes to both. A foreign key column may be NULL unless you declare it NOT NULL; NULL means "no related row" — an employee not yet assigned to a department — and the integrity check simply does not apply to NULL values. Making the column NOT NULL is how you express that every employee must have a department. And a foreign key can reference any column set with a PRIMARY KEY or UNIQUE constraint on the parent table, not only the primary key; referencing a non-unique column is an error, because the reference would be ambiguous. It can also reference its own table, which is the standard way to store a hierarchy, and it can be composite, in which case all of its columns must match together — by default (MATCH SIMPLE) the check is skipped whenever any one of them is NULL.

8. 8.What is a self-referencing foreign key, and how do you query a hierarchy built on one?

   A foreign key that points at the primary key of its own table. Employees(emp_id, name, manager_id) with manager_id referencing emp_id stores an org chart in one table: the CEO has a NULL manager_id, everyone else points at their manager. Querying one level is a self join — the table joined to itself under two aliases, one for the employee and one for the manager, with a LEFT JOIN so the CEO is not dropped. Walking the whole tree needs a recursive common table expression (WITH RECURSIVE), supported by PostgreSQL, SQL Server, Oracle, SQLite and MySQL 8, which starts from the root and repeatedly joins children to the rows found so far.

   SELECT e.name AS employee, m.name AS manager
   FROM Employees e
   LEFT JOIN Employees m ON m.emp_id = e.manager_id;

How the diagnostic asks it

One question from the DBMS bank, exactly as a sitting would show it. The bank has 5 on keys and 60 across DBMS.