db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Duncan Groenewald <duncangroenew...@xtra.co.nz>
Subject Re: Frustration: lack of alter table functionality
Date Fri, 08 Dec 2006 19:58:07 GMT
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


Mime
View raw message