Find tables without primary keys (PKs) in MySQL database

The query below lists tables in the user databases (schemas) without primary keys.


select tab.table_schema as database_name,
from information_schema.tables tab
left join information_schema.table_constraints tco
          on tab.table_schema = tco.table_schema
          and tab.table_name = tco.table_name
          and tco.constraint_type = 'PRIMARY KEY'
where tco.constraint_type is null
      and tab.table_schema not in('mysql', 'information_schema', 
                                  'performance_schema', 'sys')
      and tab.table_type = 'BASE TABLE'
--      and tab.table_schema = 'sakila' -- put schema name here
order by tab.table_schema,

Note: if you need the information for a specific database (schema), then uncomment the table_schema line and provide your database name.


  • database_name - database (schema) name
  • table_name - table name


  • One row: represents one table without primary key in a database (schema)
  • Scope of rows: all tables without primary keys in a database (schema)
  • Ordered by: database (schema) name, table name

Sample results

Sample results for all databases (schemas)

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.