代码之家 › 专栏 › 技术社区 › Ian Varley

如何在SQL表中最好地执行单级递归?

constraints recursion sql

Ian Varley · 技术社区 · 17 年前

假设您的组织中有一个分支表。其中一部分是“主要”分支机构,另一部分是卫星办公室,其分支机构可以延伸到一个主要分支机构。除此之外,分支都是对等的,并且具有相同的属性(地址等),它只影响系统中的一些东西。建模的一种方法是在如下表格中:

CREATE TABLE Branch (
    branch_id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    branch_name VARCHAR(80) NOT NULL,
    street VARCHAR(80) NULL,
    city VARCHAR(30) NULL,
    state CHAR(2) NULL,
    zip CHAR(5) NULL,
    is_satellite_office BIT NOT NULL DEFAULT(0),
    satellite_to_branch_id INT NULL REFERENCES Branch(branch_id)
)

在哪里? is_satellite_office =1如果该记录是另一个分支的卫星,并且 satellite_to_branch_id 指的是你是哪个分支的卫星,如果有的话。

在表上设置一个约束就足够容易了,这样这两列就可以在任何给定的记录上达成一致:

CONSTRAINT [CK_Branch] CHECK 
  (
    (is_satellite_office = 0 AND satellite_to_branch_id IS NULL) 
    OR (is_satellite_office = 1 AND satellite_to_branch_id IS NOT NULL)
  )

然而,我真正想要的是一种方法来保证这个递归只执行一深层次…也就是说,如果我指向一个分支作为我的父分支,它就不能有父分支本身,并且它的值 是卫星办公室 必须是0。换言之,我并不真正想要一个完全递归的树结构,我只想把它限制在一个单独的父/子关系中。这就是我要写代码的方式,如果有一种方法可以在数据库中强制执行它,但却不能像完全错误那样执行,我愿意这样做。

有什么想法吗?我正在研究MSSQL2005,但一般(非特定于供应商)解决方案是首选。不需要触发,除非真的没有其他方法。

编辑:要清楚, 卫星到分支ID 是指向同一分支表中其他记录的递归指针。我知道我可以移除 is_satellite_office BIT 依靠 IsNull(satellite_to_branch_id) 给我同样的信息,但我发现更清楚一点,明确一点,除此之外,这不是问题的要点。我真的在寻找一种纯SQL约束方法来防止递归深度大于1。

4 回复 | 直到 17 年前

cmsjr 17 年前

可以将检查约束绑定到UDF的返回值。创建一个将相关列作为输入参数的UDF,然后使用UDF中的select检查所需状态。

Otávio Décio 17 年前

在我看来,这是一个业务约束,很难在数据定义级别执行。我认为关系代数不支持确定自引用深度的限制。

Kev 17 年前

是否不允许引用约束中的存储过程?你可以在PostgreSQL中,所以如果2005年不允许这样做,我会很惊讶。

JosephStyons 17 年前

这个稍有不同的结构呢?

CREATE TABLE Branch (
    branch_id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    branch_name VARCHAR(80) NOT NULL,
    street VARCHAR(80) NULL,
    city VARCHAR(30) NULL,
    state CHAR(2) NULL,
    zip CHAR(5) NULL,
    parent_id int NULL
)

父记录ID只指向同一表中另一条记录的分支ID。如果它是空的,那么你就知道它没有父母。

然后,为了得到一个递归级别,您只需将表连接到自身一次,如下所示:

SELECT
  PARENT.BRANCH_NAME AS PARENT_BRANCH
 ,CHILD.BRANCH_NAME AS CHILD_BRANCH
FROM
  BRANCH PARENT
 ,BRANCH CHILD
WHERE CHILD.PARENT_ID PARENT.BRANCH_ID

如果要在树中强制一个深度级别,请生成一个on-insert/update触发器,如果此查询返回任何内容,则会引发异常。

SELECT *
FROM
  BRANCH B1
 ,BRANCH B2
 ,BRANCH B3
WHERE B1.PARENT_ID = :NEW.NEW_PARENT_ID
  AND B2.PARENT_ID = B1.BRANCH_ID
  AND B2.PARENT_ID = B3.BRANCH_ID;