如何仅删除父表中的行，该行由子表中的外键引用 [英] How to delete a row ONLY in parent table, which is referenced by a Foregin Key from the child table

问题描述

我想从父表中删除行/元组，但它抛出错误消息，因为它在其子表中有 FOREIGN KEY 引用.

I want to delete a row/tuple from a parent table, but it is throwing an error message because it has a FOREIGN KEY reference in its child table.

但是，就我而言，我想仅从父表中删除记录并在子表中维护数据.

However, in my case I want to delete the record only from the parent table and maintain the data in the child table.

有可能实现吗?

我知道 ON DELETE CASCADE，但我想知道我描述的情况是否有解决方案?

I know the usage of ON DELETE CASCADE, but I want to know if there is a solution for the secenario I described?

推荐答案

您的数据中有一些协议是可能的.要维护子表 data，您必须执行 ON DELETE SET NULL.这将留下数据，但将 FK 设置为 NULL 值(在子表中).这是因为数据完整性:虽然您可以保留您的数据，但您的 FK 不能在执行 方面引用父表中不存在的行FK 约束.因此，它将被设置为 NULL.

It is possible with some agreements in your data. To maintain child table data you'll have to do ON DELETE SET NULL. This will leave data, but set FK to NULL value (in child table). And that is because of data-integrity: while you can keep your data, your FK can not refer to non-existent row of parent table in terms of enforcing FK constraint. Thus, it will be set to NULL by this.

如果你想保存"FK 的值 - 那么你绝对不应该使用 FK，因为这样的行为违反了 FK 的本质.因此，请不要使用该约束，但要注意可能的完整性失败.

If you want to "save" value of FK - then you definitely should not use FK at all because such behavior violates what FK is. So then just don't use that constraint, but be aware of possible integrity fails.

这篇关于如何仅删除父表中的行，该行由子表中的外键引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！