SQL 删除PostgreSQL中带有外键的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14182079/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Delete rows with foreign key in PostgreSQL
提问by Michal Loksik
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".
错误：更新或删除表“osoby”违反了表“kontakty”上的外键约束“kontakty_ibfk_1”详细信息：键（id_osoby）=（1）仍然从表“kontakty”中引用。
How can I delete these rows?
如何删除这些行？
回答by Erwin Brandstetter
To automate this, you could define the foreign key constraint with ON DELETE CASCADE.
I quote the the manual of foreign key constraints:
要自动执行此操作，您可以使用ON DELETE CASCADE.
我引用了外键约束手册：
CASCADEspecifies that when a referenced row is deleted, row(s) referencing it should be automatically deleted as well.
CASCADE指定当删除引用的行时，引用它的行也应自动删除。
Look up the current FK definition like this:
像这样查找当前的 FK 定义：
SELECT pg_get_constraintdef(oid) AS constraint_def
FROM pg_constraint
WHERE conrelid = 'public.kontakty'::regclass -- assuming pubic 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:
然后在如下语句中添加或修改该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;
Since there is no ALTER CONSTRAINTsyntax, drop and recreate the constraint in a single ALTER TABLEstatement. That avoid possible race conditions with concurrent write access.
由于没有ALTER CONSTRAINT语法，请在单个ALTER TABLE语句中删除并重新创建约束。这避免了并发写访问时可能出现的竞争条件。
You need the privileges to do so, obviously. The operation takes an ACCESS EXCLUSIVElock on table kontaktyand a SHARE ROW EXCLUSIVElock on table osoby.
显然，您需要特权才能这样做。该操作需要ACCESS EXCLUSIVE锁定 tablekontakty和SHARE ROW EXCLUSIVE锁定 table osoby。
If you can't ALTERthe table, then deleting by hand (once) or by trigger BEFORE DELETE(every time) are the remaining options.
如果你不能ALTER删除表，那么手动（一次）或通过触发器BEFORE DELETE（每次）删除是剩下的选项。
回答by juergen d
You can't delete a foreign key if it still references another table. First delete the reference
如果外键仍然引用另一个表，则不能删除它。先删除引用
delete from kontakty
where id_osoby = 1;
DELETE FROM osoby
WHERE id_osoby = 1;
回答by Andrew Basile
One should not recommend this as a general solution, but for one-off deletion of rows in a database that is not in production or in active use, you may be able to temporarily disable triggers on the tables in question.
不应将其推荐为通用解决方案，但对于一次性删除不在生产中或未在使用中的数据库中的行，您可以暂时禁用相关表上的触发器。
In my case, I'm in development mode and have a couple of tables that reference one another via foreign keys. Thus, deleting their contents isn't quite as simple as removing all of the rows from one table before the other. So, for me, it worked fine to delete their contents as follows:
就我而言，我处于开发模式并且有几个表通过外键相互引用。因此，删除它们的内容并不像从一个表中删除另一个表中的所有行那么简单。所以，对我来说，删除它们的内容很好，如下所示：
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;
You should be able to add WHERE clauses as desired, of course with care to avoid undermining the integrity of the database.
您应该能够根据需要添加 WHERE 子句，当然要小心避免破坏数据库的完整性。
There's some good, related discussion at http://www.openscope.net/2012/08/23/subverting-foreign-key-constraints-in-postgres-or-mysql/
在http://www.openscope.net/2012/08/23/subverting-foreign-key-constraints-in-postgres-or-mysql/ 上有一些很好的相关讨论
回答by OJVM
It's been a while since this question was asked, hope can help. Because you can not change or alter the db structure, you can do this. according the postgresql docs.
问这个问题已经有一段时间了，希望能有所帮助。因为您无法更改或更改 db 结构，所以您可以这样做。根据 postgresql文档。
TRUNCATE-- empty a table or set of tables.
TRUNCATE-- 清空一个表或一组表。
TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ]
[ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]
Description
描述
TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster. Furthermore, it reclaims disk space immediately, rather than requiring a subsequent VACUUM operation. This is most useful on large tables.
TRUNCATE 快速从一组表中删除所有行。它与对每个表的非限定 DELETE 具有相同的效果，但由于它实际上并不扫描表，因此速度更快。此外，它会立即回收磁盘空间，而不需要后续的 VACUUM 操作。这在大表上最有用。
Truncate the table othertable, and cascade to any tables that reference othertable via foreign-key constraints:
截断表 othertable，并通过外键约束级联到引用 othertable 的任何表：
TRUNCATE othertable CASCADE;
The same, and also reset any associated sequence generators:
相同，并且还重置任何关联的序列生成器：
TRUNCATE bigtable, fattable RESTART IDENTITY;
Truncate and reset any associated sequence generators:
截断和重置任何关联的序列生成器：
TRUNCATE revinfo RESTART IDENTITY CASCADE ;
回答by zibi
It means that in table kontaktyyou have a row referencing the row in osobyyou want to delete. You have do delete that row first or set a cascade delete on the relation between tables.
这意味着在表中kontakty有一行引用osoby要删除的行。您必须先删除该行或在表之间的关系上设置级联删除。
Powodzenia!
波沃泽尼亚！
