AutoIncrement fields on databases without autoincrement field

Question

In MS Sql Server is easy create autoincrement fields. In my systems I stopped to use autoincrement fields for primary keys, and now I use Guid's. It was awesome, I've got a lot of advantages with that change. But in another non-primary key fields, I really was needing implement a "soft autoincrement". It's because my system is DB independent, so I create the autoinc value programatically in c#.

I would like about solutions for autoincrement fields on databases without autoincrement, what the solution that your use and why? There is some Sql Ansi statement about this? and generating directly from my c#, is a better solution?

PS: I know that select max(id)+1 from table it's not really concurrent friendly...

Pitchstone

I would suggest rewriting the title to something that is a bit more clear like "Creating database independent autoincrement fields." My concern is that I don't think I would find your title when I would do a search for the same topic. – Burcham

Do you really need a continuously incrementing number, or do you just need uniqueness? – Unlucky

Yes I need "a continuously incrementing number", for another situations I use guids, CRC, etc... – Pitchstone

Answer 1

The mechanism to generate unique id values must not be subject to transaction isolation. This is required for the database to generate a distinct value for each client, better than the trick of SELECT MAX(id)+1 FROM table, which results in a race condition if two clients try to allocate new id values concurrently.

You can't simulate this operation using standard SQL queries (unless you use table locks or serializable transactions). It has to be a mechanism built into the database engine.

ANSI SQL did not describe an operation to generate unique values for surrogate keys until SQL:2003. Before that, there was no standard for auto-incrementing columns, so nearly every brand of RDBMS provided some proprietary solution. Naturally they vary a lot, and there's no way to use them in a simple, database-independent manner.

• MySQL has the AUTO_INCREMENT column option, or SERIAL pseudo-datatype which is equivalent to BIGINT UNSIGNED AUTO_INCREMENT;
• Microsoft SQL Server has the IDENTITY column option and NEWSEQUENTIALID() which is something between auto-increment and GUID;
• Oracle has a SEQUENCE object;
• PostgreSQL has a SEQUENCE object, or SERIAL pseudo-datatype which implicitly creates a sequence object according to a naming convention;
• InterBase/Firebird has a GENERATOR object which is pretty much like a SEQUENCE in Oracle; Firebird 2.1 supports SEQUENCE too;
• SQLite treats any integer declared as your primary key as implicitly auto-incrementing;
• DB2 UDB has just about everything: SEQUENCE objects, or you can declare columns with the "GEN_ID" option.

All these mechanisms operate outside transaction isolation, ensuring that concurrent clients get unique values. Also in all cases there is a way to query the most recently generated value for your current session. There has to be, so you can use it to insert rows in a child table.

Mousse

in Firebird 2.1 : there is also SEQUENCE firebirdsql.org/rlsnotesh/… – Troubadour

@Hugues: Thanks for the tip! I have added it to the list. – Mousse

SQL-Server has also the NEWSEQUENTIALID() which is something between auto-incrementing and GUID. – Zoochore

Answer 2

I think your question is actually quite a good one. However, it is easy to get lost trying to come up with a SQL only solution. In reality you will want the optimization and transaction safety afforded by using the database implementations of the autoincrement types.

If you need to abstract out the implementation of the autoincrement operator, why not create a stored procedure to return your autoincrement value. Most SQL dialects access stored procedures in relatively the same way and it should be more portable. Then you can create database specific autoincrement logic when you create the sproc - eliminating the need to change many statements to be vendor specific.

Done this way, your inserts could be as simple as:

INSERT INTO foo (id, name, rank, serial_number)
 VALUES (getNextFooId(), 'bar', 'fooRank', 123456);

Then define getNextFooId() in a database specific way when the database is being initialized.

Burcham

Answer 3

Most databases that don't have autoincrement fields like SQL Server (I'm thinking Oracle specifically) have sequences where you ask the Sequence for the next number. No matter how many people are requesting numbers at the same time everyone gets a unique number.

Redhanded

Answer 4

The traditional solution is to have a table of ids that look something like this

CREATE TABLE ids (
  tablename VARCHAR(32) NOT NULL PRIMARY KEY,
  nextid INTEGER
)

which s populated with one row per table when you create the database.

You then do a select to get the next next id for the table you are inserting into, increment it and then update the table with the new id. Obviously, there are locking issues here, but for databases with moderate insert rates it works well. And it is completely portable.

Influential

This solution is subject to race conditions, and every major RDBMS has a transaction-safe alternative. – Mousse

Answer 5

If you need a non-primary-key autoincrement field, a very nice MySQL only solution for creating arbitraty sequences is to use the relatively unknown last_insert_id(expr) function.

If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID(). This can be used to simulate sequences...

(from http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_last-insert-id )

Here is an example which demonstrates how a secondary sequence can be kept for numbering comments for each post:

CREATE TABLE  `post` (
  `id` INT(10) UNSIGNED NOT NULL,
  `title` VARCHAR(100) NOT NULL,
  `comment_sequence` INT(10) UNSIGNED NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
);

CREATE TABLE  `comment` (
  `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `post_id`  INT(10) UNSIGNED NOT NULL,
  `sequence` INT(10) UNSIGNED NOT NULL,
  `content` TEXT NOT NULL,
  PRIMARY KEY (`id`)
);

INSERT INTO post(id, title) VALUES(1, 'first post');
INSERT INTO post(id, title) VALUES(2, 'second post');

UPDATE post SET comment_sequence=Last_insert_id(comment_sequence+1) WHERE id=1;
INSERT INTO `comment`(post_id, sequence, content) VALUES(1, Last_insert_id(), 'blah');

UPDATE post SET comment_sequence=Last_insert_id(comment_sequence+1) WHERE id=1;
INSERT INTO `comment`(post_id, sequence, content) VALUES(1, Last_insert_id(), 'foo');

UPDATE post SET comment_sequence=Last_insert_id(comment_sequence+1) WHERE id=1;
INSERT INTO `comment`(post_id, sequence, content) VALUES(1, Last_insert_id(), 'bar');

UPDATE post SET comment_sequence=Last_insert_id(comment_sequence+1) WHERE id=2;
INSERT INTO `comment`(post_id, sequence, content) VALUES(2, Last_insert_id(), 'lorem');

UPDATE post SET comment_sequence=Last_insert_id(comment_sequence+1) WHERE id=2;
INSERT INTO `comment`(post_id, sequence, content) VALUES(2, Last_insert_id(), 'ipsum');

SELECT * FROM post;
SELECT * FROM comment;

Bes