Been there done that...  this is what I do now to make making changes easy.


1.  Define tables the normal way but don't include any constraint definitions in the table definition.
2.  Define all constraints(foreign keys etc.) as separate alter table commands. with easy to use constraint names.

create table WalletDetail
   (ID              int              NOT NULL,
    UserName        varchar(32)      DEFAULT USER NOT NULL,
    Detail          long varchar      ,
    TStamp          timestamp        DEFAULT CURRENT_TIMESTAMP  NOT NULL,
    PRIMARY KEY (ID)
);
alter table WalletDetail add CONSTRAINT WalletDetailFK01 FOREIGN KEY (ID)
REFERENCES WalletStructure(ID);

If you subsequently want to change the table definition then do the following:
1.  Create a new table using the new table definition(in this example I just modify the UserName field from a varchar(32) to a varchar(64).
2.  Copy the data from the old table to the new one.

create table tmp_WalletDetail
   (ID              int              NOT NULL,
    UserName        varchar(64)      DEFAULT USER NOT NULL,
    Detail          long varchar      ,
    TStamp          timestamp        DEFAULT CURRENT_TIMESTAMP  NOT NULL,
    PRIMARY KEY (ID)
);

insert into tmp_WalletDetail(ID, UserName,Detail)
select ID, UserName, Detail from WalletDetail ;

3.  Now drop any FOREIGN KEYS defined on other tables that refer to the original table above and drop this table.  You will get errors if you have not removed all these references.  I keep the whole definition in a text file and copy all the required commands into a script to make it easier.  Forget trying to memorise anything.  I use jEdit with the SQL plugin to send the commands directly to the database.

4.  Now create a new table with the original tables name and copy everything back.  Now recreate all the FOREIGN KEYS and other constraints that refer to this table and your back in business again.

5. Don't forget to back everything up before you start !!



On 9/12/2006, at 3:42 AM, Alexander Schatten wrote:

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