If you're working with relational data, the day will soon come when you need to generate unique key values for...
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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;
SELECT id, "some data"
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;
SELECT my_sequence.nextval, "some data"
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,
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.