删除PostgreSQL中带有外键的行 [英] Delete rows with foreign key in PostgreSQL

问题描述

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

DELETE FROM osoby WHERE id_osoby='1'

我得到这个声明:

<块引用>

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

如何删除这些行?

解决方案

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

<块引用>

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

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

SELECT pg_get_constraintdef(oid) AS constraint_def从 pg_constraintWHERE conrelid = 'public.kontakty'::regclass -- 假设公共模式AND conname = 'kontakty_ibfk_1';

然后在如下语句中将 ON DELETE ... 部分添加或修改为 ON DELETE CASCADE(保留其他所有内容):

ALTER TABLE kontakty删除约束 kontakty_ibfk_1, 添加约束 kontakty_ibfk_1外键 (id_osoby) 参考 osoby (id_osoby) 删除级联；

没有 ALTER CONSTRAINT 命令.在单个 ALTER TABLE 语句中删除并重新创建约束，以避免并发写入访问时可能出现的竞争条件.

显然，您需要权限才能这样做.该操作需要对表 kontakty 的 ACCESS EXCLUSIVE 锁和对表 osoby 的 SHARE ROW EXCLUSIVE 锁.

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

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'

I get this statement:

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".

How can I delete these rows?

解决方案

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

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

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';

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;

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.

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.

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屋！