I am trying to create a field in MySQL that will have values that auto increment. However, I do not want to use auto_increment since this site may be moved at some point to a different SQL server (possibly PostgreSQL). I've seen reference to a SERIAL datatype, but I am not certain how to use it. Can I create the table with the following:
CREATE TABLE records ( recordID NOT NULL SERIAL PRIMARY KEY , record varchar(128) );
and then when inserting the value write
INSERT INTO records (sequence.nextval,2)
and expect to receive the results I want?
That's a really good question, because of the implications of moving database tables which contain autonumbers.
Suppose you have a table that contains an autonumber column. So each row would have some integer in the autonumber column, and all you know for sure is that these numbers would be unique, because there might be gaps in the numbers from previous deletions. Now you have to pick up the data and migrate it to a totally different database. Can you still use an autonumber column, or its equivalent, in the new database? The answer all depends on which database you're going to, and how much work you're prepared to do. The key point is that if you allow new autonumbers to be assigned while loading your data into a new table, and if there was even one gap in the numbers in the old table, you will subsequently have to update all related rows and assign new values for foreign keys!
In databases like MySQL and Microsoft Access and SQL/Server, autonumbering is a property of a table column. In order to load your data into a new table that has an autonumber column, while preserving the numbers assigned by the old database in the autonumber column, the new database must provide a facility to let you (1) override autonumbering while loading your data, and (2) turn autonumbering back on and resume assigning new numbers where your data left off. I believe you can do this easily in SQL/Server. I'm not sure whether you can do it when importing data into Access or MySQL (I'm not saying you can't, I'm saying I don't know).
It turns out that moving autonumbered data to databases like PostgreSQL and Oracle is relatively trivial. In PostgreSQL and Oracle, you "draw" the next number from an object called a sequence, which is separate from the table you're inserting into.
The example you gave above isn't quite right for PostgreSQL. I haven't actually used PostgreSQL myself, but according to the PostgreSQL Interactive Documentation, you would say
CREATE TABLE records ( recordID PRIMARY KEY , record varchar(128) );
CREATE SEQUENCE serial START 101;
INSERT INTO records VALUES (nextval('serial'), 2);
I think the reference to SERIAL you saw was probably Informix. Informix has a SERIAL datatype which apparently works just like an autonumber column in other databases. I haven't actually used Informix either, but according to the Informix Guide to SQL: Syntax, you can enter old data into a SERIAL column:
If you want to enter an explicit value in a SERIAL column, specify the nonzero value after you first verify that the value does not duplicate one already in the table.
So to answer your original question, go ahead and use an autonumber in MySQL, because it's far easier to use one than to emulate one! If you do move to PostgreSQL or Informix, rest assured you'll be able to move your numbers without having to change them.
This was first published in July 2002