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:

[sourcecode lang=”plain”]
CREATE TABLE test(
id INT primary key auto_increment,
name varchar(20)
);
[/sourcecode]

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.

[sourcecode lang=”plain”]
INSERT INTO test(name) values(‘Mahmud’);
[/sourcecode]

Above statement will resulting:

[sourcecode lang=”plain”]
id name
1 Mahmud
[/sourcecode]

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.

[sourcecode lang=”plain”]
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;
[/sourcecode]