Autoincrement Fields on Databases Without Autoincrement Field

AutoIncrement fields on databases without autoincrement field

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.

How to increment id without auto increment?

If you were doing this in Oracle's table server, you would use different SEQUENCE objects for each type of account.

The MariaDB fork of MySQL has a similar kind of SEQUENCE object, as does PostgreSQL. So if you were using MariaDB you would do something like this.

CREATE SEQUENCE IF NOT EXISTS org_account_id MINVALUE=10000 MAXVALUE=999999;
CREATE SEQUENCE IF NOT EXISTS user_account_id MINVALUE=1000000;

Then to use a sequence in place of autoincrement you'll do something like this.

INSERT INTO tbl (id, col1, col2) 
         VALUES (NEXTVAL(user_account_id), something, something);

In MySQL you can emulate sequence objects with dummy tables containing autoincrement ids. It's a kludge. Create the following table (one for each sequence).

CREATE TABLE user_account_id (
     sequence_id BIGINT NOT NULL AUTO_INCREMENT,
     PRIMARY KEY (`sequence_id`)
);
ALTER TABLE user_account_id AUTO_INCREMENT=1000000;

Then issue these queries one after the other to insert a row with a unique user id.

INSERT INTO user_account_id () VALUES (); 
DELETE FROM sequence WHERE sequence_id < LAST_INSERT_ID();
SET @id:=LAST_INSERT_ID();
INSERT INTO tbl (id, col1, col2) 
         VALUES (@id, something, something); 

After your insert into the dummy table, LAST_INSERT_ID() returns a unique id. The DELETE query merely keeps this dummy table from taking up too much space.

mysql auto increment without inserting

If you are using it as an ID generator, then (aside from altering the table) you'll have to insert something to get the next value.

The easiest way to do this without inserting any data would be to do an INSERT followed by calling LAST_INSERT_ID followed by issuing a ROLLBACK. That will rollback the insert, but the next ID will still be incremented.

Concerns using an auto increment field as a unique identifier in MYSQL

Just make the change.

• auto_increment must be a primary key. This means that duplicate values are not allowed. Typically you should use int unsigned not null auto_increment primary key for the definition. No need for negative numbers.
• This is a transparent identifier, so there should be no reference to its actual value anywhere. It may be convenient to reference the actual value for maintenance, but there shouldn't be any concerns about the value changing if the data is copied to a new DB. You can INSERT a value to that column to whatever you want as long as it does not already exist.

Change mysql auto increment id column value

MySQL, just like other databases, does not guarantee the absence of gaps in auto-increment column. Renumbering the table may fix the issue at a particular point in time, but gaps may (and most likely) will appear in the future.

If you want a monotically increasing sequence, then auto-increment is not what you need. Instead, you can for example create a view, and use row_number():

create view myview as
select t.*, row_number() over(order by gpid) as new_gpid
from mytable t

Why do we use primary key auto increment, and not just auto_increment?

Because of constraints, because SQL is a declarative language, and because it is self-documenting.

AUTO_INCREMENT and PRIMARY KEY do two very different things.

AUTO_INCREMENT is not standard SQL, but most databases have something like it. In usually makes the default an incrementing integer. That's the mechanistic element of a primary key: an auto-incrementing integer is a decent unique id.

PRIMARY KEY does a number of things.

1. The columns are not null.
2. The columns are unique.
3. (Non-standard, but almost always) The columns are indexed.
4. The columns are declared the primary key.
5. No other columns can be declared the primary key.

Most are constraints on the value. It must exist (1), it must be unique (2), and it must be the only primary key (5). Constraints guarantee the data is as you expect. Without them you might accidentally insert multiple rows with the same primary key, or with no primary key. Or the table might have multiple "primary" keys, which do you use? With constraints you don't have to check the data every time you fetch it, you know it will within its declared constraints.

Indexing the primary key (3) is an optimization. You're probably going to be searching by primary key a lot. Indexes aren't part of the SQL standard. That might be surprising. They're a detail of implementing SQL. Indexes don't affect the result of the query, and the SQL standard avoids telling databases how they should do things. This is part of why SQL is so ubiquitous, it is declarative.

In a declarative language you don't say how to do it, you say what you want. A SQL query says what result you want and the database figures it out for you. You could implement most of the above as constraints and triggers, but if you did the database wouldn't understand why you're doing that and would not be able to use that to help you out. By stating to the database "this is the primary key" and it can handle that as it sees fit. The database adds the necessary constraints. The database adds its optimizations, which can be more than indexing. The database can use this information in queries to identify unique rows. You get the benefit of 50 years of database theory.

Finally, it lets people know the purpose of the column. Anyone can read the schema and know that column is the primary key. That anyone can be you six months from now.

---

Related Topics