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;
Thanks,
-Rick
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/
------------------------------------------------------------------------
