Primary Keys

Primary Keys

Primary Keys, AUTO_INCREMENT, and Generated Invisible Primary Keys

InnoDB orders each table by its primary key (Clustered Indexes) and stores that key in every secondary index, so keep it short and stable: INT UNSIGNED AUTO_INCREMENT for entities, the two foreign keys for a link table such as order_items (natural keys: Surrogate Versus Natural Keys).

A table without a primary key gets a hidden row id you cannot query or replicate by. Since MySQL 8.0.30 524 , sql_generate_invisible_primary_key adds a real one instead, my_row_id, as an invisible column (MySQL 8.0.23): SELECT * and column-less INSERTs skip it.

A gap in AUTO_INCREMENT, then a generated invisible primary keySQL

INSERT INTO customers (email, name, country) VALUES ('ivan@example.com', 'Ivan Petrov', 'BG');
INSERT INTO customers (email, name, country) VALUES ('ivan@example.com', 'Ivan P.', 'BG');
INSERT INTO customers (email, name, country) VALUES ('jo@example.com', 'Jo Park', 'KR');
SELECT id, email FROM customers WHERE id > 8;
SET SESSION sql_generate_invisible_primary_key = ON;
CREATE TABLE page_views (url VARCHAR(255) NOT NULL, viewed_at DATETIME NOT NULL);
INSERT INTO page_views VALUES ('/books/1', NOW()), ('/books/2', NOW());

Output

ERROR 1062 (23000): Duplicate entry 'ivan@example.com' for key 'customers.email'
+----+------------------+
| id | email            |
+----+------------------+
|  9 | ivan@example.com |
| 11 | jo@example.com   |
+----+------------------+

The failed insert consumed id 10, and rollbacks leave gaps too, so never use these ids as a gapless sequence such as invoice numbers. SHOW CREATE TABLE page_views lists ` my_row_id bigint unsigned NOT NULL AUTO_INCREMENT /*!80023 INVISIBLE */ ; SELECT * returned only url and viewed_at, and dropping the key failed with error 1235. sql_require_primary_key = ON` rejects keyless tables outright.

MENU