CODEWITHSUNDEEP
sqlderby
John Vasiliou
John Vasiliou

Reputation: 997

Altering my primary key to auto increment - JavaDB

I am using Netbeans, writing in Java and using Derby.

I have a table within APP called PERSON. Within PERSON I have a column called PID with the following properties:

Name: PID
Nulls allowed: [ ]
Data type: NUMERIC
Column size: 4
Decimal digits: 0
Position: 1
Part of a primary key: [/]
Part of an index: [/]

I used the meta data isAutoIncrement function to check if it was already auto incrementing and it is not!

I have since tried using the following SQL commands to alter it:

I believe this may not have been for Derby:

ALTER TABLE APP.PERSON ALTER PID NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY 
(START WITH 1, INCREMENT BY 1);

Upon checking the Oracle website, I found the correct syntax:

ALTER TABLE APP.PERSON ALTER PID SET INCREMENT BY 1;

I even tried leading zeros:

ALTER TABLE APP.PERSON ALTER PID SET INCREMENT BY 0001;

None of which have worked, the error I get on the last two are:

ALTER TABLE '"APP"."PERSON"' specified attributes for column 'PID' that are 
not compatible with the existing column.

Any ideas of the correct syntax?

Upvotes: 1

Views: 3605

Answers (2)

Martin Kinuthia
Martin Kinuthia

Reputation: 1111

JavaDB does not allow altering a column with generated key word so I found the best way is to recreate the table and specify the primary key as auto incremented. For example;

create table staff(
   ID int primary key always generated as identity,
   name varchar(100)
);

This worked for me.

Upvotes: 0

Bryan Pendleton
Bryan Pendleton

Reputation: 16389

Here's what I generally do to accomplish this:

  1. Create a new table, with the desired schema, including the generated primary key
  2. Issue a INSERT INTO newtable SELECT columns FROM oldtable to populate the new table's data from the old table
  3. Rename the old table to some temporary name, like table_soon_to_be_deleted
  4. Rename the new table to the desired table name
  5. Do some testing to make sure that my behavior is as expected
  6. Drop the old table that I renamed in step (4).

Upvotes: 1

Related Questions