如何以依赖关系顺序列出表（基于外键）？ [英] How to list tables in their dependency order (based on foreign keys)?

问题描述

这个问题最初是由@PrateekGupta提出的。

---

背景

@PrateekGupta希望对多个表执行批量插入操作。

表之间具有外键关系。

如果对具有外部表的表执行INSERT操作

需求

在数据库中生成一个表列表，这些列表根据它们的依赖关系排序。

没有依赖关系（没有外键）的表将是第一个。

仅在依赖关系中的表第一组表将是第二组。

仅在第一组或第二组表中具有依赖性的表将是第三组。

依此类推...

解决方案

 示例：
 
创建表t1（i int主键，j int唯一） ）
创建表t2（i int prima ry键参考文献t1（i））； 
创建表t3（i int，j int，主键（i，j））; 
创建表t4（i int，j int，外键（i，j）引用t3（i，j））; 
创建表t5（i int引用t1（i），j int，外键（i，j）引用t3（i，j））; 
创建表t6（i int引用t2（i））; 
  

---

  cte（lvl，object_id，name）
 as 
（
从sys.tables $ b中选择1 
，object_id 
，name 
 
 
 $ b其中type_desc ='USER_TABLE'
和is_ms_shipped = 0 
 
并集所有
 
选择cte.lvl + 1 
，t .object_id 
，t.name 
 from cte 
 
加入sys.tables作为t 
 
存在
（
 select来自sys.foreign_keys的null 
 
 as fk 
 
其中fk.parent_object_id = t.object_id 
和fk.referenced_object_id = cte.object_id 
）
 
和t.object_id<> cte.object_id 
和cte.lvl< 30 
 
其中t.type_desc ='USER_TABLE'
和t.is_ms_shipped = 0 
）
 
 
选择名称
 ，max（lvl）作为cte 
 
组中的Dependent_level 
 
，按名称
 
的订单，依dependency_level 
，名称
； 
  

This question was originally asked by @PrateekGupta

---

Background

@PrateekGupta wanted to perform bulk insert operation on multiple tables.
The tables have foreign key relationships between themselves.
If an INSERT operation is done on a table with a foreign key before the referenced table is being inserted to, the operation might fail due to violation of the foreign key.

Requirement

Produce a list of tables within a database ordered according to their dependencies.
Tables with no dependencies (no foreign keys) will be 1st.
Tables with dependencies only in the 1st set of tables will be 2nd.
Tables with dependencies only in the 1st or 2nd sets of tables will be 3rd.
and so on...

解决方案

    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
;

这篇关于如何以依赖关系顺序列出表（基于外键）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！