在PostgreSQL中删除具有外键的行

Question

我想删除包含外键的行，但是当我尝试像这样做时：

DELETE FROM osoby WHERE id_osoby='1'

我收到了以下声明:

错误: 更新或删除“osoby”表上的行违反了“kontakty_ibfk_1”表对“kontakty”上的外键约束 细节: 由于表“kontakty”中仍引用了键（id_osoby）=（1），因此需要注意。

我如何删除这些行？

- Michal Loksik

---

也要查看这个 [on delete cascade] (https://dev59.com/HGkv5IYBdhLWcg3wxjvQ) ;) 在表中设置这些选项很有必要...在创建外键时我们会先添加父级再添加子级，所以在删除时我们需要先删除子级，再删除父级 ;) - bonCodigo

Answer 1

为了自动化这个过程，你可以使用 ON DELETE CASCADE 定义外键约束。
引用外键约束手册中的描述：

CASCADE 表示当参考行被删除时，引用它的行也应该被自动删除。

像这样查找当前的 FK 定义：

SELECT pg_get_constraintdef(oid) AS constraint_def
FROM   pg_constraint
WHERE  conrelid = 'public.kontakty'::regclass  -- assuming public schema
AND    conname = 'kontakty_ibfk_1';

然后在语句中添加或修改 ON DELETE ... 部分为 ON DELETE CASCADE （保留其他所有内容），例如：

ALTER TABLE kontakty
   DROP CONSTRAINT kontakty_ibfk_1
 , ADD  CONSTRAINT kontakty_ibfk_1
   FOREIGN KEY (id_osoby) REFERENCES osoby (id_osoby) ON DELETE CASCADE;

没有ALTER CONSTRAINT命令。为了避免可能的并发写访问竞争条件，您需要在单个ALTER TABLE语句中删除并重新创建约束。

显然您需要相应的权限。该操作将在表kontakty上获取一个ACCESS EXCLUSIVE锁和在表osoby上获取一个SHARE ROW EXCLUSIVE锁。

如果您无法对表进行ALTER，那么手动删除（一次）或使用BEFORE DELETE触发器（每次）是剩下的选项。

- Erwin Brandstetter

---

我们没有创建带有ON DELETE CASCADE的表。我不能更改表结构。是否有一种方式可以自动删除外键？或者我们必须按照@juergen d建议的方法进行操作？ - वरुण

@Varun：如果您无法使用ON DELETE CASCADE添加FK来更改表，则手动删除（一次）或使用BEFORE DELETE触发器（每次）是剩下的选项。 - Erwin Brandstetter

很好，所以答案是在创建语句中的具有外键的列上添加 ON DELETE CASCADE。 - 425nesp

@425nesp：我添加了明确的指示。 - Erwin Brandstetter

在我的情况下，我需要 DDL 来查看确切的名称约束，在这种情况下它将是\d kontakty。 - marquicus

这仍然是我认为最好的答案。唯一的问题是在使用 IDS 时有点棘手。它可能会删除所有具有相同 ID 的所需和不需要的行。 - pythonGo

Answer 2

不建议将此作为通用解决方案，但如果需要删除未在生产环境或活跃使用中的数据库中的某些行，您可以尝试临时禁用涉及表格的触发器。

在我的情况下，我处于开发模式，并且有几个通过外键相互引用的表格。因此，在删除其中一个表格的所有行之前，删除它们的内容并不是很简单。所以，对我来说，按照以下方式删除它们的内容可以正常工作：

ALTER TABLE table1 DISABLE TRIGGER ALL;
ALTER TABLE table2 DISABLE TRIGGER ALL;
DELETE FROM table1;
DELETE FROM table2;
ALTER TABLE table1 ENABLE TRIGGER ALL;
ALTER TABLE table2 ENABLE TRIGGER ALL;

当然，您应该能够根据需要添加WHERE子句，并小心避免破坏数据库的完整性。

在http://www.openscope.net/2012/08/23/subverting-foreign-key-constraints-in-postgres-or-mysql/上有一些相关的好讨论。

- Andrew Basile

---

死链的存档版本：https://web.archive.org/web/20160922175428/http://www.openscope.net/2012/08/23/subverting-foreign-key-constraints-in-postgres-or-mysql/。（建议编辑队列已满，所以我没有足够的声望来自己编辑） - Joakim

当需要为测试截取一个巨大表格的一部分（例如1年）时，这非常有帮助！ - sunsetjunks

Answer 3

如果外键仍然引用另一个表，则无法删除该外键。 首先删除引用。

delete from kontakty
where id_osoby = 1;

DELETE FROM osoby 
WHERE id_osoby = 1;

- juergen d

Answer 4

这个问题已经有一段时间了，希望可以帮到您。由于您不能更改或修改数据库结构，可以按照PostgreSQL 文档进行操作。

TRUNCATE -- 清空一个表或一组表。

TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ]
    [ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]

描述

TRUNCATE快速地从一组表中删除所有行。它与每个表上未有限定符的DELETE具有相同的效果，但由于它实际上不扫描表格，因此更快。此外，它会立即回收磁盘空间，而不需要随后的VACUUM操作。这在大型表格上最为有用。

---

截断othertable表，并级联到通过外键约束引用othertable的任何表格：

TRUNCATE othertable CASCADE;

重置相关的序列生成器：

TRUNCATE bigtable, fattable RESTART IDENTITY;

截断并重置任何相关的序列生成器：

TRUNCATE revinfo RESTART IDENTITY CASCADE ;

- OJVM

---

非常好，非常有用。 - sidtagirisa

最佳答案。 - Mateusz Niedbal

Answer 5

这意味着在表kontakty中，您有一行引用了要删除的osoby表中的行。您需要先删除该行或在表之间设置级联删除关系。

祝好运！

- zibi

Answer 6

可以通过发布额外的SQL脚本来删除与FK相关的记录，从而实现此目的。

为此，在DELETE命令的WHERE子句中使用子查询即可轻松完成所需操作。

类似于以下内容：

DELETE FROM kontakty 
    WHERE fk_column_from_kontakty_matching_id_osoby IN (
        SELECT id_osoby FROM osoby WHERE id_osoby = '1'
    );


DELETE FROM osoby WHERE id_osoby = '1';

• 这个示例假设kontakty表中的FK列与osoby.id_osoby匹配的名称为fk_column_from_kontakty_matching_id_osoby，因为无法从提供的错误消息中推断出来。
• 对于OP的情况，查询可以简化很多，但我决定保留它，以展示完成更复杂的场景。
• 这种方法在SQL迁移中非常有用，在这种情况下，我们不能依赖分配的id，并且无法轻松地先获取id然后再进行操作。

- helvete

---

这是对所提出问题最干净的解决方案。拥有“CASCADE”条件等一切都很好，但是 OP 可能没有实际修改设计的权利。鉴于现状，这是解决问题的方法。 - dakini