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 view descriptor or
       <search condition> included in constraint descriptor because
       any implicit <column reference>s in 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 the name 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 in the <search
       condition> of any constraint descriptor other than a table con-
       straint descriptor that contains references 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 column resulting
       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 <revoke statement> 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 with a 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 Access Rule 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 the ordinal
       position of C is reduced by 1.