SQL AUTO INCREMENT

Sometimes we want to have the database create a numerical primary key value as we continue to add rows of data. In MySQL, this is done by specifying AUTO_INCREMENT to the primary key field.

The syntax for AUTO_INCREMENT is as follows:

CREATE TABLE TABLE_NAME
(PRIMARY_KEY_COLUMN INT NOT NULL AUTO_INCREMENT
...
PRIMARY KEY (PRIMARY_KEY_COLUMN));

For example, let's say we want to create a table that consists of a primary key, last name, and first name. We use the following SQL:

CREATE TABLE USER_TABLE
(userid int NOT NULL AUTO_INCREMENT,
last_name varchar(50),
first_name varchar(50),
primary key (uid));

Upon creation, there is no data in this table.

We insert the first value:

INSERT INTO USER_TABLE VALUES ('Perry','Jonathan');

Now the table has the following values:

Table USER_TABLE

userid	last_name	first_name
1	Perry	Jonathan

Now we insert the second value:

INSERT INTO USER_TABLE values ('Davis','Nancy');

Now the table has the following values:

Table USER_TABLE

userid	last_name	first_name
1	Perry	Jonathan
2	Davis	Nancy

By default, AUTO_INCREMENT starts with 1 and increases by 1. To change the default starting value, you can use the ALTER TABLE command as follows:

ALTER TABLE TABLE_NAME AUTO_INCREMENT = [New Number]

where [New Number] is the starting value you want to use.

The AUTO INCREMENT interval value is controlled by the MySQL Server variable auto_increment_increment and applies globally. To change this to a number different from the default of 1, use the following command in MySQL:

mysql> SET @@auto_increment_increment=[interval number];

where [interval number] is the interval value you want to use. So, if we want to set the interval to be 5, we would issue the following command:

mysql> SET @@auto_increment_increment=5;