MySQL Keys — Primary, Foreign, Composite, and Everything In Between

Keys in MySQL are columns (or sets of columns) that identify rows uniquely or link tables together. They enforce rules like "no duplicate values here" or "this value must exist in another table."

Table of Contents

• How Keys Work in MySQL
• Super Key
• Candidate Key
• Primary Key
• Alternate Key
• Composite Key
• Foreign Key

How Keys Work in MySQL

MySQL does not have a "key" keyword that covers all six types. Instead, keys are implemented through constraints and indexes:

• PRIMARY KEY — enforces uniqueness + creates an index
• UNIQUE / UNIQUE KEY — enforces uniqueness + creates an index
• INDEX / KEY — creates an index (does not enforce uniqueness)
• FOREIGN KEY — enforces referential integrity (InnoDB only)

When you create a key, MySQL almost always creates an index behind it. You can see all indexes (and therefore keys) on a table using:

SHOW INDEX FROM table_name;
SHOW CREATE TABLE table_name;

Important: Foreign keys only work with the InnoDB storage engine. MyISAM ignores foreign key syntax entirely — no error, but no enforcement either.

Super Key

A Super Key is any set of columns that can uniquely identify a row. It is the broadest category — if you can add more columns and still identify the row, it is still a super key.

MySQL does not have a SUPER KEY keyword. The concept is theoretical: any combination of columns that covers a unique column is a super key. You can verify this by running a SELECT:

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

-- Is (id, email) unique? Yes — because 'id' alone is already unique.
-- Run this to prove it:
SELECT id, email, COUNT(*)
FROM employees
GROUP BY id, email
HAVING COUNT(*) > 1;
-- Empty result set means (id, email) is a super key.

Any set that contains id is a super key — (id), (id, name), (id, email, name). The super key concept is the foundation: Candidate Keys are just super keys with no extra baggage.

Candidate Key

A Candidate Key is a super key that you cannot shrink — remove any column and it stops being unique.

In MySQL, candidate keys are implemented using UNIQUE (or UNIQUE KEY) constraints. Every column (or column pair) you mark as UNIQUE is a candidate key:

CREATE TABLE employees (
    emp_id INT NOT NULL,
    email VARCHAR(100) NOT NULL,
    name VARCHAR(100),
    UNIQUE (emp_id),    -- candidate key #1
    UNIQUE (email)      -- candidate key #2
);

In this table:

• emp_id is a candidate key.
• email is a candidate key.
• (emp_id, name) is a super key but not a candidate key — name is unnecessary baggage.

Note: In MySQL, UNIQUE allows multiple NULL values (unlike PRIMARY KEY). If a column allows NULL, it is still valid as a UNIQUE constraint, but theoretically it cannot be a candidate key (since NULL means "unknown").

To see all candidate keys on an existing table:

SHOW INDEX FROM employees WHERE Non_unique = 0;
-- Shows all unique indexes = your candidate keys

Primary Key

A Primary Key is the candidate key you choose as the main row identifier. MySQL enforces:

• Only one primary key per table.
• No NULL values allowed.
• Values must be unique.
• InnoDB uses the primary key as the clustered index — rows are physically stored on disk in primary key order.

CREATE TABLE employees (
    emp_id INT NOT NULL,
    email VARCHAR(100) NOT NULL,
    name VARCHAR(100),
    PRIMARY KEY (emp_id)   -- chosen over email
);

Auto-increment (most common pattern in MySQL):

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

MySQL automatically indexes the primary key — you do not need a separate INDEX statement. You can verify:

SHOW INDEX FROM employees;
-- One row with Key_name = 'PRIMARY' and Non_unique = 0

Alternate Key

An Alternate Key is any candidate key that was not chosen as the primary key.

In MySQL, these are the UNIQUE columns that are not the PRIMARY KEY. They are also called unique keys in MySQL terminology.

CREATE TABLE employees (
    emp_id INT NOT NULL,
    email VARCHAR(100) NOT NULL,
    name VARCHAR(100),
    PRIMARY KEY (emp_id),       -- chosen
    UNIQUE KEY (email)          -- alternate key (was not chosen)
);

To see alternate keys on a table:

SHOW INDEX FROM employees
WHERE Non_unique = 0
  AND Key_name != 'PRIMARY';
-- Shows 'email' as a unique non-primary index

MySQL lets you name your unique constraints. By default, it uses the first column name:

CREATE TABLE employees (
    emp_id INT NOT NULL,
    email VARCHAR(100) NOT NULL,
    UNIQUE KEY uk_email (email)   -- named alternate key
);

Composite Key

A Composite Key uses two or more columns together. No single column is unique — only the combination is.

CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT,
    PRIMARY KEY (order_id, product_id)   -- composite primary key
);

In this table:

• order_id alone repeats (one order has many products).
• product_id alone repeats (one product appears in many orders).
• (order_id, product_id) together are unique.

Column order matters in MySQL. The index created by this composite primary key can speed up queries that filter by order_id, but not queries that filter by product_id alone:

-- This query uses the composite index efficiently:
SELECT * FROM order_items WHERE order_id = 101;

-- This query does NOT use the composite index:
SELECT * FROM order_items WHERE product_id = 5;

Composite keys can also be unique keys or foreign keys:

CREATE TABLE product_tags (
    product_id INT NOT NULL,
    tag_id INT NOT NULL,
    UNIQUE KEY (product_id, tag_id)   -- composite unique key
);

Foreign Key

A Foreign Key links two tables. It ensures a value in one table exists in another table first.

Critical MySQL requirement: Foreign keys only work with InnoDB. If you use MyISAM, the FOREIGN KEY syntax is accepted but silently ignored.

-- Check your engine:
SELECT ENGINE FROM information_schema.TABLES
WHERE TABLE_NAME = 'your_table';

CREATE TABLE departments (
    dept_id INT NOT NULL,
    name VARCHAR(100),
    PRIMARY KEY (dept_id)
) ENGINE=InnoDB;

CREATE TABLE employees (
    emp_id INT NOT NULL,
    name VARCHAR(100),
    dept_id INT,
    PRIMARY KEY (emp_id),
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
) ENGINE=InnoDB;

Now inserting an employee with dept_id = 999 fails if no department with that ID exists:

INSERT INTO departments VALUES (1, 'Engineering');     -- OK
INSERT INTO employees VALUES (101, 'Alice', 1);        -- OK
INSERT INTO employees VALUES (102, 'Bob', 999);        -- Error! No such department

MySQL behavior on update/delete:

FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE

MySQL also requires:

• Parent and child columns must have the exact same data type.
• The referenced parent column must be indexed (MySQL auto-indexes PRIMARY KEY and UNIQUE).
• Foreign key columns are not automatically indexed in MySQL — you should add an index yourself for join performance.
• You can have NULLs in a foreign key column (they bypass the check).

To view foreign keys on a table:

SHOW CREATE TABLE employees;
-- Or:
SELECT * FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'employees';