Document syntax change for ALTER TABLE in Reference Manual

• Key: DERBY-4016
• Type: Improvement
• Status: Closed
• Resolution: Fixed
• Priority: Minor
• Assignee: Kim Haase
• Reporter: Kim Haase
• Component/s: Documentation
• Affects Version/s: 10.5.1.1
• Fix Version/s: 10.5.1.1

Description

DERBY-4013 adds support for the standard SET keyword in addition to the optional WITH keyword in ALTER TABLE ALTER [COLUMN] statements. This change should be documented in the Reference Manual.

Question: [WITH] DEFAULT occurs also in ALTER TABLE ADD COLUMN. Will SET work here as well?

Comments

[Kim Haase]

The "Derby and standards" section of the Developer's Guide (http://db.apache.org/derby/docs/dev/devguide/cdevstandards806118.html) mentions Derby's implementation of ALTER TABLE:

"ALTER TABLE syntax
    Derby uses a slightly different ALTER TABLE syntax for altering column defaults. While SQL99 uses DROP and SET, Derby uses DEFAULT."

Is our ALTER TABLE syntax now standard, since we now support both DROP and SET? Should this item be removed?

[Knut Anders Hatlen]

SQL allows this construct, which is still not accepted by Derby, I think:

  ALTER TABLE t ALTER COLUMN c DROP DEFAULT

[Kim Haase]

Thanks, Knut. I suppose we could then modify the statement to say,

"ALTER TABLE syntax
    Derby limits somewhat the ALTER TABLE syntax for altering column defaults. Derby does not support DROP DEFAULT."

Would that be correct?

I haven't yet heard back as to whether SET DEFAULT works with ALTER TABLE ADD COLUMN as well as ALTER TABLE ALTER COLUMN -- does it?

[Dag H. Wanvik]

As far as ADD COLUMN, that uses the normal column definition syntax.

[Section 11.13 in SQL 2003 has the production

<set column default clause> ::= SET <default clause>

which is only ever used by <alter column action>, see section 11.12.]

We still do not support the syntax for DROP DEFAULT, but one can specify SET DEFAULT NULL
which has the same semantics as dropping the default. We should probably add this syntax as well. I will
see if I can fit it into DERBY-4013.. Thanks for alerting me.

[Kim Haase]

Thanks, Dag. So the last line of column-alteration in http://db.apache.org/derby/docs/dev/ref/rrefsqlj81859.html will change to

  column-name [ WITH | SET ] DEFAULT default-value

(Presumably you can't use both WITH and SET?)

Currently we don't say anything about this clause, but I can add that you can specify NULL as the default-value to disable a previous default setting. Can you use NULL if you don't use SET -- that is, can you specify WITH DEFAULT NULL or just DEFAULT NULL?

[Kim Haase]

Attaching DERBY-4016.diff and rrefsqlj81859.html, which make what I believe are the necessary changes to the ALTER TABLE syntax along with an explanatory couple of sentences and an example. Please let me know whether anything needs changing.

[Kim Haase]

Having learned from a comment on DERBY-4013 that we're now implementing DROP DEFAULT, I'm filing new versions of DERBY-4016.diff and rrefsqlj81859.html that include that change, and that also move the information about the two DEFAULT clauses to a more visible place, in the section "Modifying columns". Hope this is okay.

[Knut Anders Hatlen]

The patch looks fine to me. You're right that we cannot use both WITH and SET. I'm not sure if DROP DEFAULT and SET DEFAULT NULL have the exact same semantics (DROP DEFAULT is supposed to fail if there is no default, I think) but the current wording, which just says that SET DEFAULT NULL is an alternative, should be OK.

[Kim Haase]

Thanks very much, Knut Anders.

Committed patch DERBY-4016.diff to documentation trunk at revision 736390.

[Kim Haase]

Closing, since the fix has appeared in the latest alpha docs.