不能删除表用户，因为其他对象依赖于它

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如果存在用户;我收到此消息：

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?

我发现解决方案是删除所有数据库。但是，无论如何，如何在没有全面数据删除的情况下解决这个问题？

3 个解决方案

#1

Use the cascade option:

使用级联选项：

DROP TABLE if exists users cascade;

this will drop any foreign key that is referencing the users table or any view using it.

这将删除引用users表或使用它的任何视图的任何外键。

It will not drop other tables (or delete rows from them).

它不会删除其他表（或从中删除行）。

#2

In general, to drop several interdependent tables you start from the tables that nothing depends on (the ones that have foreign keys pointing to other tables), and work backwards. E.g., if the table transactions depends on the table users, you'd drop transactions first. In short: Delete tables in the reverse order from how they were created.

通常，要删除几个相互依赖的表，您可以从没有任何依赖的表（具有指向其他表的外键的表）开始，然后向后工作。例如，如果表事务取决于表用户，则首先删除事务。简而言之：按照创建方式的相反顺序删除表。

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不支持（关键字被接受但没有效果）。

#3

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”字段中显示依赖对象。

#1