CODEWITHSUNDEEP
sqldatabasederby
Nitesh Verma
Nitesh Verma

Reputation: 1815

Alter a table column with auto increment by 1 in derby

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?

Upvotes: 10

Views: 49513

Answers (8)

Faxon Lander Montenegro
Faxon Lander Montenegro

Reputation: 517

The ALTER TABLE statement cannot add an IDENTITY column to a table

If your table is empty or is not in production. drop table and create again, example:

DROP TABLE CUSTOMER;
CREATE TABLE CUSTOMER
(CUSTOMER_ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 
1),
FIRSTNAME VARCHAR(100) NOT NULL,VARCHAR(100),
PREFERRED_ID INTEGER,
CONSTRAINT primary_key PRIMARY KEY (CUSTOMER_ID)
);

Upvotes: 0

Michael Karaz
Michael Karaz

Reputation: 1

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)
) ;

Upvotes: -1

FaizFizy
FaizFizy

Reputation: 459

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.

Upvotes: 4

Nitesh Verma
Nitesh Verma

Reputation: 1815

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:

enter image description here

Upvotes: 11

vels4j
vels4j

Reputation: 11298

Check this

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

If your table is empty, Try this

ALTER TABLE DROP  PRIMARY KEY your_primaryKeyContrainName ; 
ALTER TABLE ISSUERECIPT DROP COLUMN IRCODE ;
ALTER TABLE ISSUERECIPT ADD COLUMN 
IRCODE PRIMARY KEY INTEGER NOT NULL 
GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1);

See Also : Derby ALTER TABLE Syntax

Upvotes: 1

arvin_codeHunk
arvin_codeHunk

Reputation: 2390

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

Upvotes: 1

Naveen Bangwal
Naveen Bangwal

Reputation: 223

ALTER TABLE tbl ADD id INT PRIMARY KEY AUTO_INCREMENT;

Upvotes: 0

Asmita
Asmita

Reputation: 1190

Try this :

alter table ISSUERECIPT modify column IRCODE int(4) auto_increment

Upvotes: -1

Related Questions