Alter a table column with auto increment by 1 in d

Question

I have created a table in derby Netbeans and now i realize that i need to make a column as auto incremented by 1 which is a primary key. How can i do so? I tried the following code but was in vain.

ALTER TABLE ISSUERECIPT ALTER IRCODE SET INCREMENT BY 1;

Do i need to create the table once again or can it be possible some other way?

Answer 1

ALTER TABLE ISSUERECIPT  ADD IRCODE INTEGER NOT NULL primary key GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),

I guess could do the things for you

Answer 2

Recreate the table again see example below:

CREATE TABLE students
(
id INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1),
name VARCHAR(24) NOT NULL,
address VARCHAR(1024),
CONSTRAINT primary_key PRIMARY KEY (id)
) ;

Answer 3

ALTER TABLE tbl ADD id INT PRIMARY KEY AUTO_INCREMENT;

Answer 4

I have found an alternate solution, i dropped the column from the database (thanks vels4j) added the column once again from the netbeans derby UI as shown below:

Answer 5

Try this :

alter table ISSUERECIPT modify column IRCODE int(4) auto_increment

Answer 6

To alter the column to be auto-generated, the code is

ALTER TABLE ISSUERECIPT ALTER IRCODE SET INCREMENT BY 1;

BUT the column must already be defined with the IDENTITY attribute (as written in this documentation).

In most cases (assuming that you too), the primary key column is not set as IDENTITY. Therefore, you may intend to alter the column to IDENTITY, but that is impossible.

The only way is to drop the table and create it again, as written here.