Find tables without relationships - Loner Tables - in MySQL database

Bart Gawrych - Dataedo Team Bart Gawrych 2019-02-04

Table of Contents:


    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.

    By continuing to use our website, you consent to the use of cookies in accordance with our Privacy Policy (updated 28-04-2020).
    Accept