Re: Happy column adding and dropping

On Tue, Jan 25, 2000 at 11:20:01AM -0800, Don Baccus wrote:
> At 12:29 PM 1/25/00 -0600, Ross J. Reedstrom wrote:
> >On Tue, Jan 25, 2000 at 12:23:15PM -0500, Tom Lane wrote:
> 
> >> Well, yeah: wouldn't you expect that "ADD COLUMN x DEFAULT 42" would
> >> cause every row currently existing in the table to acquire x = 42,
> >> rather than x = NULL?  In fact that would *have* to happen to allow
> >> constraints to be added; consider ADD COLUMN x DEFAULT 42 NOT NULL.
> 
> >Actually, no I wouldn't expect it. That's mixing DDL and DML in one
> >statement.  I expect the ALTER command to be pure DDL, and the UPDATE
> >to be pure DML.
> 
> Hmmm...interesting...is alter table in the standard?  Again, my copy
> of Date's SQL 92 primer is somewhere 'wteen Boston, MA and Portland, OR,
> so I can't look myself.   Since you've got the standard available you
> can answer perhaps?

Gee, Don, that's a good idea, I should have thought of it myself! BTW,
what I have is labelled "Second Informal Review Draft" and is dated July
30, 1992.  I don't know how it differs from the final standard, if at all.
I orginally got it from someone on this list, I forget who. Anyone
want a copy, I'll happily email it to you, or throw it on a website:
it's 1.6M of ASCII, ready for the line printer ;-) (273K gzipped.)

That said, ALTER is in fact described, and the ADD COLUMN case agrees
with Tom's expectations, rather than mine, see General Rule 2, below:
   11.11  <add column definition>
   Function
   Add a column to a table.
   Format
   <add column definition> ::=        ADD [ COLUMN ] <column definition>

   Syntax Rules
      None.
   Access Rules

      None.
   General Rules
   1) The column defined by the <column definition> is added to T.
   2) Let C be the column added to T. Every value in C is the default      value for C.
      Note: The default value of a column is defined in Subclause 11.5,      "<default clause>".
      Note: The addition of a column to a table has no effect on any      existing <query expression> included in a
viewdescriptor or      <search condition> included in constraint descriptor because      any implicit <column
reference>sin these clauses are replaced      by explicit <column reference>s when the clause is originally
evaluated.See the Syntax Rules of Subclause 7.10, "<query ex-      pression>".
 


For what it's worth, here's what it says about DROP COLUMN. Note that
the question of what to do about references to columns: standard says,
throw and error, unless the DBA really means it, with the CASCADE option,
except for VIEWs, which get dropped, unless the DBA is careful to say
RESTRICT.

   11.15  <drop column definition>
   Function
   Destroy a column.
   Format
   <drop column definition> ::=        DROP [ COLUMN ] <column name> <drop behavior>
   <drop behavior> ::= CASCADE | RESTRICT
   Syntax Rules
   1) Let T be the table identified by the <table name> in the con-      taining <alter table statement> and let TN be
thename of T.
 
   2) Let C be the column identified by the <column name> CN.
   3) C shall be a column of T and C shall not be the only column of      T.
   4) If RESTRICT is specified, then C shall not be referenced in      the <query expression> of any view descriptor or
inthe <search      condition> of any constraint descriptor other than a table con-      straint descriptor that
containsreferences to no other column      and that is included in the table descriptor of T.
 
      Note: A <drop column definition> that does not specify CASCADE      will fail if there are any references to that
columnresulting      from the use of CORRESPONDING, NATURAL, SELECT * (except where      contained in an exists
predicate>),or REFERENCES without a      <reference column list> in its <referenced table and columns>.
 
      Note: If CASCADE is specified, then any such dependent object      will be dropped by the execution of the
<revokestatement> spec-      ified in the General Rules of this Subclause.
 
   Access Rules
      None.
   General Rules
   1) Let A be the current <authorization identifier>. The following      <revoke statement> is effectively executed
witha current <au-      thorization identifier> of "_SYSTEM" and without further Access      Rule checking:
 
        REVOKE INSERT(CN), UPDATE(CN), REFERENCES(CN) ON TABLE TN        FROM A CASCADE
   2) Let VN be the name of any view that contains a reference to      column C of table T. The following <drop view
statement>is      effectively executed with a current <authorization identifier>      of "_SYSTEM" and without further
AccessRule checking:
 
        DROP VIEW VN CASCADE
   3) If the column is not based on a domain, then its data type de-      scriptor is destroyed.
   4) The data associated with C is destroyed and the descriptor of C      is removed from the descriptor of T.
   5) The identified column and its descriptor are destroyed.
   6) The degree of T is reduced by 1. The ordinal position of all      columns having an ordinal position greater than
theordinal      position of C is reduced by 1.