Keys in DBMS — Primary, Candidate, Super, Foreign, Composite Key and Constraints

A key is a column (or set of columns) that identifies rows or links tables. "Explain the types of keys" is one of the most asked DBMS interview questions.

1. Types of keys

Key	Meaning	In the picture
Super key	any set of columns that uniquely identifies a row	(emp_id), (emp_id, name), (email, name)…
Candidate key	a minimal super key (no extra columns)	emp_id, email, aadhaar
Primary key	the candidate key chosen to identify rows; unique and NOT NULL	emp_id
Alternate key	candidate keys not chosen	email, aadhaar
Foreign key	a column that refers to the primary key of another table	dept_id → departments
Composite key	a key made of 2+ columns	(roll_no, course_code) in enrollments

Every candidate key is a super key, but not every super key is a candidate key.

2. Constraints in SQL

Constraints are rules the database enforces on every insert and update.

CREATE TABLE departments (
    dept_id   INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL UNIQUE
);
CREATE TABLE employees (
    emp_id   INT PRIMARY KEY,
    name     VARCHAR(50) NOT NULL,
    email    VARCHAR(80) UNIQUE,
    salary   INT CHECK (salary > 0),
    city     VARCHAR(30) DEFAULT 'Jaipur',
    dept_id  INT REFERENCES departments(dept_id)
);
INSERT INTO departments VALUES (10, 'Engineering'), (20, 'Sales');
INSERT INTO employees (emp_id, name, email, salary, dept_id) VALUES (1, 'Asha', 'asha@x.in', 60000, 10);
SELECT emp_id, name, city, dept_id FROM employees;

emp_id | name | city | dept_id
1 | Asha | Jaipur | 10

(city got the DEFAULT value because we didn't give one.)

Constraint	Rejects
`PRIMARY KEY`	duplicates and NULLs in the key
`UNIQUE`	duplicate values (one NULL usually allowed)
`NOT NULL`	empty values
`CHECK (condition)`	values that break the rule (salary ≤ 0)
`DEFAULT`	— fills a value when none is given
`FOREIGN KEY` / `REFERENCES`	values that don't exist in the parent table

3. Referential integrity and ON DELETE

A foreign key means "this value must exist over there". What happens if the parent row is deleted?

ON DELETE RESTRICT / NO ACTION — block the delete (the default).
ON DELETE CASCADE — delete the child rows too (deleting an order deletes its order items).
ON DELETE SET NULL — set the foreign key to NULL.

4. Primary key vs unique key

	Primary key	Unique key
How many per table	one	many
NULL allowed	no	yes (usually one)
Purpose	identify rows	prevent duplicates in other columns

Common mistakes

Saying a table can have many primary keys. It has one primary key (which may be composite).
Thinking a foreign key must be unique. Many employees can have the same dept_id.
Forgetting NOT NULL on columns that must always have a value.

Quick recap

Super ⊇ candidate ⊇ primary; the rest of the candidates are alternate keys.
Foreign key = link to another table's primary key; composite = multi-column key.
Constraints: PRIMARY KEY, UNIQUE, NOT NULL, CHECK, DEFAULT, FOREIGN KEY.
ON DELETE CASCADE / SET NULL / RESTRICT decides what happens to child rows.

Keys and Constraints — Primary, Candidate, Foreign, Super and Composite Keys