Making an column auto increment through script

Question

I am little new to Oracle Environment. I have a following script that creates table with few columns in it.

CREATE TABLE CLASS_STUDENT_LECTURES_MAP(
    STUDENT_LECTURES_ID NUMBER(38,0)    NOT NULL, 
    STUDENT_CODE        NUMBER(38,0)    NOT NULL, 
    STUDENT_NAME_DEFINATION_ID  NUMBER(38,0)    NOT NULL, 
    CONSTRAINT STUDENT_LECTURES_ID _PK PRIMARY KEY (STUDENT_LECTURES_ID)
    );

What i want is to make the Primary Key column STUDENT_LECTURES_ID an auto incrementing column, can this be possible through sql script above?

Like in SQL Server you can specify IDENTITY(1,1) to make a column auto-increment. Any such option for oracle?

Answer 1

Autoincrement cannot be done in a straightforward way in oracle. You will have to use a workaround:

1. Create a table

CREATE TABLE CLASS_STUDENT_LECTURES_MAP( STUDENT_LECTURES_ID NUMBER(38,0) NOT NULL, STUDENT_CODE NUMBER(38,0) NOT NULL, STUDENT_NAME_DEFINATION_ID NUMBER(38,0) NOT NULL, CONSTRAINT STUDENT_LECTURES_ID _PK PRIMARY KEY (STUDENT_LECTURES_ID) );

2.. Create a Sequence:

CREATE SEQUENCE INCREMENT_SEQ START WITH 1 INCREMENT BY 1 NOCACHE NOCYCLE;

3.. Create A Trigger:

`CREATE OR REPLACE TRIGGER table_a_BEFORE_INSERT_TRIGGER 
BEFORE INSERT ON table_a 
FOR EACH ROW
BEGIN
  SELECT increment_seq.NEXTVAL
  INTO   :new.id
  FROM   dual;
END;
/`

4.. Test with insert Query:

INSERT INTO CLASS_STUDENT_LECTURES_MAP ( STUDENT_CODE, STUDENT_NAME_DEFINATION_ID) VALUES (10,20);