Cannot drop table users because other objects depend on it

Question

I want to drop my tables in my database. But, when I use, for example, DROP TABLE if exists users; I receive this message:

cannot drop table users because other objects depend on it

I found the solution is to drop all tables. But, anyway, how to solve this problem without total data removal?

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

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

Answer 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).

Answer 3

I created my_func() trigger function as shown below:

CREATE FUNCTION my_func() RETURNS trigger
AS $$
BEGIN
END;
$$ LANGUAGE plpgsql;

Then, I created my_t trigger with my_func() on test table as shown below:

CREATE TRIGGER my_t AFTER UPDATE ON test
FOR EACH ROW EXECUTE FUNCTION my_func();

Then, I tried to drop my_func() trigger function as shown below:

DROP FUNCTION my_func;

But, I got the similar error below:

ERROR: cannot drop function my_func() because other objects depend on it
DETAIL: trigger my_t on table test depends on function my_func()
HINT: Use DROP ... CASCADE to drop the dependent objects too.

So, I set CASCADE as shown below, then I could drop my_func trigger function and my_t trigger together without error. *The doc explains how to drop a function in detail:

                   -- ↓ ↓ ↓ ↓
DROP FUNCTION my_func CASCADE;

Or first, I dropped my_t trigger on test table as shown below. *The doc explains how to drop a trigger in detail::

DROP TRIGGER my_t ON test;

Then, I could drop my_func() trigger function without error as shown below:

DROP FUNCTION my_func;

Answer 4

use the following command :

drop trigger "trigger_name" on "table_name";

as per the supabase documentation here.

Answer 5

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