Adding AUTO_INCREMENT using ALTER in sql

Question

I've been trying to add AUTO_INCREMENT on a created table , but the ALTER table query is not working

My Table:

Query and Error:

And also

ALTER TABLE professor ADD sno INT IDENTITY;

is not working

Answer 1

Can you please try:

alter table professor add sno integer generated by default on null as identity;

EDIT: Sorry, the OP is asking for 11g, as the code above works after 12.

Then you need to use sequence. Please see below:

ALTER TABLE professor ADD sno INT;
CREATE SEQUENCE dept_seq START WITH 1;

And you need to set a trigger for your sequence like:

CREATE OR REPLACE TRIGGER dept_bir 
BEFORE INSERT ON departments 
FOR EACH ROW

BEGIN
  SELECT dept_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;

Reference: How to create id with AUTO_INCREMENT on Oracle?