在父子关系中级联软删除的方法 [英] Method for cascading soft deletes in parent-child relationships

问题描述

我有一个简单的架构，其中使用了软删除功能（这是它的设计方式，不能更改）。有两个表参与架构：公司（id，已删除）和雇员（id，company_id，已删除）其中 company_id 当然是 Company 表的FK。规则为：

I have a simple schema in which soft deletes are used (that's how it is designed and that can't be changed). There are two tables that participate in the schema: Company (id, is_deleted) and Employee (id, company_id, is_deleted) where company_id ofcourse is a FK to the Company table. The rules are:

• 如果公司已删除 = true ，那么所有引用该公司的 Employee 应该具有 is_deleted = true 。


• 但是 Employee 可能具有 is_deleted = true ，即使父 Company 具有 is_deleted = false 。

• If a Company has is_deleted = true, then all Employee referring to that company should have is_deleted = true.
• But an Employee may have is_deleted = true even if the parent Company has is_deleted = false.

我的两个问题是：a）如何强制执行这些约束？ b）如何最简单地确保在软删除 Company 时，将 is_deleted = true 级联。

My two problems are a) how to enforce these constraints? b) how to easiest ensure that is_deleted = true is cascaded when a Company is soft-deleted.

我添加了标签postgresql和sql server，因为它们是我最感兴趣的数据库。如果其他rdbms中还有其他解决方案，我也想听听

I added the tags postgresql and sql server because those are the databases I'm mostly interested in. If there are other solutions in other rdbms:es I'd like to hear about them too.

推荐答案

严格来说，级联此类值的唯一方法是使用ON UPDATE CASCADE。要做到这一点， is_deleted列必须是唯一约束的一部分。

Strictly speaking, the only way to cascade values like that is by using ON UPDATE CASCADE. To do that, the column "is_deleted" has to be part of a unique constraint.

仅此一点也不难。如果company.id是您的主键，那么一对列{id，is_deleted}也将是唯一的。这对列上的唯一约束将使您可以通过外键引用来级联更新。

That alone isn't too hard. If company.id is your primary key, then the pair of columns {id, is_deleted} will also be unique. A unique constraint on that pair of columns would allow you to cascade updates through a foreign key reference.

但这在您的中不起作用的情况下，因为您需要允许引用的值与引用的值不同。

But that won't work in your case, because you need to allow referencing values to be different from the referenced values.

因此，在您的情况下，我认为您有三种选择。

So in your case, I think you have three options.

• 触发器


• 存储过程


• 应用程序代码

在所有这些情况下，您都需要注意权限（可能是撤消删除权限）以及可以避免代码的情况。例如，dbms命令行界面和GUI界面可用于绕开应用程序代码和存储过程中的约束，具体取决于权限。

In all those cases, you need to pay attention to permissions (probably revoking delete permissions) and to cases that can avoid your code. For example, the dbms command-line interface and GUI interface can be used to get around constraints in application code and, depending on permissions, in stored procedures.

这篇关于在父子关系中级联软删除的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！