AutoIncrement sequences in Oracle/Postgres/MySql

Came across a situation, where I need to perform Auto insert on a Table's column in a sequential key order, and that too be compatible with Oracle, Postgres and MySql.

And here is how the comparision goes....,

Ofcourse we can use TableMax + 1, but the overhead is the performance...So decided to go with Pseudo columns..

Pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values.
Thus suggests the use of Sequences., that can generate unique sequential values. These values are often used for primary and unique keys. You can refer to sequence values in SQL statements with these pseudocolumns:

    * CURRVAL: Returns the current value of a sequence
    * NEXTVAL: Increments the sequence and returns the next value

A sequence can be accessed by many users concurrently with no waiting or locking.

Create Sequence:

CREATE SEQUENCE sequence_name
    MINVALUE value
    MAXVALUE value
    START WITH value
    INCREMENT BY value
    CACHE value;

Sequences are independant of any table and can be used to keep a value unique across a number of tables.

MySQL doesn't currently support sequences. There is no sequence number generator (CREATE SEQUENCE) in MySQL. However it does have an auto increment value which can be applied to a primary key of a table. This is done during the table creation.

mysql> create table seq_test (id int primary key auto_increment, name  varchar(21));

As the name suggests the value is assigned automatically, this is in contrast to Oracle where we have to call the nextval function of the sequence to return the value when we need it.

In Postgres you can use type SERIAL during table creation which is similar to AUTO_INCREMENT in MySQL.

pgsql=> CREATE TABLE seq_test (id    SERIAL PRIMARY KEY);

 Is just shorthand notation for:

pgsql=> CREATE SEQUENCE XXX START 1;
pgsql=> CREATE TABLE seq_test (id integer PRIMARY KEY DEFAULT nextval('XXX'));

Well the shorthand notation has a minor gotcha: you cannot drop the sequence that has been created automatically. You need to drop the column itself.
Thats the difference between a SERIAL PRIMARY KEY definition and the "verbose" mode.

Verbosely you can have even more control over the sequence. With SERIAL the default is something like

CREATE SEQUENCE XXX
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;

By hand you can define e.g.

CREATE SEQUENCE XXX
    START n
    INCREMENT BY n
    MAXVALUE n
    MINVALUE n
    CACHE 1;

Insert in to Table using seq nextVal.

pgsql=> insert into seq_test(id, name)values(nextval('s1'), test1);
INSERT 0 1
pgsql=> select * from seq_test;
 id          name
------------------
  1          test1
(1 row)

We can also insert value of our choosing.

pgsql=> insert into seq_test(id, name)values('100', 'test2');
INSERT 0 1
pgsql=> select * from seq_test;
 id             name
-------------------------
   1            test1
 100            test2
(2 rows)

Thus, the sequence will start from its own higher point, not related to any of the table values.

pgsql=> insert into seq_test(id, name)values(nextval('s1'), 'test3');
INSERT 0 1
pgsql=> select * from seq_test;
 id             name
---------------------------
   1           test1
 100           test2
   2           test3
(3 rows)

However the same is different in MySQL If you use AUTO_INCREMENT.

mysql> insert into seq_test (name) values ('test1');

mysql> select * from seq_test;
+----+------+
| id | name |
+----+------+
|  1 | test1 |
+----+------+
1 row in set

However you can override the value of your choice.

mysql> insert into seq_test (id,name) values (100,'test2');
Query OK, 1 row affected

mysql> select * from seq_test;
+-----+------+
| id  | name |
+-----+------+
|   1 | test1 |
| 100 | test2 |
+-----+------+
2 rows in set

The sequence will then start from this new higher point.

mysql> insert into seq_test (name) values ('test3');
Query OK, 1 row affected

mysql> select * from seq_test;
+-----+-------+
| id  | name  |
+-----+-------+
|   1 | test1  |
| 100 | test2  |
| 101 | test3 |
+-----+-------+
3 rows in set

Also you can reset the sequence using an alter table command.

mysql> alter table seq_test auto_increment = 100;
Query OK, 1 row affected

This method could also be used to assign a higher number to start the sequence rather than starting with 1 by calling the alter table straight after the table creation.

So in summary auto_increment offers a great way of assigning a unique value automatically to a table. However what it doesn't allow when compared with an Oracle Sequence is different increment values, ability to use across a number of tables.
Oracle doesn't provide either AUTO_INCREMENT like MySQL or SERIAL like Postgres.,
If you look an equivalent to AUTO_INCREMENT in MySQL.,

-- Won't work:
Sql>CREATE TABLE   (seq_id NUMBER(1) DEFAULT xxx_seq.NEXTVAL);
ORA-00984: column not allowed here

Then how to create an autoincrement field in a table with a sequence ...

-- Will work:

Sql> create table seq_test(id number , name varchar2(21));
Statement processed.

First create a sequence
Sql> create sequence xxx ;
Statement processed.

Then create the trigger.
create trigger yyy before insert on seq_test
for each row
when (new.id is null)
begin
 select xxx.nextval into :new.id from dual;
end;
/
Also, the below works as like Postgres...

Sql>INSERT INTO seq_test(id, name)VALUES(xxx_seq.nextval, 'test1');

However, setting the default vlaue while Table creation to sequence.nextVal in Postgres will work unlike in Oracle.

pgsql=>create sequence xxx;

pgsql=>create table seq_test(id INT DEFAULT nextval('s1') NOT NULL, name varchar(21));

pgsql=> insert into seq_test(name)values('test4');

pgsql=> select * from seq_test;

id | name
----+-------
  3 | test4
(1 row)


Decided to use sequences while Insert.........