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:
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:
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 DU...
To continue reading for free, register below or login
To read more you must become a member of SearchEnterpriseLinux.com
AL. 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:
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.
