Find Tables Without a Primary Key in MySQL

Lists tables that have no primary key. Such tables cause replication lag and break Group Replication/Galera.

#primary-key#replication#schema

mysql · sql

SELECT t.table_schema, t.table_name, t.engine, t.table_rows
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_type = 'BASE TABLE'
  AND t.table_schema NOT IN ('mysql','sys','information_schema','performance_schema')
  AND c.constraint_name IS NULL
ORDER BY t.table_rows DESC;

Operational Notes & Remediation Guidance

Verify in a staging environment prior to running in production. This is a read-only diagnostic query; it does not acquire exclusive locks.

Production Safety & Execution Standards

This script is designed for read-only diagnostics; it does not alter schema or modify data directly.
Prior to executing on production systems under high load, verify the target database version (Min. Version: 5.7).
Exercise caution with mutating actions (e.g., terminating sessions or dropping indexes) referenced in the notes section.

Other MYSQL Diagnostic Scripts

View All (100)

#21Performance

Find Long-Running Queries in MySQL

Lists active queries running longer than 60 seconds from the processlist. Fast diagnosis for slowdowns and lock pile-ups.

View Script

#22Locking

Find MySQL InnoDB Lock Waits and the Blocking Query

Uses the sys schema to show waiting and blocking sessions with lock modes. Resolves who is holding up whom in a single query.

View Script

#23Storage

List the Largest Tables and Disk Usage in MySQL

Ranks tables by data, index and free space size. Core input for capacity planning and archiving decisions.

View Script

#24Performance

Detect Unused Indexes in MySQL

Uses sys.schema_unused_indexes to list indexes that were never used. Helps safely drop indexes that slow down writes.

View Script