db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Alexander Schatten <ala...@gmx.at>
Subject Frustration: lack of alter table functionality
Date Fri, 08 Dec 2006 14:42:02 GMT
I have to say, that I am really frustrated today. I evaluated Derby for 
some time, and liked it for many reasons, but then I realise that Derby 
does have significant limits in the alter table syntax: it is neither 
possible to properly change the type of a column, nor to rename a column 
or table and particularly not to drop a column.

so unfortunately this is really a show-stopper that makes me deeply worried.

I made the mistake, that I have a column in one table declared as 
timestamp and want to change it to date. so what should I do? changing 
is not possible. So I thought, I create a new column, copy the values 
(not easily possible either) then drop the original one then rename the 

I created the second one just to figure out that there is no option to 
drop a column. actually, I never thought that such a fundamental command 
is missing...

now this gets serious. the only option I see now is: to somehow backup 
all data from this table, then drop the table, but before this: drop all 
constraints refering to this table (a LOT in this case!! foreign keys), 
memorize all those constreints. then recreate this table restore the 
data, which is probably very, very difficult because of the foreign key 
constraints, so in effect I probably have to recreate the whole database 
and figure out a restore option then.

restore is also an issue, as I use identity columns, and I was once 
trying to import data and then had big problems in getting identity 
columns running again.

ok, at the moment this is only a development database, and I can 
recreate it (with all test data), but I am thinking: what happens if I 
have to do this when there are live systems running? I cannot easily 
provide a migration script for such an update. this means that such 
restructurings are practically impossible or really big undertakings 
with the need to shutdown databases, making backups and restores, 
probably needing to write software that makes the migration... hence 
really a big deal.

for what? altering one column...???? this is hard to justify!!

honestly, this is so severe, that I am considering to go back to hsqldb, 
that has some other limits that are really not nice.

however, an important argument for java databases is the good 
integration in the development process; and restructuring databases is a 
very important issue here.

can someone give me a hint how I could solve this problem in an easier 
way with derby? did I oversee something? or is it foreseeable that a new 
version of Derby will come out supporting proper alter table statements?

thank you very much!


View raw message