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.


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

Search Keys

  • sql auto increment constraint
  • autoincrement
  • auto increment in sql
  • sql auto_increment
  • sql auto generate id
  • sql server autonumber
  • alter table auto_increment
  • oracle auto increment primary key
  • mssql autoincrement
  • 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
  • sql increment

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

Search Keys

  • 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