Unique Numbers in Oracle and MySQL

It’s common in database work to need unique integers. They get used in id columns in tables, and for other purposes. This note compares and contrasts the Oracle and MySQL approaches to doing this.

MySQL handles this need with autoincrement columns, in tables and Oracle handles it with database objects called sequences.

In MySQL, when you have an autoincrement column and you INSERT a new row in a table, you simply don’t mention the autoincrement column and MySQL puts it there. You can then insert the same number into another table by referencing LAST_INSERT_ID().

 INSERT INTO person  (name, date)                             /*MySQL*/
                 VALUES ('joe', '2015-01-01');
 INSERT INTO contact (person_id, phone)
                 VALUES (LAST_INSERT_ID(), '555-1212');

In Oracle, you can populate an id column by mentioning a sequence object’s .nextval property. You can get the just-used value of that sequence by referencing its .currval property.

 INSERT INTO person (id, name, date)                         -- Oracle
                   VALUES (person_seq.nextval, 'joe', '2015-01-01');
 INSERT INTO contact (id, person_id, phone)
                   VALUES (contact_seq.nextval, person_seq.currval, '555-1212');

Each time you mention the sequence’s .nextval property, it’s guaranteed to give a new number.

Sequence objects are pretty cool when you need unique numbers that aren’t directly related to the primary key of some table or other. You can do this in MySQL, but it’s a kludge: If you create the following table:

 CREATE TABLE sequence (                                       /*MySQL*/
     sequence_id BIGINT NOT NULL AUTO_INCREMENT,
     PRIMARY KEY (`sequence_id`)
) 

Then issue these three queries one after the other:

INSERT INTO sequence () VALUES ();                            /*MySQL*/
DELETE FROM sequence WHERE sequence_id < LAST_INSERT_ID();
SELECT LAST_INSERT_ID() AS sequence;

The third query is guaranteed to return a unique sequence number. This guarantee holds even if you have dozens of different client programs connected to your database. (The DELETE query merely keeps this otherwise pointless table from taking up too much space.)

With Oracle, you create the sequence

create sequence seq                                           --Oracle

and then just do

SELECT seq.nextval FROM DUAL                                  --Oracle

to get a new sequence number and that’s it. It too guarantees uniqueness even with dozens of connected client programs.

Similarly, if you need the value of a sequence you just generated with .nextval, you can issue this command and get it.

SELECT seq.currval FROM DUAL                                 --Oracle

As with MySQL’s LAST_INSERT_ID(), this is handled session by session so another client using the sequence won’t make you get their number instead of yours.

Bottom line: both DBMSs can generate unique integers. The integrity of both schemes is designed to be preserved across server stops and restarts. The Oracle sequence is more flexible.