Find tables without relationships - Loner Tables - in MariaDB database

This query listed tables that do not have foreign keys, which means that they are not referencing any table or that they are not on the "many" side of FK.

The query below lists something a little different - tables that are not referencing and are not referenced by other tables. Something that can be called as Loner Tables. This diagram illustrates the concept:

Learn more about Loner Tables

Query

select 'No FKs >-' as refs,
       concat(tab.table_schema, '.', tab.table_name) as 'table',
       '>- no FKs' as fks
from information_schema.tables tab
left join information_schema.referential_constraints ref
          on tab.table_schema = ref.constraint_schema
          and tab.table_name = ref.table_name
left join information_schema.referential_constraints ref_by
          on tab.table_schema = ref_by.unique_constraint_schema
          and tab.table_name = ref_by.referenced_table_name
where ref.constraint_name is null
      and ref_by.constraint_name is null
      and tab.table_type = 'BASE TABLE'
      and tab.table_schema not in ('mysql', 'information_schema',
                                   'performance_schema', 'sys')
     -- and tab.table_schema = 'your database name'
order by tab.table_schema,
         tab.table_name;

Columns

  • refs - icon that symbolizes the lack of references by foreign key constraints
  • table - name of the table
  • fks - icon that symbolizes the lack of foreign key constraints

Rows

  • One row: represents one table
  • Scope of rows: tables that are not related (not referencing and not being referenced with foreign key constraints) with any table
  • Ordered by: table name

Sample results

List of Loner Tables in the Factories database (schema):

0
There are no comments. Click here to write the first comment.