A key is one or more columns used to identify rows uniquely or to link tables together. There isn't just one kind — different keys solve different problems. Let's go through each one properly.

We'll use this table throughout:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_code VARCHAR(20) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    ssn VARCHAR(11) NOT NULL UNIQUE,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    department_id INT
);

mysql> SHOW COLUMNS FROM employees;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| id            | int          | NO   | PRI | NULL    | auto_increment |
| employee_code | varchar(20)  | NO   | UNI | NULL    |                |
| email         | varchar(255) | NO   | UNI | NULL    |                |
| ssn           | varchar(11)  | NO   | UNI | NULL    |                |
| first_name    | varchar(100) | YES  |     | NULL    |                |
| last_name     | varchar(100) | YES  |     | NULL    |                |
| department_id | int          | YES  |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+

A Primary Key uniquely identifies every row.

Rules:

• Cannot be duplicated.
• Cannot be NULL.
• Only one Primary Key per table.

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_code VARCHAR(20) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    ssn VARCHAR(11) NOT NULL UNIQUE,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    department_id INT
);

Here, id was picked as the Primary Key.

INSERT INTO employees 
(employee_code, email, ssn, first_name, last_name, department_id)
VALUES
('EMP001', 'john@gmail.com', '123-45-6789', 'John', 'Doe', 1),
('EMP002', 'alice@gmail.com', '987-65-4321', 'Alice', 'Smith', 2),
('EMP003', 'bob@gmail.com', '555-66-7777', 'Bob', 'Wilson', 1);

mysql> SELECT * FROM employees;
+----+---------------+-----------------+-------------+------------+-----------+---------------+
| id | employee_code | email           | ssn         | first_name | last_name | department_id |
+----+---------------+-----------------+-------------+------------+-----------+---------------+
|  1 | EMP001        | john@gmail.com  | 123-45-6789 | John       | Doe       |             1 |
|  2 | EMP002        | alice@gmail.com | 987-65-4321 | Alice      | Smith     |             2 |
|  3 | EMP003        | bob@gmail.com   | 555-66-7777 | Bob        | Wilson    |             1 |
+----+---------------+-----------------+-------------+------------+-----------+---------------+

Why is id be the Primary Key?

Because every employee gets a unique ID. 1, 2, 3

There can never be two employees with id = 1 and the value can never be NULL.

MySQL automatically creates an index

When you declare PRIMARY KEY(id)

MySQL automatically creates an index on id.

What happens if you try this?

INSERT INTO employees
VALUES (
    1,
    'EMP010',
    'new@gmail.com',
    '222-33-4444',
    'New',
    'User',
    1
);

MySQL says: ERROR 1062 (23000): Duplicate entry '1' for key 'employees.PRIMARY' because 1 already exists.

mysql> INSERT INTO employees
-> VALUES (
->     1,
->     'EMP010',
->     'new@gmail.com',
->     '222-33-4444',
->     'New',
->     'User',
->     1
-> );
ERROR 1062 (23000): Duplicate entry '1' for key 'employees.PRIMARY'

Any column (or combination of columns) that could legally serve as the primary key — meaning it's unique and never empty for every row. A table can have several candidate keys, but only one gets chosen as the actual primary key.

In the employees table,

id, employee_code, email and ssn (assuming each is guaranteed unique and always filled in). All four are candidate keys.

A Candidate Key is any column that could become the Primary Key.

Any candidate key that wasn't chosen as the primary key. It's still unique and still usable — just not the official identifier.

Since id became the primary key, employee_code, email and ssn are now alternate keys — they could have been the primary key, but weren't picked.

-- employee_code and ssn are alternate keys:
-- still unique, but not the chosen primary key
ALTER TABLE employees
ADD CONSTRAINT unique_employee_code UNIQUE (employee_code);

Think of it like this:

✔ id          ← chosen
 
employee_code
email
ssn

The remaining Candidate Keys are called Alternate Keys.

Any set of columns that can uniquely identify a row — including columns that aren't strictly necessary.

Every candidate key is a super key, but a super key can also include extra, redundant columns.

All of these uniquely identify a row, so all are super keys:

• id
• id + email
• employee_code + ssn
• id + employee_code + ssn
• email
• email + department_id
• email + first_name

A Super Key is any combination of columns that uniquely identifies a row.

A primary key made of two or more columns combined.

A Composite Key uses multiple columns together.

Example:

Suppose employees can work in multiple departments.

| employee_id | department_id |
|-------------|---------------|
| 1           |             1 |
| 1           |             2 |
| 2           |             1 |

Neither column is unique.

Employee 1 appears twice.

Department 1 appears twice.

But together employee_id + department_id is unique.

CREATE TABLE employee_departments (
    employee_id INT,
    department_id INT,
    PRIMARY KEY(employee_id, department_id)
);

A column in one table that references the Primary Key of another table, creating a relationship between them.

Table employees has department_id INT but it is not yet a Foreign Key.

Suppose we have another table.

CREATE TABLE departments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

mysql> DESC departments;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| name  | varchar(100) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+

INSERT INTO departments (id, name)
VALUES
(1, 'Engineering'),
(2, 'Finance');

mysql> SELECT * FROM departments;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | Engineering |
|  2 | Finance     |
+----+-------------+

ALTER TABLE employees
ADD CONSTRAINT fk_employee_department
FOREIGN KEY (department_id)
REFERENCES departments(id);

employees.department_id is a Foreign Key pointing to departments.id. MySQL will reject any department_id value that doesn't already exist in departments — that's called referential integrity.

Enforces that no two rows share the same value in a column — but unlike a Primary Key, a table can have multiple Unique Keys, and (depending on the database) a Unique column can allow one NULL.

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    employee_code VARCHAR(20) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    ssn VARCHAR(11) NOT NULL UNIQUE,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    department_id INT
);

Add UNIQUE Constraint for email (if not already exists)

ALTER TABLE employees
ADD CONSTRAINT unique_email UNIQUE (email);

An artificial, system-generated identifier that has no real-world meaning — it exists purely to identify the row. id INT AUTO_INCREMENT is a classic surrogate key.

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY, -- surrogate key: just a number, means nothing outside the database
    employee_code VARCHAR(20) NOT NULL
);

The id value (1, 2, 3...) doesn't represent anything about the employee — it's just a stable, guaranteed-unique label.

The opposite of a surrogate key — a key made from data that has real-world meaning, like a Social Security Number(ssn), email.

-- ssn and email are natural keys: they identify something meaningfully,
-- not just internally to the database

Why surrogate keys are usually preferred: natural keys can change (a person's email changes, a company merges and reissues employee codes), which breaks anything referencing them as a foreign key. Surrogate keys never need to change, since they carry no real-world meaning to begin with.

Candidate Key: email

Super Key: email + department_id

Because department_id is unnecessary.

Email alone already identifies the employee.

Candidate Keys are simply the smallest possible Super Keys.

| Column        | Key Type    |
|---------------|-------------|
| id            | Primary Key |
| employee_code | Unique Key  |
| email         | Unique Key  |
| ssn           | Unique Key  |

     EMPLOYEES TABLE
 
+---------------------------------------------------------------+
| id               → Primary Key                               |
|                  → Candidate Key                             |
|                  → Super Key                                 |
|                                                               |
| employee_code    → Unique Key                                |
|                  → Candidate Key                             |
|                  → Alternate Key                             |
|                  → Super Key                                 |
|                                                               |
| email            → Unique Key                                |
|                  → Candidate Key                             |
|                  → Alternate Key                             |
|                  → Super Key                                 |
|                                                               |
| ssn              → Unique Key                                |
|                  → Candidate Key                             |
|                  → Alternate Key                             |
|                  → Super Key                                 |
|                                                               |
| department_id    → Foreign Key (after REFERENCES is added)   |
+---------------------------------------------------------------+