Support for ALTER STATEMENT to DROP , MODIFY, RENAME a COLUMN

• Key: DERBY-396
• Type: Improvement
• Status: Closed
• Resolution: Fixed
• Priority: Major
• Assignee: Bryan Pendleton
• Reporter: Kumar Matcha
• Component/s: SQL
• Fix Version/s: 10.3.1.4

Description

Alter Statement should support dropping a column, modifying a column to a different data type , rename a column.

Comments

[Rick Hillegas]

---------------------------------

ANSI supplies syntax for dropping a column:

  ALTER TABLE tableName DROP columnName

----------------------------------------------

Derby already supports some datatype modification. It would be interesting to understand the use cases for additional datatype mods.

----------------------------------------------

I can't find ANSI syntax for renaming a column nor can I find this feature in DB2. Oracle and Postgres agree on the following syntax:

  ALTER TABLE tableName RENAME COLUMN oldColumnName TO newColumnName

MySQL supports the following less intuitive syntax:

  ALTER TABLE tableName CHANGE oldColumnName newColumnName oldDatatype

Is the Oracle/Postgres agreement a good enough standard?

[Kumar Matcha]

Derby is supposed to be SQL-92E compliance and supports key features in the SQL-99 standards. If you take a look at the SQL92 Standard for ALTER TABLE STATEMENT :

<alter table statement> ::=
              ALTER TABLE <table name> <alter table action>

         <alter table action> ::=
                <add column definition>
              | <alter column definition>
              | <drop column definition>
              | <add table constraint definition>
              | <drop table constraint definition>

So its supposed to provide support for dropping a column. Although RENAME COLUMN is vendor specific, Oracle/Postgres aggrement is a good enough standard.

[Scott MacDonald]

I am presently working on the Rename and Drop column issues.

As discussed previously on this Comment page, should the Oracle and Postgres syntax for renaming and dropping columns be followed?

The syntax is:

ALTER TABLE <table_name> DROP COLUMN <table_name>

ALTER TABLE <table_name> RENAME COLUMN <old_name> TO <new_name>


I have also been browsing documentation for Oracle, Postgres, and DB2 and doing some simple testing in DB2 regarding contstraints on renaming and dropping columns.

I have found that any column can be dropped, so long as it does not affect the referential integrity of the table or any other tables in the database (ie. as long as the column is not a primary or foreign key).

I have also found that any column can be renamed so long as the new column name is not already the name of another column in the table. The new column name must also be either an ordinary or delimited identifier.

If anybody has any comments on these topics before I begin implementation, I would like to hear them.

Thanks,
Scott

[Satheesh Bandaram]

SQL standard specifies DROP COLUMN syntax as:

ALTER TABLE <table name> DROP [COLUMN ]<column name><drop behavior>

<drop behavior>::= CASCADE | RESTRICT

You have to define if you are implementing CASCADE or RESTRICT behavior. Restrict would be easier to implement.

About RENAME COLUMN, it is not defined in the SQL standard. Derby typically allows SQL conforming syntax, so not sure about this one...

[Rick Hillegas]

I think that the Oracle/Postgres RENAME syntax is straightforward and good enough. Its similarity to Derby's RENAME TABLE syntax is also inviting. MySQL's syntax is, frankly, odd. ANSI and DB2 are silent on the issue. So I say, go with the inviting, straightforward syntax already embraced by two big players.

[Rick Hillegas]

Enhancement request 396 tracks various ALTER TABLE improvements, including column-dropping.

[Neil Weber]

What's also odd is that the DatabaseMetaData for Derby returns true for supportsAlterTableWithDropColumn(). Since Derby doesn't support dropping columns, the meta data should be false.

[Rick Hillegas]

Downgrading priority to major.

[Dheeraj Dhiman]

Hello Scott MacDonald !!
I m dheeraj !
I m switch from sql server to apache derby for my project !!
You say We can rename or drop a column from table !!
But i just try this & exception occur !!
My case is ::
I just create a table .

create table simple( name varchar(100), rollno bigint , fullName varchar(100));
table create after excuting this statement .

but now i want to change name of column rollno !!

I try ur suggetion which is given by you !!

you say ::

alter table simple rename column rollno to serialno ;
After excuting this query error occur ;
ERROR 42X01: Syntax error: Encountered "RENAME" at line 1, column 17.

&& i m also try another thing if we want drop a column !!
alter table simple drop column name ;
ERROR 42X01: Syntax error: Encountered "drop" at line 1, column 17


SWo now how can i drop or rename a column in apache derby !!
The table simple table have no primary key & no other forignkey !!
so how can i do this !!
I have mail accoutn on your apache derby jira log .
with name dheeraj.dhiman
or you can mail me at my email id
[e-mail]

I wanna answer of this problem as early as possible !!
Thanks !!

[Wade Chandler]

Hmmm. I think what Scott said was he is working on it and no where did I see where he said it works. He mentions something working in another server. Did you see him write something else some where besides this issue?

[Rick Hillegas]

Hi Wade,

Scott did not submit a patch for this issue and he has moved on to other pursuits. This issue is currently unassigned.

[Bryan Pendleton]

I am interested in working on this issue, and would like to hear from others who are interested in it.

I propose to start by working on ALTER TABLE DROP [COLUMN] column [CASCADE|RESTRICT].

If that goes well, I'll move on to some of the other ALTER TABLE column variants.

Following the suggestion made by Dan on the mailing list
(http://www.nabble.com/forum/ViewPost.jtp?post=1252308), I'm hoping to wire up the
parser to the existing Alter Table support code.

I've got a prototype implementation of the DROP COLUMN support working, and
will be asking the list for some help with parser issues soon.

[Bryan Pendleton]

Attached file 'dropColumn_1.diff' is a first attempt at providing support for
   ALTER TABLE DROP COLUMN

This patch contains some small changes to the parser/compiler to recognize
the new ALTER TABLE syntax, and uses the existing code in
AlterTableConstantAction.java for the execution support.

The patch contains a small amount of new test code, but really I haven't done
much testing yet; I suspect that there is a LOT more testing to do, and I'm
hoping that reviewers will suggest particular test cases that would be of
interest.

I'm particularly interested in feedback about the parser changes, and about testing,
but of course all feedback is very gratefully appreciated.

[Bryan Pendleton]

I propose to file separate JIRA issues for the topics described here:

1) drop column
2) rename column
3) change column from null to not null, or vice versa
4) change column default value
5) change column datatype

DERBY-119 already exists to track issue (3); I will be filing 4 new issues, linked to this one, to track the other topics. DERBY-168 was previously filed for issue (4), but was closed by changing the documentation, so I will open a new issue to track addition of the functionality.

[Bryan Pendleton]

I got confused and opened DERBY-1492 accidentally; it was a duplicate of DERBY-119 so I closed it. Then I opened DERBY-1515 to track changing a column's datatype.

[Bryan Pendleton]

I believe this issue can now be closed, and the other issues tracked independently. If you disagree, please comment accordingly.

[Kathey Marsden]

Since this issue has 14 votes, I think I would prefer to see it left open and the other issues linked as "part of" this one. That way the vote information can be preserved until the complete issue is resolved.

[Bryan Pendleton]

DERBY-119 and DERBY-1491 have made it into the 10.2 release, yay!
DERBY-1489 has made good progress, but is currently blocked on some
interactions with the new GRANT/REVOKE feature. No significant progress
on DERBY-1490 or DERBY-1515 has occurred recently.

[Bryan Pendleton]

I propose to mark this issue resolved for 10.3.

Can anybody see any part of this feature which has not yet been addressed?

Kathey, I know you were concerned that we not prematurely close this issue. How
do you feel about marking it as resolved at this point?

[Rajesh Kartha]

I tend to agree with Bryan. As I understand ,the only issue that remains is to ALTER a COLUMN's DATATYPE, and the work-around provided in DERBY-1515 seems reasonable enough.

-Rajesh

[Bryan Pendleton]

Thanks Rajesh! I've marked this issue as FIXED in 10.3. If the community feels that there are additional needs in the area of table alteration, please either re-open this issue with information about what is needed, or (probably simpler) open a new issue describing the remaining needs.

[Andrew McIntyre]

This issue has been resolved for over a year with no further movement. Closing.