SQL AUTO INCREMENT Field

Syntax for MySQL

The following SQL statement defines the "BookID" field to be an auto increment in the "Books" table:

CREATE TABLE Books
(
BookID INT NOT NULL AUTO_INCREMENT,
BookName VARCHAR(255),
AuthorName VARCHAR(255),
BookPrice DECIMAL (7, 2)
);

MySQL database uses the AUTO_INCREMENT keyword to perform an auto-increment feature.

AUTO_INCREMENT keyword will start value from 1 and it will increment by 1 for each new inserted record. If you want to change the value sequence of AUTO_INCREMENT with user defined value, use the following SQL statement:

ALTER TABLE Books AUTO_INCREMENT = 50;

We will NOT have to specify a value for the "BookID" field while adding a new record into the "Books" table. Bcoz a unique value will be added automatically on the "BookID" column:

INSERT INTO Books (BookName, AuthorName, BookPrice)
VALUES ('Sql Programming','Suresh Babu', 175.5);
INSERT INTO Books (BookName, AuthorName, BookPrice)
VALUES ('Sql Hacking','Suresh Babu', 225);

The SQL statement above would insert two new records into the "Books" table. The "BookID" field would be assigned automatically with a unique value.

After executing above query, the "Books" table look like this:

Syntax for MICROSOFT SQL SERVER

The following SQL statement defines the "BookID" field to be an auto increment in the "Books" table:

CREATE TABLE Books
(
BookID INT IDENTITY(1,1) PRIMARY KEY,
BookName VARCHAR(255),
AuthorName VARCHAR(255),
BookPrice DECIMAL (7, 2)
);

SQL SERVER database uses the IDENTITY keyword to perform an auto-increment feature.

In the above example IDENTITY keyword will start value from 1 and it will increment by 1 for each new inserted record.

If you want to specify that the "BookID" field should start at value 200 and increment by 10, change it to IDENTITY(200,10).

We will NOT have to specify a value for the "BookID" field while adding a new record into the "Books" table. Bcoz a unique value will be added automatically on the "BookID" column:

INSERT INTO Books (BookName, AuthorName, BookPrice)
VALUES ('Sql Complete Reference','Siva Kumar', 150);
INSERT INTO Books (BookName, AuthorName, BookPrice)
VALUES ('Sql Administrator','Varshini Kutty', 250);
INSERT INTO Books (BookName, AuthorName, BookPrice)
VALUES ('Pl Sql Quick Book','Bala Murugan', 20);

The SQL statement above would insert two new records into the "Books" table. The "BookID" field would be assigned automatically with a unique value.

Let's assume the IDENTITY will start "200" and increment by "10", then the "Books" table look like this:

Syntax for MICROSOFT ACCESS

The following SQL statement defines the "BookID" field to be an auto increment in the "Books" table:

CREATE TABLE Books
(
BookID Integer PRIMARY KEY AUTOINCREMENT,
BookName VARCHAR(255),
AuthorName VARCHAR(255),
BookPrice DECIMAL (7, 2)
);

Microsoft Access database uses the AUTOINCREMENT keyword to perform an auto-increment feature.

In the above example AUTOINCREMENT keyword will start value from 1 and it will increment by 1 for each new inserted record.

If you want to specify that the "BookID" field should start at value 500 and increment by 2, change the AUTOINCREMENT to AUTOINCREMENT(500, 2)..

We will NOT have to specify a value for the "BookID" field while adding a new record into the "Books" table. Bcoz a unique value will be added automatically on the "BookID" column:

INSERT INTO Books (BookName, AuthorName, BookPrice)
VALUES ('Sql Database Reference','Haris Karthik', 350);
INSERT INTO Books (BookName, AuthorName, BookPrice)
VALUES ('Sql Quick Command','Azagu Varshith', 300);

The SQL statement above would insert two new records into the "Books" table. The "BookID" field would be assigned automatically with a unique value.

Let's assume the AUTOINCREMENT will start "500" and increment by "2", then the "Books" table look like this: