A primary key is a column (or combination of columns) whose values exist — they cannot be NULL — and are unique across the table. The primary key is what lets us identify a single row out of the whole table. If the table is students, the primary key is whatever lets us point at exactly one student — usually a student ID number, sometimes a synthetic id column added specifically for the purpose.
Two students can have the same first name, the same family name, even the same birthdate. They cannot have the same primary key. That’s the constraint the database enforces.
A table has at most one primary key. It can have additional UNIQUE constraints — an email column might also be unique — but only one column (or combination) is designated as the primary key, and only the primary key plays the special role of being what foreign keys reference by default. A primary key implies UNIQUE plus NOT NULL plus this referential-target role; a bare UNIQUE constraint allows NULL values and isn’t the default target of foreign keys. Treating “PK” and “unique constraint” as synonyms misses the distinction.
In SQL, a primary key is declared either inline with a column or with a PRIMARY KEY (...) clause at the end of the CREATE TABLE:
CREATE TABLE students (
student_id INT NOT NULL,
first_name VARCHAR(255),
family_name VARCHAR(255),
PRIMARY KEY (student_id)
);If we later try to insert a row with a student_id that already exists, the database refuses:
Error Code: 1062. Duplicate entry '20201456' for key 'students.PRIMARY'
That error is the database doing its job. We asked it to enforce uniqueness on the primary key, and when we tried to violate the constraint, it stopped us. Without it, we’d silently end up with two rows for the same student, and every later query that joined to this table would return duplicate results.
The primary key has a counterpart: a Foreign key is a column in another table whose values are required to exist in this table’s primary key. Together, primary keys and foreign keys are what enforce referential integrity — the guarantee that every reference can be followed to a real row.
A primary key is one of several kinds of SQL constraint, alongside NOT NULL, UNIQUE, CHECK, and FOREIGN KEY. (AUTO_INCREMENT is a column attribute rather than a constraint, and its spelling is dialect-specific — MySQL uses AUTO_INCREMENT, PostgreSQL SERIAL or IDENTITY, SQLite AUTOINCREMENT, SQL Server IDENTITY.)