How to alter column from PRIMARY KEY to IDENTITY for Derby

Question

The SQL for the creation of the table is:

CREATE TABLE myTable(id INTEGER NOT NULL PRIMARY KEY, ...)

Instead I need it to be:

CREATE TABLE myTable(id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1), ...)

as described in the Derby documentation. So my question is what would be the alter statement I would need to create AFTER the initial create statement? In other words:

CREATE TABLE myTable(id INTEGER NOT NULL PRIMARY KEY, ...)
ALTER TABLE myTable ...

Thank you very much for the assistance!

Answer 1

Looking at the documentation this seems impossible. You can change the type length (not even the type itself), the default, nullability and the next generated value but even the last option requires the column to already be defined as IDENTITY. A thread from 2009 says that you can't even add an IDENTITY column. A test confirms this is true to this day.

So it seems there is only one solution: You have to replace the table. Something like this:

1. create a new table with a placeholder name that contains the desired columns
2. copy any data over from the original table
3. drop the original table
4. rename the new table

It's really an unfortunate solution because if you already have other tables referencing the id column of your table as that would mean further work.

I tried messing with the system tables but they seem to be read-only (and for good reason).

Answer 2

Looks like this issue in Derby has been fixed as of the 10.12.1.1 release. Now commands such as:

ALTER TABLE t ADD COLUMN x INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY

to an existing database now work, as does GENERATED BY DEFAULT. Looks like the change requires the underlying database to be at least in 10.11 format.

Answer 3

One technique is to: (a) create a new table with the new column defined as you desire, and all other columns as they were before, (b) run an INSERT INTO ... SELECT ... statement to copy all the data from the existing table to the new table, (c) RENAME TABLE to rename the old table to some other name, (d) RENAME TABLE to rename the new table to the correct tablename, and then finally (e) DROP TABLE the old table.