I'm attempting to alter a Derby database that has a table like this:
CREATE TABLE sec_merch_categories (
category_id int NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
packageName VARCHAR(100) NOT NULL,
name VARCHAR(100) NOT NULL,
primary key(category_id)
);
I'd like to change the category_id column to be:
category_id int NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1),
The only place I've seen that documents this is IBM's DB2, which advises dropping the expression, changing the integrity of the table, and adding the expression back. Is there anyway of doing this in Derby?
Thanks,
Andrew
You can create a new table with the schema you want (but a different name), then use INSERT INTO ... SELECT FROM ... to copy the data from the old table to the new table (or unload the old table and reload it into the new table using the copy-data system procedures), then use RENAME TABLE to rename the old table to an alternate name and rename the new table to its desired name.
And, as @a_horse_with_no_name indicated in the above comment, all of these steps are documented in the Derby documentation on the Apache website.