[DERBY-4013] Allow standard SQL syntax: ALTER TABLE ALTER [COLUMN] <col> SET DEFAULT <default>

• Type: Improvement
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0
• Fix Version/s: 10.5.1.1
• Component/s: SQL
• Assignee: Dag H. Wanvik
• Reporter: Dag H. Wanvik
• Created: 09/Jan/09 18:31
• Updated: 02/May/13 02:29
• Resolved: 26/Jan/09 18:29

Description

Presently, the Derby syntax is ALTER TABLE ALTER [COLUMN] <col> [WITH] DEFAULT <default>.
The "SET" keyword is not accepted, only an optional "WITH". It would be good to accept the standard syntax here as well.
Cf. SQL 2003, section 11.12 <alter column definition> and section 11.13 <set column default clause>.

Also, DROP DEFAULT is standard syntax not supported, so we should add that, too.

Repro on trunk:

$ java org.apache.derby.tools.ij
ij version 10.5
ij> connect 'jdbc:derby:wombat;create=true';
ij> create table t(i int default 0, j int);
0 rows inserted/updated/deleted
ij> alter table t alter column j with default 1;
0 rows inserted/updated/deleted
ij> insert into t values (default, default);
1 row inserted/updated/deleted
ij> select * from t;
I |J
-----------------------
0 |1

1 row selected
ij> alter table t alter column j default 2;
0 rows inserted/updated/deleted
ij> insert into t values (default, default);
1 row inserted/updated/deleted
ij> select * from t;
I |J
-----------------------
0 |1
0 |2

2 rows selected
ij> alter table t alter column j set default 3;
ERROR 42X01: Syntax error: Encountered "set" at line 1, column 30.

Issue Links

• is depended upon by: DERBY-4016 Document syntax change for ALTER TABLE in Reference Manual