SQL AUTO INCREMENT Field

The SQL AUTO INCREMENT field is used for auto generating number with user defined values for particular field whenever a new row is being inserted into a table.

If we need to create a column with unique value, we define that column as AUTO INCREMENT field.

You can also search these topics using sql auto increment constraint, auto increment in sql, sql auto generate id, sql server autonumber, alter table auto_increment, oracle auto increment primary key, primary key autoincrement, sql insert auto increment, sql server autoincrement, sql server increment counter, sql server identity increment, sql increment value by 1 in select.

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:

BookId	BookName	AuthorName	BookPrice
50	Sql Programming	Suresh Babu	175.5
51	Sql Hacking	Suresh Babu	225

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:

BookId	BookName	AuthorName	BookPrice
200	Sql Complete Reference	Siva Kumar	150
210	Sql Administrator	Varshini Kutty	250
220	Pl Sql Quick Book	Bala Murugan	200

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:

BookId	BookName	AuthorName	BookPrice
500	Sql Database Reference	Haris Karthik	350
502	Sql Quick Command	Azagu Varshith	300

You can also search these topics using sql increment value, sql increment counter, auto increment keys, auto generated id, sql insert with auto increment, sql table auto increment, create table auto increment sql, ms access auto increment, auto increment sql server 2008, sql server auto increment id, auto increment keys in database, auto increment column, sql change column to auto increment, auto generate primary key sql, sql insert with auto increment primary key, ms sql primary key autoincrement.