tables_without_pk
CRITICAL
Rule IDs: sch_001
Overview
- Purpose
- Schema-quality signal: tables lacking a primary key, usually from summarized metadata rather than a single global variable.
- Dynamic (MySQL 8.4 reference)
- Change by altering table definitions (add a primary key).
- Default value
- N/A
- Version and product notes
- MySQL 8.4 InnoDB encourages a primary key; behavior differs by engine.
- Documentation
- Primary reference depends on product (MySQL, MariaDB, Percona, or OS documentation).
What is checked
Rules that reference this variable, with their severity and what each rule detects:
- CRITICAL
sch_001: Add primary keys to all InnoDB tables. Tables without PK cause full table scans during row-based replication.
Tuning guidance
- Recommended actions:
- Add primary keys to all InnoDB tables. Tables without PK cause full table scans during row-based replication.
- Trade-offs: Schema issues (missing PKs, MyISAM tables) affect replication, backup, and InnoDB efficiency. Fixing schema requires application-level changes and testing, not just variable changes.
Example
ALTER TABLE my_table ADD COLUMN id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY;