Question

What is a primary key?

Answer 1

A primary key is a column (or a set of columns) in a database table whose values uniquely identify each row. It is the table’s main identifier used for lookups, joins, and referential integrity.

Key properties
- Uniqueness: no two rows can have the same primary-key value(s).
- Not nullable: values must be present (cannot be NULL).
- Minimality: it contains no extra column — removing any column would break uniqueness.
- Stability (best practice): values should not change frequently.

Common types and examples
- Single-column primary key: id INT PRIMARY KEY
- Composite (multi-column) primary key: PRIMARY KEY (order_id, product_id)
- Surrogate key: system-generated (e.g., auto-increment integer or UUID)
- Natural key: uses real-world data (e.g., social security number), though often discouraged if it can change

SQL examples
- Create table with single key:
CREATE TABLE Person (id INT PRIMARY KEY, name VARCHAR(100));
- Composite key:
CREATE TABLE OrderItem (order_id INT, product_id INT, PRIMARY KEY (order_id, product_id));

Differences from related concepts
- Candidate key: any column(s) that could uniquely identify rows; the primary key is the chosen candidate.
- Unique constraint: enforces uniqueness but a table can have multiple unique constraints; a primary key is a single designated unique, non-null identifier.
- Foreign key: a column in one table that references the primary key of another to enforce referential integrity.

Best practices
- Prefer short, numeric surrogate keys for performance and stability.
- Avoid using mutable or business-data columns as primary keys.
- Choose a single primary key per table; use composite keys only when appropriate.