Как составить список таблиц в порядке их зависимости (на основе внешних ключей)?

Этот вопрос был изначально задан @PrateekGupta

Фон

@PrateekGupta хотел выполнить массовую операцию вставки для нескольких таблиц.
Таблицы имеют отношения внешнего ключа между собой.
Если операция INSERT выполняется для таблицы с внешним ключом до вставки в нее ссылочной таблицы, операция может завершиться ошибкой из-за нарушения внешнего ключа.

Reuierment

Создайте список таблиц в базе данных, упорядоченный в соответствии с их зависимостями.
Таблицы без зависимостей (без внешних ключей) будут 1-ыми.
Таблицы с зависимостями только в первом наборе таблиц будут вторыми.
Таблицы с зависимостями только в первом или втором наборах таблиц будут третьими.
и так далее...

Источник

1 ответ

Решение
    example:

    create table t1 (i int primary key,j int unique)
    create table t2 (i int primary key references t1 (i));
    create table t3 (i int,j int,primary key (i,j));
    create table t4 (i int,j int,  foreign key (i,j) references t3 (i,j));
    create table t5 (i int references t1 (i),j int,foreign key (i,j) references t3 (i,j));
    create table t6 (i int references t2 (i));

with        cte (lvl,object_id,name)
            as 
            (
                select      1
                           ,object_id
                           ,name

                from        sys.tables

                where       type_desc       = 'USER_TABLE'
                        and is_ms_shipped   = 0

                union all

                select      cte.lvl + 1
                           ,t.object_id
                           ,t.name
                from                    cte

                            join        sys.tables  as t

                            on          exists
                                        (
                                            select      null

                                            from        sys.foreign_keys    as fk

                                            where       fk.parent_object_id     = t.object_id 
                                                    and fk.referenced_object_id = cte.object_id
                                        )

                                    and t.object_id <> cte.object_id
                                    and cte.lvl < 30

                where       t.type_desc     = 'USER_TABLE'      
                        and t.is_ms_shipped = 0
            )


select      name
           ,max (lvl)   as dependency_level

from        cte

group by    name

order by    dependency_level
           ,name
;
Источник

Приведенные выше ответы не будут работать с круговыми ссылками. Вместо этого вы можете использовать эту хранимую процедуру.

EXEC sp_msdependencies @flags = 8

Посмотреть варианты флага можно здесь

EXEC sp_msdependencies '?'

К сожалению, это недоступно в SQL Azure.

Источник

Спасибо за Дэвида.

Я только что добавил имя схемы к его запросу, если кому-то нужно

WITH cte (lvl, object_id, name, schema_Name) AS
  (SELECT 1, object_id, sys.tables.name, sys.schemas.name as schema_Name
   FROM sys.tables Inner Join sys.schemas on sys.tables.schema_id = sys.schemas.schema_id
   WHERE type_desc = 'USER_TABLE'
     AND is_ms_shipped = 0
   UNION ALL SELECT cte.lvl + 1, t.object_id, t.name, S.name as schema_Name
   FROM cte
   JOIN sys.tables AS t ON EXISTS
     (SELECT NULL FROM sys.foreign_keys AS fk
      WHERE fk.parent_object_id = t.object_id
        AND fk.referenced_object_id = cte.object_id )
   JOIN sys.schemas as S on t.schema_id = S.schema_id
   AND t.object_id <> cte.object_id
   AND cte.lvl < 30
   WHERE t.type_desc = 'USER_TABLE'
     AND t.is_ms_shipped = 0 )
SELECT schema_Name, name, MAX (lvl) AS dependency_level
FROM cte
GROUP BY schema_Name, name
ORDER BY dependency_level,schema_Name, name;
Источник

Версия скрипта для Oracle:

with 
    foreign_keys as (
        select
          src_cc.owner as src_owner,
          src_cc.table_name as src_table,
          src_cc.column_name as src_column,
          dest_cc.owner as dest_owner,
          dest_cc.table_name as dest_table,
          dest_cc.column_name as dest_column,
          c.constraint_name
        from
          all_constraints c
        inner join all_cons_columns dest_cc on
          c.r_constraint_name = dest_cc.constraint_name
          and c.r_owner = dest_cc.owner
        inner join all_cons_columns src_cc on
          c.constraint_name = src_cc.constraint_name
          and c.owner = src_cc.owner
        where
          c.constraint_type = 'R'
          and dest_cc.owner = :owner),
    cte (lvl,table_name) as (
        select 1, table_name
        from all_tables
        where 
            owner = :owner
        union all
        select cte.lvl + 1, t.table_name
        from cte
        join all_tables t on exists (
            select null 
            from foreign_keys fk 
            where 
                fk.src_table = t.table_name
                and fk.dest_table = cte.table_name
        )
        and t.table_name <> cte.table_name
            AND cte.lvl < 30
    )
select table_name, max (lvl)   as dependency_level
from        cte
group by    table_name
order by    dependency_level desc, table_name
;
Источник

Мне нужно было сделать это самому, и я надеялся, что кто-то уже сделал это для Postgres, но ничего не нашел, поэтому я просто оставлю это здесь:

WITH RECURSIVE t AS (
    SELECT relnamespace as nsp, oid as tbl, null::regclass as source, 1 as level
    FROM pg_class
    WHERE relkind = 'r'
        AND relnamespace not in ('pg_catalog'::regnamespace, 'information_schema'::regnamespace)
UNION ALL
    SELECT c.connamespace as nsp, c.conrelid as tbl, c.confrelid as source, p.level + 1
    FROM pg_constraint c
    INNER JOIN t p ON (c.confrelid = p.tbl AND c.connamespace = p.nsp)
    WHERE c.contype = 'f'
        AND c.connamespace not in ('pg_catalog'::regnamespace, 'information_schema'::regnamespace)
)
SELECT nsp::regnamespace, tbl::regclass
FROM t
GROUP BY nsp, tbl
ORDER BY max(level) DESC;

Это своего рода гибридный запрос между двумя вариантами использования. Вы можете удалитьGROUP BY а также SELECT source::regclass вместо этого, если вам нужно посмотреть, к какой таблице относится внешний ключ.

Источник
Другие вопросы по тегам