在 PostgreSQL 中使用外键删除行 [英] Delete rows with foreign key in PostgreSQL

问题描述

我想删除包含外键的行，但是当我尝试这样的事情时:

I would like to delete rows which contain a foreign key, but when I try something like this:

DELETE FROM osoby WHERE id_osoby='1'

我明白了:

错误:更新或删除表osoby"违反了表kontakty"上的外键约束kontakty_ibfk_1"详细信息:键 (id_osoby)=(1) 仍从表kontakty"中引用.

ERROR: update or delete on table "osoby" violates foreign key constraint "kontakty_ibfk_1" on table "kontakty" DETAIL: Key (id_osoby)=(1) is still referenced from table "kontakty".

如何删除这些行?

推荐答案

要自动执行此操作，您可以使用 ON DELETE CASCADE 定义外键约束.
我引用 外键约束手册:

To automate this, you could define the foreign key constraint with ON DELETE CASCADE.
I quote the the manual for foreign key constraints:

CASCADE 指定当引用的行被删除时，行引用它也应该被自动删除.

CASCADE specifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as well.

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

Look up the current FK definition like this:

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 (保留其他所有内容):

Then add or modify the ON DELETE ... part to ON DELETE CASCADE (preserving everything else as is) in a statement like:

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 语句中删除并重新创建约束，以避免并发写入访问可能出现的竞争条件.

There is no ALTER CONSTRAINT command. Drop and recreate the constraint in a single ALTER TABLE statement to avoid possible race conditions with concurrent write access.

显然，您需要特权才能这样做.该操作在表 kontakty 上使用 ACCESS EXCLUSIVE 锁并在表 osoby 上使用 SHARE ROW EXCLUSIVE 锁.

You need the privileges to do so, obviously. The operation takes an ACCESS EXCLUSIVE lock on table kontakty and a SHARE ROW EXCLUSIVE lock on table osoby.

如果您不能ALTER 表，则手动删除(一次)或触发器BEFORE DELETE(每次)是剩下的选项.

If you can't ALTER the table, then deleting by hand (once) or by trigger BEFORE DELETE (every time) are the remaining options.

这篇关于在 PostgreSQL 中使用外键删除行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！