How to modify column to auto increment in PL SQL Developer?

Question

I have created one table in PL SQL Developer.

CREATE TABLE Patient_List
(
   Patient_ID number NOT NULL,
   Patient_Name varchar(50) NOT NULL,
   Patient_Address varchar(100) NULL,
   App_Date date NULL,
   Descr varchar(50),
   CONSTRAINT patient_pk PRIMARY KEY(Patient_ID)
);

I want to auto increment Patient_ID, I tried altering the table and modifying the Patient_ID column but it's showing an error "invalid ALTER TABLE option"

ALTER TABLE Patient_List
MODIFY Patient_ID NUMBER NOT NULL GENERATED ALWAYS AS IDENTITY;

Please help, Thanks in advance.

What is your Oracle version? (`SELECT version FROM PRODUCT_COMPONENT_VERSION where product like 'Oracle%';` will tell you) — a_horse_with_no_name, Jan 08 '19 at 09:49
Identity columns were introduced in Oracle 12.1 (and even there you can't change an existing column to an identity column - you can only modify exiting identity columns or **add** a new identity column) — a_horse_with_no_name, Jan 08 '19 at 09:53
as a_horse_with_no_name said you cannot use identity colum unless your version is 12. You can creat a `sequence` and write `trigger` to insert values automatically or access sequence from your code and set it as the value to `Patient_ID `. — Dushmantha, Jan 08 '19 at 11:20

score 3 · Accepted Answer · answered Jan 08 '19 at 11:31

This is not possible.

Oracle 10g didn't even have identity columns, they were introduced in Oracle 12.1

But even with a current Oracle version, you can't convert a regular column to an identity column. You would need to add a new one.

Before identity columns, the usual way was to create a sequence and a trigger to populate the column.

See here: How to create id with AUTO_INCREMENT on Oracle?

score 0 · Answer 2 · answered Jun 24 '20 at 09:09

If anybody wants to modify existing column as auto_increment use this three lines

alter table Product drop column test_id;

create sequence Product_test_id_seq INCREMENT BY 1 nocache;

alter table Product add test_id Number default Product_test_id_seq.nextval not null;

How to modify column to auto increment in PL SQL Developer?

2 Answers2