ZipKit
Glossary

Primary key

Primary key is the term for a column, or group of columns, whose values uniquely identify each row in a relational table. In standard SQL the values must be both unique and not null, and a table can have at most one primary key. Other tables point at it with foreign keys, which is how rows get linked. PostgreSQL backs it with a unique B-tree index.

How it works

Declaring PRIMARY KEY adds two rules. Two rows cannot hold the same key value, and the key columns cannot be NULL. PostgreSQL documents this as equivalent to UNIQUE plus NOT NULL, and it also creates a unique B-tree index on the key columns automatically. A key that spans several columns is a composite key, and uniqueness applies to the combination.

  • Surrogate keys are generated values with no business meaning, such as an auto-incrementing integer, a UUID or a ULID. Because they carry no meaning, nothing outside the database forces them to change.
  • Natural keys come from the data itself, such as an email address or a product code. If the real-world value changes, every foreign key that copies it must change too.
  • Auto-numbering differs by engine. PostgreSQL uses GENERATED ... AS IDENTITY. In SQLite, a column declared INTEGER PRIMARY KEY becomes an alias for the 64-bit signed rowid and fills itself when you insert no value.

This run uses SQLite 3.45.1 through Python's sqlite3 module:

c.execute("CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT NOT NULL UNIQUE)")
c.execute("INSERT INTO users(email) VALUES ('a@example.com')")
c.execute("INSERT INTO users(email) VALUES ('b@example.com')")
# [(1, 'a@example.com'), (2, 'b@example.com')]
c.execute("INSERT INTO users(id,email) VALUES (1,'c@example.com')")
# IntegrityError: UNIQUE constraint failed: users.id
c.execute("DELETE FROM users WHERE id=2")
c.execute("INSERT INTO users(email) VALUES ('d@example.com')")
# [(1, 'a@example.com'), (2, 'd@example.com')]

Can a primary key be NULL?

No in the SQL standard, but SQLite is a documented exception. SQLite allows NULL in a PRIMARY KEY column unless the column is INTEGER PRIMARY KEY, the table is WITHOUT ROWID or STRICT, or you add NOT NULL. The SQLite docs call this a bug in early versions that was kept for backward compatibility. In a test with code TEXT PRIMARY KEY, two rows with a NULL key were both accepted, while the same insert into a STRICT table failed with NOT NULL constraint failed: s.code.

Common pitfalls

  • Assuming SQLite enforces NOT NULL on a text key: declare NOT NULL explicitly, or use STRICT tables, so NULL keys are rejected.
  • Reusing deleted ids: after deleting id 2, the next insert above got id 2 again. Without AUTOINCREMENT, SQLite can reuse values; if old ids appear in logs or URLs, add AUTOINCREMENT or use UUIDs.
  • Using a mutable value as the key: an email address or username looks unique but changes. Updating it means updating every foreign key. Use a surrogate key and keep the email as UNIQUE.
  • Forgetting that a composite key is one key: a row (1, 10) and (1, 11) are distinct, but a second (1, 10) fails with UNIQUE constraint failed: enr.student_id, enr.course_id.
  • Skipping the key entirely: PostgreSQL does not require a primary key, but the docs say it is usually best to have one. Without one, tools such as GUI editors cannot reliably identify a single row, and foreign keys have no default target.
  • Relying on rowid: in a SQLite table without INTEGER PRIMARY KEY, the rowid can change after VACUUM.

Related terms

  • SQL — the language where PRIMARY KEY constraints are declared.
  • SQLite — treats INTEGER PRIMARY KEY as an alias of its rowid.
  • ACID — transactions keep key constraints true even when a write fails midway.
  • UUID — a common choice for a surrogate primary key.

See also