当前位置：文江博客话题详情

检查递归外键的方法

发布于 2024-11-09 19:08:38 字数 521 浏览 5 评论 0原文

假设我有一个名为 EMPLOYEE 的表，该表有一个名为 ManagerID 的字段。该字段是 EMPLOYEE 表中另一条记录（其经理是谁）的 FK。验证不存在循环引用（递归外键）的理想方法是什么？

编辑 - 让我补充一下，每个员工并不需要经理。将会有一些员工记录未设置其 ManagerID 字段。

*示例：用户 123 将用户 345 作为其经理。用户 345 有用户 567 作为其经理。由于某些疯狂的原因，用户 567 将用户 123 作为其管理员。

我想检查用户 345 是否处于循环引用中。*

解决此问题的最佳方法是什么？一个简单的查询？业务逻辑代码（C#/VB.NET）？

其他人做了什么？

示例表架构 -

-- Employee Table --
UserID (int - PK)
ManagerID (int - FK to another UserID; can be null)

原文

Say I have a table named EMPLOYEE, and the table has a field called ManagerID. This field is a FK to another record of the EMPLOYEE table of who their manager is. What is an ideal way to verify that there is no circular reference (recursive foreign keys)?

EDIT - Let me add that every employee does NOT need a manager. There will be some employee records that do not have their ManagerID field set.

*Example: User 123 has User 345 as their manager. User 345 has User 567 as their manager. For some crazy reason, User 567 has User 123 as their manager.

I want to check if User 345 is in a circular reference.*

What is the best way to figure this out? A simple query? Business logic code (C#/VB.NET)?

What have other people done?

Example table schema -

-- Employee Table --
UserID (int - PK)
ManagerID (int - FK to another UserID; can be null)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

無心 2024-11-16 19:08:38

这里有一些入门者 - 他们要么使用递归 CTE 要么使用游标：

http://blogs.msdn.com/b/sqlazure/archive/2010/07/01/10033575.aspx

http://social.msdn.microsoft.com/Forums/en-ZA/transactsql/thread/50a0bd26-87c8-4197-84f9-5fb1dfd792b6

回复收藏 0 原文

谎言月老 2024-11-16 19:08:38

如果您正在使用 SQL Server，这里是符合您要求的解决方案；
需要编写CTE来查找循环引用；

下面的查询将为您提供具有循环引用的用户 ID

WITH TRACKER AS
(


SELECT U.id,U.manager_id,CAST(','+cast(U.id as varchar(36))+',' as varchar(max)) as Path,0 as Cycle FROM User_table AS U
    UNION ALL
    SELECT U.id,U.manager_id, TRACKER.Path + CAST(U.id AS varchar(36))+',', case WHEN TRACKER.Path LIKE '%,'+CAST(U.id AS VARCHAR(36)) + ',%' then 1 else 0 end   FROM User_table AS U

 INNER JOIN TRACKER ON U.manager_id = TRACKER.id WHERE TRACKER.Cycle=0 
)

SELECT TRACKER.id from TRACKER where Cycle= 1;

If you are dealing with SQL server, here is the solution that matches your requirement;
You need to write CTE to find cyclic references;

Below query will give you user IDs having cyclic reference

WITH TRACKER AS
(


SELECT U.id,U.manager_id,CAST(','+cast(U.id as varchar(36))+',' as varchar(max)) as Path,0 as Cycle FROM User_table AS U
    UNION ALL
    SELECT U.id,U.manager_id, TRACKER.Path + CAST(U.id AS varchar(36))+',', case WHEN TRACKER.Path LIKE '%,'+CAST(U.id AS VARCHAR(36)) + ',%' then 1 else 0 end   FROM User_table AS U

 INNER JOIN TRACKER ON U.manager_id = TRACKER.id WHERE TRACKER.Cycle=0 
)

SELECT TRACKER.id from TRACKER where Cycle= 1;

回复收藏 0 原文

~没有更多了~