Auto Increment Column – Sequence as Default Value in Oracle and mysql

Auto Increment Column  is a quite common requirement in the coding.Here are some of the ways to get that in mysql and oracle database

Auto Increment Column Mysql database

The AUTO_INCREMENT attribute can be used to generate a unique identity for new rows

Example

CREATE TABLE test_lab (
id INT NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
) ;

INSERT INTO test_lab (name) VALUES
(‘john’),(‘scott’),(‘bill’),
(‘tom),(‘duke’);

SELECT * FROM test_lab;

 

 

id Name
1 john
2 scott
3 bill
4 tom
5 Duke

No value was specified for the AUTO_INCREMENT column, so MySQL assigned sequence numbers automatically.

Auto Increment Column Oracle database

This feature( Oracle Auto Increment Column – Sequence as Default Value) is not present in Oracle so far. But with 12c, oracle is able to provide that.

Suppose we want to set a table where the sequence should be auto incremented on insert .There are many way to achieve it

Option 1
Prior to Oracle 12c, we need to use before insert triggers to achieve it

create table test_lab
(
id number primary key
name varchar (10)
);

create sequence test_lab_seq start with 1 increment by 1 nocycle;

create or replace trigger test_lab_bef_auto
before insert on test_lab
for each row
begin
select test_lab_seq.nextval into :new.id
from dual;
end;
/

 

or

create or replace trigger test_lab_bef_auto
before insert on test_lab
for each row
begin
:new.id := test_lab_seq.nextval;
end;
/

We can specify when id is null in triggers to avoid the errors when the insert statement is having value of id column

insert into test_lab values (‘john’);

insert into test_lab values (‘scott’);
commit;
select * from
test_lab;

 

id Name
1 john
2 scott
3 bill
4 tom
5 Duke

Option 2:
With Oracle 12c, we can directly assign sequence nextval as a default value for a column, So you no longer need to create a trigger to populate the column with the next value of sequence, you just need to declare it with table definition.

create sequence test_lab_seq start with 1 increment by 1 nocycle;

create table test_lab
(
id number default test_lab_seq.nextval primary key
);

insert into test_lab values (‘john’);

insert into test_lab values (‘scott’);
commit;

select * from
test_lab;

 

id Name
1 john
2 scott
3 bill
4 tom
5 Duke

Option 3
With 12c , we can use the identify feature

create table test_lab (
id number(10) GENERATED AS IDENTITY,
name varchar(15)
);

Table created.

desc TEST_LAB
Name Null? Type
———————– ——– —————-
ID NOT NULL NUMBER(10)
NAME VARCHAR2(15)

We can see that NOT NULL constraint is automatically imposed on the Identity column

Oracle internally use sequence to populate the auto-increment column values.

insert into test_lab values (‘john’);

insert into test_lab values (‘scott’);
commit;

select * from
test_lab;

 

 

id Name
1 john
2 scott
3 bill
4 tom
5 Duke

Related Oracle sql Articles

how to create table in oracle

How to create Oracle views

Top-N Queries and Pagination

how to write sql queries

Oracle sql decode processing