Oracle on Linux tips, tutorials and expert advice

More Oracle on Linux tips from the experts <<previous|next>> :Setting up a cluster with Linux and Oracle

Unix-to-Linux migration

Sequence number differences between Oracle and MySQL

By Nigel McFarlane

SearchEnterpriseLinux.com

If you're working with relational data, the day will soon come when you need to generate unique key values for your data, otherwise known as sequence numbers. In this tip I'll contrast the Oracle way with the MySQL way.

Since sequence numbers aren't part of the entry-level conformance of the SQL92 standard, their use creates portability problems for your SQL scripts straight away.

Way back when, there were no sequence number features in any relational database. In those days, if you wanted to generate unique numbers (like invoice numbers), you had to store them separately in a whole table, and increment them yourself:

CREATE TABLE my_sequence (id INT NOT NULL);

INSERT INTO my_sequence VALUES (0);

UPDATE my_sequence SET id = id + 1;

INSERT

INTO my_table

SELECT id, "some data"

FROM my_sequence;

That approach was rather clunky to say the least, but at least it is standard SQL.

In modern Oracle databases, all you need to do is create a sequence object, and then select new numbers from it:

CREATE SEQUENCE my_sequence;

INSERT

INTO my_table

SELECT my_sequence.nextval, "some data"

FROM dual;

The special table DUAL contains exactly one row and one column and is used mostly to report back a single piece of data - in this case derived from a sequence object. In all my life I've never been able to understand why a dummy table with one row and one column is called DUAL. Doesn't DUAL mean two-part or two-use? What's dual about a single row? Oh well.

By comparison, MySQL has no DUAL table. Instead, it allows you to put an extra detail on any numeric column of any table, provided it's an indexed key column. Not only can the column be NULLable, it can also be AUTO_INCREMENTable. Here's an example:

CREATE TABLE my_table (

id INT NOT NULL AUTO_INCREMENT,

data CHAR(30),

PRIMARY_KEY(id)

);

INSERT INTO my_table (null, "some data");

The INSERT statement looks highly suspect. After all, doesn't a primary key normally require a concrete value? True, and in this case MySQL calculates that for you. By passing in NULL to the column with the AUTO_INCREMENT property, you're telling MySQL to use the next free number not yet used for that column. If this INSERT statement were to be applied three times, each time the ID value would be one larger.

For the first three rows inserted, that number would merely be 1, then 2, then 3.

The fact that the sequence number is tied to a specific table in MySQL is a little restrictive, but there are various techniques that can be used to work around it. For example, MySQL provides a LAST_INSERT_ID() that can be used to dig the generated number out for other purposes.


21 Jan 2005

Related Content

Related glossary terms

Terms from Whatis.com − the technology online dictionary
Unix-to-Linux migration
  • Unix  (searchEnterpriseLinux.com)

Related Resources

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.