Find tables without foreign keys in MySQL database

The query below lists all tables that do not have foreign keys.

See also:

Query

select tab.table_schema as database_name,
       tab.table_name,
       '>- no FKs' as foreign_keys
from information_schema.tables tab
left join information_schema.table_constraints fks
          on fks.table_schema = tab.table_schema
          and fks.table_name = tab.table_name
          and fks.constraint_type = 'FOREIGN KEY'
where tab.table_type = 'BASE TABLE'
      and tab.table_schema not in ('mysql', 'information_schema',
                                   'performance_schema', 'sys')
      and fks.table_name is null
     -- and tab.table_schema = 'your database name'
order by tab.table_schema,
         tab.table_name;

Columns

  • database_name - name of the database (schema)
  • table_name - name of the table
  • foreign_keys - symbol indicating the lack of FKs

Rows

  • One row: represents one table that does not have a foreign key
  • Scope of rows: all tables in a database (schema) that do not have foreign keys (do not refer to other tables)
  • Ordered by: table name

Sample results

List of tables in the Sakila database (schema) without foreign keys:

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