Search code examples
sqlderby

How to alter column from PRIMARY KEY to IDENTITY for Derby

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!

Solution

  • 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).