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

Source: https://skills101.in/learn/dbms-sql/dbms-keys-and-constraints — Skills101 free tutorial (DBMS & SQL). Updated 2026-11-09.

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.

![An employees table with emp_id (primary key), email and aadhaar (candidate keys), name, and dept_id (foreign key)](https://skills101.in/learn-img/dbms-sql/keys.svg)

## 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.

```sql
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;
```

```text
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.


Practice quiz and free certificate: https://skills101.in/learn/dbms-sql/dbms-keys-and-constraints