You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Jakub Jonik <ku...@gmail.com> on 2007/08/15 16:10:50 UTC

Identity column

I am trying to add an Identity column to an existing table with the
following SQL:
ALTER TABLE DummyTable ADD COLUMN DummyColumn GENERATED BY DEFAULT AS
IDENTITY START WITH 1, INCREMENT BY 1.

And I am getting the error:
SQL state 42601: ALTER TABLE statement cannot add an IDENTITY column to a table.

Is there any way to work around this?

Thanks
Jakub

Re: Identity column

Posted by Bryan Pendleton <bp...@amberpoint.com>.
> That's fine if there are no triggers or other objects associated with the
> original table. Is there a system procedure or the like for finding such
> objects?

Absolutely!

http://db.apache.org/derby/docs/dev/ref/rrefsistabs38369.html

thanks,

bryan

Re: Identity column

Posted by Luan O'Carroll <lu...@xoetrope.com>.


Bryan Pendleton wrote:
> 
>> SQL state 42601: ALTER TABLE statement cannot add an IDENTITY column to a
>> table.
>> 
>> Is there any way to work around this?
> 
> Perhaps:
> 
> 1) create a new table with all the columns of the existing table,
>     plus the new identity column.
> 2) INSERT INTO new-table SELECT * FROM old-table to copy all the
>     data from the old table to the new table.
> 3) drop the old table
> 4) rename the new table to be the old table name
> 
> thanks,
> 
> bryan
> 
> 
> 
> 

That's fine if there are no triggers or other objects associated with the
original table. Is there a system procedure or the like for finding such
objects?

-- 
View this message in context: http://www.nabble.com/Identity-column-tf4273368.html#a12183301
Sent from the Apache Derby Users mailing list archive at Nabble.com.

Re: Identity column

Posted by Bryan Pendleton <bp...@amberpoint.com>.
> SQL state 42601: ALTER TABLE statement cannot add an IDENTITY column to a table.
> 
> Is there any way to work around this?

Perhaps:

1) create a new table with all the columns of the existing table,
    plus the new identity column.
2) INSERT INTO new-table SELECT * FROM old-table to copy all the
    data from the old table to the new table.
3) drop the old table
4) rename the new table to be the old table name

thanks,

bryan

Re: Identity column

Posted by Kathey Marsden <km...@sbcglobal.net>.
Knut Anders Hatlen wrote:
> Jakub Jonik <ku...@gmail.com> writes:
>
>   
>> I am trying to add an Identity column to an existing table with the
>> following SQL:
>> ALTER TABLE DummyTable ADD COLUMN DummyColumn GENERATED BY DEFAULT AS
>> IDENTITY START WITH 1, INCREMENT BY 1.
>>
>> And I am getting the error:
>> SQL state 42601: ALTER TABLE statement cannot add an IDENTITY column to a table.
>>
>> Is there any way to work around this?
>>     
>
> There is actually code for it (for instance in
> AlterTableConstantAction.updateNewAutoincrementColumn()), but it is
> disabled in the parser with a reference to bug 5724. Does anyone have
> any details on why it is disabled?
>
>   
The bug notes indicate it was disabled for db2 compatiblity mode which 
no longer exists.

Kathey

Re: Identity column

Posted by Knut Anders Hatlen <Kn...@Sun.COM>.
Jakub Jonik <ku...@gmail.com> writes:

> I am trying to add an Identity column to an existing table with the
> following SQL:
> ALTER TABLE DummyTable ADD COLUMN DummyColumn GENERATED BY DEFAULT AS
> IDENTITY START WITH 1, INCREMENT BY 1.
>
> And I am getting the error:
> SQL state 42601: ALTER TABLE statement cannot add an IDENTITY column to a table.
>
> Is there any way to work around this?

There is actually code for it (for instance in
AlterTableConstantAction.updateNewAutoincrementColumn()), but it is
disabled in the parser with a reference to bug 5724. Does anyone have
any details on why it is disabled?

-- 
Knut Anders