sql - 冲突的 CASCADE 和 RESTRICT 外键要求？

Question

我正在开发一个数据库，用于跟踪项目中的文件和依赖项。简而言之，我有两个主表； PROJECTS 表列出项目名称和其他属性，FILES 表列出文件。每个文件条目都指向一个项目，作为外键设置为 CASCADE，因此如果我从数据库中删除一个项目记录，所有文件记录也会消失。到目前为止，还不错。

现在我有一个额外的 DEPENDENCIES 表。依赖表中的每条记录都是两个文件，指定第一个文件依赖于第二个文件。同样，这些是外键，第一个设置为 CASCADE(因此，如果我删除一个文件条目，则该记录将被删除)，但第二个设置为 RESTRICT(如果有任何其他文件依赖，则不允许我删除文件条目在上面)。同样，一切似乎都很好。

不幸的是，我似乎无法再使用单个 SQL 删除语句删除项目!删除尝试级联删除文件，但如果其中任何一个出现在 DEPENDENCIES 表中，RESTRICT 外键会阻止删除(即使依赖项表中的该记录将被删除，因为另一列是 CASCADE)。我唯一的解决方法是计算删除文件的确切顺序，这样就不会违反任何依赖项记录约束，并在尝试删除项目之前一次删除一个文件记录。

有什么方法可以设置我的数据库架构，以便从项目表中删除单个 SQL 可以正确地级联其他删除吗？我使用的是 Firebird 2.1，但我不知道这是否有任何区别 - 似乎应该有一种方法可以使它正常工作？

Answer 1

您无法通过级联外键控制删除顺序，但您可以在 PROJECTS 上设计一个触发器来删除 FILES 中属于到这个项目，并且也列在 DEPENDENCIES 中作为依赖于其他 FILES。使它成为 BEFORE DELETE 触发器，因此它应该在级联效果之前执行。

像这样:

CREATE TRIGGER Del_Child_Files FOR PROJECTS
BEFORE INSERT
AS BEGIN
  FOR SELECT F.FILE_ID FROM FILES F JOIN DEPENDENCIES D 
      ON F.FILE_ID = D.CHILD_ID
    WHERE F.PROJECT_ID = OLD.PROJECT_ID
    INTO :file_id
  DO
    DELETE FROM FILES WHERE FILE_ID = :file_id;
  DONE
END

因此，当您删除一个项目时，这将删除该项目依赖于其他文件的所有“子”文件，并且这会级联删除 DEPENDENCIES 中的行，因此所有剩余文件都是免费的的依赖关系。您删除项目现在可以级联删除这些文件。

我还没有测试过这个，我的 Firebird 语法可能生锈了，但也许它会让你开始。

显然，请在您的数据副本而不是实时数据上进行测试!