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
second.
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!
Alex
