db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <rick.hille...@oracle.com>
Subject Re: Views dropped automatically when altering tables?
Date Tue, 07 Feb 2012 14:43:45 GMT
Hi John,

This is one of many areas where Derby's dependency tracking could be 
improved. I think that Derby is over-aggressive in dropping views when 
you drop a column in the underlying table. As I read the SQL Standard 
(part 2, section 11.19 <drop column definition>), a RESTRICTed column 
drop does not need to affect views which don't reference the column. 
However, in this situation, Derby sees all views on the table as 
blocking dependencies. The following script shows this:

connect 'jdbc:derby:memory:db;create=true';

create table t( a varchar( 10 ), b varchar( 10 ) );
create view v1 as select a from t;
select * from v1;

-- column alteration is ok, the view is not dropped
alter table t alter column a set data type varchar( 20 );
select * from v1;

-- adding a column is ok, the view is not dropped
alter table t add column c int;
select * from v1;

-- dropping a column behaves over-aggressively. the view does not need 
to prevent this action.
alter table t drop column b restrict;
select * from v1;

-- similarly, the view does not need to be dropped here. derby does 
issue a warning that v1 is dropped.
alter table t drop column b;
select * from v1;


On 2/7/12 2:28 AM, John English wrote:
> Hi all,
> When altering a table the other day I discovered that any views that
> reference the table get dropped automatically (and silently), as do
> any views that depend on those views, and so on. This came as a nasty
> surprise to me when I tried to access one of those views!
> If the table has associated triggers I get an error when I try to alter
> it, so I know which triggers are involved and I can then drop them and
> re-create them after the alteration. Shouldn't the same be true for
> views to avoid surprises? Or does the standard mandate this bizarre
> behaviour somewhere?
> I'd be glad to hear any informed opinions on this...
> ------------------------------------------------------------------------
>  John English | My old University of Brighton home page is still here:
>               | http://www.cem.brighton.ac.uk/staff/je/
> ------------------------------------------------------------------------

View raw message