db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: DERBY-396: Drop Column dependency questions
Date Mon, 10 Jul 2006 16:44:20 GMT
Hi Bryan,

My $0.02 follows. Cheers-Rick

Bryan Pendleton wrote:

> ...
> a) RESTRICT processing should consider an index on a column to be
> a dependent object and fail the DROP COLUMN if the column is used
> in an index?

The ANSI spec doesn't provide much guidance here since ANSI doesn't talk 
about indexes. I would recommend using DROP TABLE as your guide. 
Dropping a table implicitly drops all indexes defined on that table. I 
would expect that dropping a column would implictly drop all indexes 
which mention that column.

> b) CASCADE processing should cascade the DROP COLUMN to include
> dropping a view which uses the column that is dropped?

My reading of the ANSI spec is that CASCADE is the opposite of RESTRICT. 
That is, if you specify CASCADE, then the database will drop the 
dependent objects which blocked the RESTRICTed version of the statement. 
According to the ANSI spec (Volume 2, section 11.18), these dependent 
objects include:

o views which mention the column
o certain constraints
o triggers which mention the column
o generated columns whose definitions mention the dropped column (we 
don't support this yet)
o sql routines which mention the column (again, we don't support this)

So my reading of the ANSI spec suggests that CASCADE should drop views 
which mention the column.

I think that the RESTRICT-blocking logic is smart enough to compute the 
closure of dropped objects (the dependents of the dependents and so on). 
I would think that CASCADE would drop everything in that closure.

> thanks,
> bryan

View raw message