Keys and Constraints in DBMS Interview Questions

A key is a set of columns that identifies a row. In students(roll_no, email, name, dept_id):

• Super key: any identifying set, such as {roll_no, name}.
• Candidate key: a minimal super key, such as {roll_no} or {email}.
• Primary key: the chosen candidate key; unique and never NULL.
• Alternate key: a candidate key that was not chosen.
• Foreign key: points to another table's key (dept_id).
• Composite key: two or more columns.
• Surrogate key: a generated id with no business meaning.

See primary keys.

Both reject duplicates, but a table has one primary key and it is never NULL, while it can have many unique keys and those accept NULL.

CREATE TABLE users (
    id    INTEGER PRIMARY KEY,
    email TEXT UNIQUE,
    name  TEXT
);
INSERT OR IGNORE INTO users VALUES (1, 'asha@mail.com', 'Asha');
INSERT OR IGNORE INTO users VALUES (1, 'other@mail.com', 'Duplicate id');
INSERT OR IGNORE INTO users VALUES (2, 'asha@mail.com', 'Duplicate email');
INSERT OR IGNORE INTO users VALUES (3, NULL, 'No email');
SELECT id, email, name FROM users;

INSERT OR IGNORE skips rows that break a constraint, so both duplicates are dropped and the NULL email is kept. SQLite quirk: a PRIMARY KEY other than INTEGER PRIMARY KEY accepts NULL unless you add NOT NULL or use a STRICT or WITHOUT ROWID table; see the SQLite CREATE TABLE docs. See unique constraints.

A super key is any attribute set that identifies a row; a candidate key is a minimal one, where removing any attribute breaks uniqueness. In employees(emp_id, email, name), {emp_id, name} is a super key but not a candidate key, because {emp_id} alone is unique. Attributes that belong to some candidate key are prime attributes, which the normal forms refer to.

A composite key spans two or more columns, used when no single column is unique but the combination is, typically in a many-to-many junction table.

CREATE TABLE enrollments (
    student_id INTEGER,
    course_id  INTEGER,
    grade      TEXT,
    PRIMARY KEY (student_id, course_id)
);
INSERT OR IGNORE INTO enrollments VALUES (1, 101, 'A');
INSERT OR IGNORE INTO enrollments VALUES (1, 102, 'B');
INSERT OR IGNORE INTO enrollments VALUES (2, 101, 'A');
INSERT OR IGNORE INTO enrollments VALUES (1, 101, 'C');
SELECT student_id, course_id, grade FROM enrollments;

The pair (1, 101) can appear once, so the fourth insert is skipped. Column order matters: this index serves "courses of a student", and "students in a course" needs its own index on course_id.

It prints 2. NULL is not equal to NULL, so two NULLs are not duplicates. SQLite, PostgreSQL and MySQL allow many NULLs in a unique column; SQL Server allows one. For at most one NULL, PostgreSQL 15+ has UNIQUE NULLS NOT DISTINCT.

It prints 2. SQLite enforces foreign keys only after PRAGMA foreign_keys = ON;, which is off by default (SQLite foreign key docs), so the orphan order goes in. MySQL (InnoDB), PostgreSQL and SQL Server enforce them by default. PRAGMA foreign_key_check finds orphans afterwards:

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER REFERENCES customers(id));
INSERT INTO customers VALUES (1, 'Asha');
INSERT INTO orders VALUES (10, 1), (11, 99);
PRAGMA foreign_key_check;

It returns orders | 11 | customers | 0: the child table, the orphan's rowid, the parent table and which foreign key is broken.

It prints 1. ON DELETE CASCADE deletes child rows with their parent, so Asha's two orders go and Ravi's stays. NO ACTION (the default) and RESTRICT refuse the delete instead, and SET NULL or SET DEFAULT rewrite the child column. Cascade parts that cannot exist alone, like order lines; protect money and history with RESTRICT. See foreign keys.

It prints 2. Ravi fails the check and is skipped, but Meera goes in: NULL >= 18 is unknown, and CHECK rejects a row only when the expression is FALSE, the opposite of WHERE. That is standard SQL. To require an age, add NOT NULL. See check constraints.

Rules the DBMS checks on every write: domain (type, CHECK, NOT NULL), entity integrity (the primary key is unique and never NULL), key (UNIQUE) and referential integrity (a foreign key value exists in the parent, or is NULL). Rules that span rows or tables need triggers, since the major engines do not implement CREATE ASSERTION. Enforce them in the database, because there is always a second writer.

Use a generated surrogate key as the primary key and keep the natural key (email, ISBN) as UNIQUE. Natural keys change, and a changed primary key must be updated in every referencing table; they are often wide, and InnoDB copies the primary key into every secondary index. Natural keys suit stable short codes like 'IN', and junction tables.