2

Change the step auto_increment fields increment by

The above link describes how to change the step value of AUTO_INCREMENT in mysql. This will affect all tables.

Is this behavior is specific to mysql? Can someone suggest a DBMS that allows for the step value to be set per table?

Community
  • 1
  • 1
mahesh
  • 597
  • 11
  • 23
  • I am just curious. Why would you want the auto_increment to step by n value? it is mostly intended for primary keys so that you do not have to set it or keep track of the latest (or hash of all values) yourself... If you have a specific field you want to step, why don't you do it by a program or SQL statement? – Saher Ahwal Feb 15 '13 at 15:25
  • @Saher, to ensure two MySQL instances in a cluster don't generate the same id values. – Bill Karwin Feb 15 '13 at 15:27
  • auto increment is per table.... if you have 10 tables, each of them will have their own auto increment – jcho360 Feb 15 '13 at 15:38
  • @BillKarwin if you're using auto-increment in a cluster setup, you're really begging for trouble. This is strongly discouraged. – Romain Feb 15 '13 at 15:55
  • @Romain, I agree it's risky if done improperly, but I have consulted for dozens of MySQL sites and I see it used successfully a lot. It's a topic for another thread though. – Bill Karwin Feb 15 '13 at 16:07

3 Answers3

3

Technically, AUTO_INCREMENT as a keyword is specific to MySQL, because other RDBMS brands implement different features to generate artificial unique id values. MySQL and SQLite are the only widely-used RDBMS that don't allow per-table incrementation options.

Microsoft SQL Server

SQL Server declares a column with an IDENTITY option, and the IDENTITY keyword can take arguments for the initial value and the increment, so you can vary this per table.

CREATE TABLE employee
(
 id int IDENTITY(1,2),
 fname varchar (20),
 minit char(1),
 lname varchar(30)
)

Oracle

Oracle uses a SEQUENCE object and you call NEXTVAL() to get new values for primary keys. You can use a single sequence for multiple tables, or a specific sequence for a specific table, or any combination. You just name the sequence explicitly every time you INSERT.

CREATE SEQUENCE s START WITH 1 INCREMENT BY 10; 

INSERT INTO employee (id) VALUES (s.NEXTVAL);

PostgreSQL

PostgreSQL has sequence objects with the same options, and you can declare which sequence a table uses for automatic generation of values.

CREATE SEQUENCE s START WITH 1 INCREMENT BY 10; 

CREATE TABLE employee (
    id integer NOT NULL DEFAULT NEXTVAL('s')
);

You can also declare SERIAL as a shorthand, and this implicitly creates a sequence for the column. I haven't tested it, but I suppose you could ALTER SEQUENCE on the sequence it creates, to change its incrementation step.

IBM DB2

DB2 has sequence objects too. DB2 also can declare columns to auto-generate identity values.

CREATE TABLE EMPLOYEE (
  SERIALNUMBER  BIGINT NOT NULL 
        GENERATED ALWAYS AS IDENTITY
        (START WITH 1, INCREMENT BY 1),
  FIRSTNAME CHAR(64),
  LASTNAME  CHAR(64),
  SALARY        DECIMAL(10, 2),
  PRIMARY KEY (SERIALNUMBER)
);

Or:

CREATE SEQUENCE EMPSERIAL
 AS BIGINT
 START WITH 1
 INCREMENT BY 10;

SQLite

SQlite doesn't seem to have a means to change its auto-increment increment either globally or per-table; it increments by 1 until it reaches maxint, then it tries a few random values, then gives up.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
0

Are you asking if other database systems can change the step value of AUTO_INCREMENT? If so, yes, you can do this in SQL Server. I suspect most RDBS support this, but I know it's supported in SQL Server.

Melanie
  • 3,021
  • 6
  • 38
  • 56
0

SQL Server - IDENTITY

IDENTITY [ (seed , increment) ]

http://msdn.microsoft.com/en-us/library/ms186775.aspx

Oracle - SEQUENCE

CREATE SEQUENCE Test_Sequence
INCREMENT BY 1
START WITH 1;

INSERT INTO Test (ID, Val)
VALUES (Test_Sequence.NEXTVAL, 1);

http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_6015.htm

jdl
  • 1,104
  • 8
  • 12