按外键排序数据库表

• 下载源代码 - 6.26 KB

背景

我一直在研究新的 Microsoft 同步框架，特别是数据库提供程序。

Visual Studio 2008 有一个新的项目项类型，“本地数据库缓存”，不要与“本地数据库”混淆，后者用于 SQL Server Compact 版数据库同步。紧凑型数据库不允许外键关系；因此，它不适合我的需求。虽然微软正在努力开发 Express 版本的设计器，但我继续进行。

从主表检索的数据需要按照不会违反外键约束的顺序检索。我们的理解是，我们正在沿着软件开发的顺利路径进行工作。同步框架将处理由于其他问题（不涉及检索数据的顺序）而导致的错误。

本文的目的是提供一种根据数据库中存在的外键关系对数据库表进行排序的方法。

排序算法

以下是 Microsoft 的 AdventureWorks 示例数据库的一部分。该图显示了一些表之间的基本字段和外键关系。

在尝试对表进行排序四次之后，我提出了一种我认为效果很好的算法。它基于以下原则：每个表都属于以下类别之一

1. 无 – 表不被其他表引用，也不引用其他表。
2. 被引用 – 表被其他表引用。
3. 两者 – 表被表引用并引用其他表。
4. 引用 – 表只引用其他表。

下图显示了上面数据库表的图片，使用表的类表示法，组织成四个分类

在每个类别中，表按名称排序，因为它们与类别中的任何其他表都没有关系；例外是类别 3（两者）。类别 3 需要进一步排序，说明哪个表引用其他表。

下图表示类别 3 中表的最终排序顺序 (D、B、E、C)。如果查看类的箭头，您会注意到所有箭头都从右向左流动。这是我们执行排序时要寻找的流程。

鉴于上述 AdventureWorks 数据库表和类图，表应按以下顺序排序

1. dbo.ErrorLog
2. Sales.SalesTerritory
3. HumanResources.Employee
4. Sales.SalesPerson
5. Sales.SalesOrderHeader
6. Sales.Store
7. Sales.SalesPersonQuotaHistory
8. Sales.SalesTerritoryHistory

Using the Code

该项目被分解成以下文件

1. Program.cs – 主驱动程序并打印排序结果。
2. TableInfo.cs – 检索数据库信息，创建表关系并执行排序。
3. TableInfoComparer.cs – 比较算法。

表信息使用以下两个 SQL 脚本从 SQL Server 检索

检索当前数据库中的完全限定表名

SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS TableName
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE='BASE TABLE'
ORDER BY TableName

检索表之间的关系

SELECT  
    OBJECT_SCHEMA_NAME (fkey.referenced_object_id) + '.' +  
    OBJECT_NAME (fkey.referenced_object_id) AS ReferenceTableName 
    ,COL_NAME(fcol.referenced_object_id, 
              fcol.referenced_column_id) AS ReferenceColumnName 
    ,OBJECT_SCHEMA_NAME (fkey.parent_object_id) + '.' +  
    OBJECT_NAME(fkey.parent_object_id) AS TableName 
    ,COL_NAME(fcol.parent_object_id, fcol.parent_column_id) AS ColumnName 
FROM sys.foreign_keys AS fkey 
    INNER JOIN sys.foreign_key_columns AS fcol ON 
               fkey.OBJECT_ID = fcol.constraint_object_id 
ORDER BY ReferenceTableName, ReferenceColumnName, TableName, ColumnName