SQL 不能删除表用户，因为其他对象依赖它
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35338711/
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
cannot drop table users because other objects depend on it
提问by Vyacheslav
I want to drop my tables in database.
But, when I use, for example,
DROP TABLE if exists users;I receive this message:
我想删除数据库中的表。但是，例如，当我使用时，
DROP TABLE if exists users;我收到此消息：
cannot drop table users because other objects depend on it
cannot drop table users because other objects depend on it
I found the solution is to drop all database. But, anyway, how to solve this problem without total data removal?
我发现解决方案是删除所有数据库。但是，无论如何，如何在不完全删除数据的情况下解决这个问题？
回答by a_horse_with_no_name
Use the cascadeoption:
使用cascade选项：
DROP TABLE if exists users cascade;
this will drop any foreign key that is referencing the userstable or any view using it.
这将删除引用users表或使用它的任何视图的任何外键。
It will not drop other tables(or delete rows from them).
它不会删除其他表（或从中删除行）。
回答by dat789
If it was really necessary to drop that specific table with or without recreating it, then first find the object(s) that depends on it.
如果确实有必要在重新创建或不重新创建该特定表的情况下删除它，那么首先找到依赖于它的对象。
CREATE OR REPLACE VIEW admin.v_view_dependency AS
SELECT DISTINCT srcobj.oid AS src_oid
, srcnsp.nspname AS src_schemaname
, srcobj.relname AS src_objectname
, tgtobj.oid AS dependent_viewoid
, tgtnsp.nspname AS dependant_schemaname
, tgtobj.relname AS dependant_objectname
FROM pg_class srcobj
JOIN pg_depend srcdep ON srcobj.oid = srcdep.refobjid
JOIN pg_depend tgtdep ON srcdep.objid = tgtdep.objid
JOIN pg_class tgtobj ON tgtdep.refobjid = tgtobj.oid AND srcobj.oid <> tgtobj.oid
LEFT JOIN pg_namespace srcnsp ON srcobj.relnamespace = srcnsp.oid
LEFT JOIN pg_namespace tgtnsp ON tgtobj.relnamespace = tgtnsp.oid
WHERE tgtdep.deptype = 'i'::"char" AND tgtobj.relkind = 'v'::"char";
Then,
然后，
select top 99 * from admin.v_view_dependency where src_objectname like '%the_table_name_it_complaint_about%';
The result set will show you the dependant object in the field "dependant_objectname".
结果集将在字段“dependant_objectname”中显示依赖对象。
回答by alexis
In general, to drop several interdependent tables you start from the tables that nothing depends on (the ones that have foreign keys pointing toother tables), and work backwards. E.g., if the table transactionsdepends on the table users, you'd drop transactionsfirst. In short: Delete tables in the reverse order from how they were created.
在一般情况下，放弃你，没有什么依赖于表（即有外键指向的那些启动若干相互依存表到其他表），和工作倒退。例如，如果 tabletransactions依赖于 table users，您将transactions首先删除。简而言之：以与创建表相反的顺序删除表。
If you manage to create tables with circular dependencies, you can first delete the foreign key constraint that prevents deletion. Or you can use the modifier CASCADE, which (as @a_horse explained in the comments), will drop any foreign key constraints that involve the deleted table. But note that not all DBMS's support CASCADE: Postgres does, but MySQL does not (the keyword is accepted but has no effect).
如果您设法创建具有循环依赖关系的表，您可以先删除防止删除的外键约束。或者您可以使用修饰符CASCADE，它（如@a_horse 在评论中解释的那样）将删除任何涉及已删除表的外键约束。但请注意，并非所有 DBMS 都支持CASCADE：Postgres支持，但 MySQL 不支持（该关键字被接受但无效）。
