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.