DROP TABLE CASCADE and even DROP ALL OBJECTS fail due to certain types of dependencies

[Vladimir Oslon]

Here's a little SQL excerpt:

CREATE TABLE A (A INT);
CREATE TABLE B (B INT AS SELECT A FROM A);
DROP TABLE A,B;

running it, I get

Cannot drop "PUBLIC.A" because "PUBLIC.B" depends on it; SQL statement:

DROP TABLE A,B [90107-171] 90107/90107 (Help)

This could have been predicted - after all, the doc only promises that "All dependent views are dropped as well if the CASCADE clause is used." However, DROP SCHEMA and even DROP ALL OBJECTS yield the same result, although the doc for the latter says "Drops all existing views, tables, sequences, schemas, function aliases, roles, user-defined aggregate functions, domains, and users (except the current user)."

The INFORMATION_SCHEMA only keeps track of the above dependency in TABLES/COLUMNS (in the SQL form), so I see no reasonable way to drop a table without going to unreasonable lengths figuring out its dependants.

Yet a method must exist to force dropping a table (please don't suggest DROP B,A instead of DROP A,B).

Or have I run into a design flaw?

Thanks,

Vladimir 

[Steve McLeod]

Hi Vladimir,

In your example, it seems simple. However consider a more advanced example:

DROP TABLE A, B, C, D, E, F, G;

where there is a series of dependencies. Determining the dependencies automatically would be tricky. I think it is reasonable for SQL (and H2's implementation thereof) to demand that you specify the tables to drop in an order that satisfies the dependencies.

[Vladimir Oslon]

Hi Steve,

My example is by no means simpler than yours. What I'm talking about is a way to get rid of a [set of] table[s] no questions asked -  something like DROP TABLE FORCE and/or DROP SCHEMA FORCE.

Within the current SQL scope, though, I strongly believe that DELETE ALL OBJECTS ought to disregard any dependencies whatsoever.

Thanks,

Vladimir

[Noel Grandin]

Thanks for the test case.

This is fixed in SVN.

DROP ALL OBJECTS and DROP SCHEMA now knows how to deal with computed
column dependencies.