John English <[email protected]> writes: > 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?
How did you alter the table? At least for the DROP COLUMN feature, the syntax looks like this: DROP [ COLUMN ] column-name [ CASCADE | RESTRICT ] The CASCADE gives the behavior you see, the RESTRICT should balk. In the documentation, we see that CASCADE is the default: http://db.apache.org/derby/docs/10.8/ref/rrefsqlj81859.html "The keywords CASCADE and RESTRICT are also optional. If you specify neither CASCADE nor RESTRICT, the default is CASCADE." The SQL standard makes the <drop behavior> specification mandatory (no default), see ISO/IEC 9075-2:2003 (E) section 11.18 <drop column definition>. Thanks, Dag > > 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/ > ------------------------------------------------------------------------
