Adsar Logo


Find MySQL tables without a primary key



When dealing with MySQL tables, it's always best to have some sort of Primary Key defined for every table, especially if you're using replication.
I've had too many times when I've caused major CPU issues by master->slave replication going mad for days trying to follow binary logging.

Anyway, to generate a list of tables without a Primary Key, use the following SQL:

SELECT 
    t.table_name, t.*
FROM INFORMATION_SCHEMA.TABLES AS t
LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c 
ON (
       t.TABLE_NAME = c.TABLE_NAME
   AND c.CONSTRAINT_SCHEMA = t.TABLE_SCHEMA
   AND c.constraint_name = 'PRIMARY'
)
WHERE 
    t.table_schema <> 'information_schema'
AND t.table_schema <> 'performance_schema'
AND t.table_schema <> 'mysql'
AND t.TABLE_TYPE = 'BASE TABLE'
AND c.constraint_name IS NULL;


Trees for life


Want to get in touch? mail@adsar.co.uk