PRIMARY KEY

This Topic's Focus

Chapter 5 covered what a primary key represents conceptually (entity integrity, the unique identifying attribute of a row). Here the focus is purely mechanical: how you declare, name, alter, and drop a PRIMARY KEY constraint in SQL DDL.

Column-Level Declaration

sql
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL
);

This inline form works only for a single-column key.

Table-Level Declaration (mandatory for composite keys)

sql
CREATE TABLE enrollments (
    student_id INT,
    course_id  INT,
    grade      CHAR(2),
    PRIMARY KEY (student_id, course_id)
);

A composite primary key made of two or more columns can only be expressed as a table-level clause — there is no way to inline it onto a single column definition.

Naming the Constraint

sql
CREATE TABLE students (
    student_id INT,
    name       VARCHAR(100) NOT NULL,
    CONSTRAINT pk_students PRIMARY KEY (student_id)
);

If you don't name it, the engine auto-generates a name (e.g. students_pkey in PostgreSQL, or a numeric-suffixed name in MySQL/SQL Server), which you'll need to look up later before you can drop it.

Adding a PRIMARY KEY to an Existing Table

sql
ALTER TABLE students ADD CONSTRAINT pk_students PRIMARY KEY (student_id);

Preconditions: the target column(s) must already be NOT NULL (or the engine implicitly makes them so) and must not currently contain duplicate values — both are validated against existing rows before the constraint is accepted.

Dropping / Replacing a PRIMARY KEY

sql
-- PostgreSQL / Oracle / SQL Server
ALTER TABLE students DROP CONSTRAINT pk_students;

-- MySQL (primary keys are unnamed at the engine level)
ALTER TABLE students DROP PRIMARY KEY;

MySQL's dedicated DROP PRIMARY KEY syntax exists precisely because MySQL doesn't let you assign an arbitrary custom name to the primary key the way other engines do — it's always identified as the primary key of the table, singular.

Edge Cases

• You cannot have two PRIMARY KEY clauses in one CREATE TABLE — attempting it is a syntax/semantic error, unlike UNIQUE where multiple are fine.
• Auto-increment/identity columns (AUTO_INCREMENT in MySQL, SERIAL/GENERATED ALWAYS AS IDENTITY in Postgres, IDENTITY in SQL Server) are commonly paired with PRIMARY KEY but are logically independent features — you can have one without the other.
• Changing which column(s) make up the primary key later requires dropping the old constraint and adding a new one; there's no direct "ALTER PRIMARY KEY" rename-in-place across all engines.

Key Takeaways / Q&A

Q: Can a composite primary key be declared with column-level syntax? A: No — any key spanning more than one column requires the table-level PRIMARY KEY (col1, col2, ...) clause.

Q: What must be true of a table's data before `ALTER TABLE ... ADD PRIMARY KEY` succeeds? A: The target columns must contain no NULLs and no duplicate combinations — the engine validates this against existing rows first.

Q: How do you drop an unnamed primary key in MySQL vs PostgreSQL? A: MySQL uses ALTER TABLE t DROP PRIMARY KEY directly; PostgreSQL requires you to know (or look up) the auto-generated constraint name and use DROP CONSTRAINT.