MySQL Primary Key
A primary key is the constraint that gives every row in a table a stable, unique identity, and it underpins how other tables reference that row.
What a Primary Key Guarantees
A PRIMARY KEY constraint combines UNIQUE and NOT NULL: every value in the key column (or columns) must be different from every other row's, and none of them may be NULL. A table can have at most one primary key, though that key may span multiple columns as a composite key.
Single-column primary key
CREATE TABLE students (
student_id INT PRIMARY KEY AUTO_INCREMENT,
full_name VARCHAR(100) NOT NULL
);Composite Primary Keys
When no single column is naturally unique but a combination of columns is, you can declare a composite primary key by listing more than one column inside a table-level PRIMARY KEY clause. A common example is a join table connecting two other tables, where the pair of foreign keys together identifies one row.
Composite key on a join table
CREATE TABLE enrollments (
student_id INT NOT NULL,
course_id INT NOT NULL,
enrolled_on DATE NOT NULL,
PRIMARY KEY (student_id, course_id)
);Adding a Primary Key Later
If a table was created without a primary key, one can be added afterward, provided the target column already contains unique, non-null values (or the table is empty).
Add a primary key to an existing table
ALTER TABLE students
ADD PRIMARY KEY (student_id);- MySQL automatically creates a unique index on the primary key, which also speeds up lookups by that column.
- InnoDB physically stores table rows ordered by the primary key (a clustered index), so a well-chosen key affects performance, not just correctness.
- AUTO_INCREMENT is commonly paired with an integer primary key so the database generates each new ID automatically.
