Creating auto increament column in oracle

Unlike MySQL, Oracle has not auto_increament attribute when you create a table. In MySQL, we can create table with auto increament column in simple way like this:

CREATE TABLE test(
    id INT primary key auto_increment,
    name varchar(20)
);

To insert a row into [cci]test[/cci] table, simply leave blank for [cci]id[/cci] column, MySQL will automatically increse the value on every insert statement.

INSERT INTO test(name) values('Mahmud');

Above statement will resulting:

id            name
1             Mahmud

How about auto increament in Oracle. Auto increament in oracle not as simple as in MySQL. Look at below codes to create auto increament in oracle.

CREATE TABLE test(
id NUMBER primary key,
name varchar2(20)
);

CREATE SEQUENCE test_sequence
START WITH 1
INCREMENT BY 1;

CREATE OR REPLACE TRIGGER test_trigger
BEFORE INSERT
ON test
REFERENCING NEW AS NEW
FOR EACH ROW
BEGIN
SELECT test_sequence.nextval INTO :NEW.ID FROM dual;
END;

Leave a Comment Cancel Reply