Find tables without primary keys (PKs) in MariaDB database

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

Query

select tab.table_schema as database_name,
       tab.table_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',
                                  'sys', 'performance_schema')
      and tab.table_type = 'BASE TABLE'
order by tab.table_schema,
         tab.table_name;

Columns

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

Rows

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

Sample results