PRIMARY KEY

The column or columns that identify a row, plus AUTO_INCREMENT and what InnoDB's clustered index means for your choice.

Syntax[CONSTRAINT [symbol]] PRIMARY KEY (col_name, ...)

A PRIMARY KEY names the column or columns that identify a row uniquely. It is a unique index with an extra rule: the key columns cannot be NULL. A table may have at most one.

InnoDB stores the table in primary key order

In InnoDB the primary key is the clustered index — the rows themselves live in the leaves of that index, physically ordered by the key. Two consequences follow. Lookups by primary key fetch the row in one index traversal. And every secondary index entry carries a copy of the primary key value, because that is how a secondary index finds the row; the manual's advice is direct: "If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key."

Define one. If you do not, InnoDB takes the first UNIQUE index whose columns are all NOT NULL; failing that it generates a hidden index called GEN_CLUST_INDEX over a 6-byte row ID you cannot query or use.

Shorthand and longhand

id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY and the table-level PRIMARY KEY (id) produce the same key. Only the table-level form can list several columns, which is how you declare a composite key: PRIMARY KEY (order_id, product_id).

AUTO_INCREMENT

An AUTO_INCREMENT column must be indexed. Insert NULL or 0 and the server assigns the next value; LAST_INSERT_ID() returns it for your connection only. In MySQL 8.4 the counter is persisted across server restarts.

Example

Example · sql
CREATE TABLE customers (
  -- Shorthand: AUTO_INCREMENT surrogate key declared on the column
  id         BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  email      VARCHAR(255)    NOT NULL,
  created_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  -- The natural key still deserves a UNIQUE constraint
  UNIQUE KEY uniq_customers_email (email)
) ENGINE = InnoDB;

INSERT INTO customers (email) VALUES ('[email protected]');
SELECT LAST_INSERT_ID();  -- the id just assigned, on this connection only

-- Longhand equivalent of the id line above:
--   id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
--   PRIMARY KEY (id)

When to use it

  • Give a new table a stable identifier that foreign keys can point at.
  • Stop duplicate rows in a many-to-many join table with a composite key.
  • Diagnose why a table with no primary key is slow and hard to replicate.

More examples

A composite primary key on a join table

The composite key replaces both a surrogate id and a separate UNIQUE constraint, and it clusters every line of an order together on disk. Note the extra index on product_id: a composite key only helps queries that filter on its leading column, so the second column needs its own index for reverse lookups.

Example · sql
CREATE TABLE order_items (
  order_id   BIGINT UNSIGNED NOT NULL,
  product_id BIGINT UNSIGNED NOT NULL,
  quantity   INT UNSIGNED    NOT NULL DEFAULT 1,
  unit_price DECIMAL(10,2)   NOT NULL,
  -- One row per product per order, enforced by the key itself
  PRIMARY KEY (order_id, product_id),
  -- The PK's leading column covers order_id lookups; product_id needs its own
  KEY idx_order_items_product (product_id),
  CONSTRAINT fk_items_order   FOREIGN KEY (order_id)   REFERENCES orders (id),
  CONSTRAINT fk_items_product FOREIGN KEY (product_id) REFERENCES products (id)
) ENGINE = InnoDB;

Why a random primary key hurts, and the fix

Reach for this when an external system supplies ids and AUTO_INCREMENT is not an option. UUID_TO_BIN(uuid, 1) rearranges the time fields so that generated values increase, which keeps inserts appending near the end of the clustered index; the second argument is a swap flag, and BIN_TO_UUID must be given the same flag to decode correctly.

Example · sql
-- Problem: a 36-character text UUID as the clustered key.
-- Every secondary index row carries all 36 bytes, and inserts land
-- at random points in the clustered index instead of at the end.
CREATE TABLE sessions_bad (
  id CHAR(36) NOT NULL PRIMARY KEY,   -- '9f8b...' as text
  account_id BIGINT UNSIGNED NOT NULL,
  KEY idx_account (account_id)        -- each entry = account_id + 36 bytes
);

-- Better: keep the UUID as a 16-byte binary value, swapped so that
-- time-ordered UUIDs sort in insertion order.
CREATE TABLE sessions (
  id         BINARY(16) NOT NULL DEFAULT (UUID_TO_BIN(UUID(), 1)) PRIMARY KEY,
  account_id BIGINT UNSIGNED NOT NULL,
  KEY idx_account (account_id)
);

SELECT BIN_TO_UUID(id, 1) AS id FROM sessions;  -- read it back as text

Adding a primary key to a table that never had one

Useful during an audit of an inherited schema. A table without a primary key is stored on InnoDB's hidden GEN_CLUST_INDEX, which nothing can query, and row-based replication has to scan the whole table to locate each changed row — so the fix is worth the rebuild.

Example · sql
-- Check first: this returns the tables with no PRIMARY KEY
SELECT t.table_name
FROM information_schema.tables t
LEFT JOIN information_schema.table_constraints c
  ON  c.table_schema = t.table_schema
  AND c.table_name   = t.table_name
  AND c.constraint_type = 'PRIMARY KEY'
WHERE t.table_schema = DATABASE()
  AND t.table_type   = 'BASE TABLE'
  AND c.constraint_name IS NULL;

-- Adding one rebuilds the table. Note that adding an AUTO_INCREMENT
-- column does NOT permit concurrent DML, so LOCK = NONE is rejected:
-- ALGORITHM = INPLACE, LOCK = SHARED (reads only) is the minimum.
ALTER TABLE event_log
  ADD COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT FIRST,
  ADD PRIMARY KEY (id),
  ALGORITHM = INPLACE, LOCK = SHARED;

Related

sql create tablesql keyssql uniquesql index introsql foreign key

Discussion

  • Be the first to comment on this lesson.