🚨 Get Your Free NFT Certificate Mint by Completing the Web3 Exam! START NOW

Code has been added to clipboard!

SQL AUTO INCREMENT for Different Database Systems

Reading time 3 min
Published Aug 9, 2017
Updated Oct 3, 2019

SQL AUTO INCREMENT Field: Main Tips

  • This field permits you to generate a unique digit when a data record is written to a table.
  • This is useful when you want the primary key field value to be generated each time we insert a new record.

MySQL: Syntax

In the code examples below we specify the column "ID" to be auto-incremented in the table "Person":

Example
CREATE TABLE Person (
    ID int NOT NULL AUTO_INCREMENT,
    LName varchar(255) NOT NULL,
    FName varchar(255),
    AgeID int,
    PRIMARY KEY (ID)
);

Auto-incrementation is started by a keyword AUTO_INCREMENT in MySQL.

The keywords value starts with 1 and then gets incremented by 1 more for every record.

In the code example below we start the sequence with a different value:

Example
ALTER TABLE Person AUTO_INCREMENT=100;

When writing another data record to the table "Person", we do not need to define a value for the column "ID" because it is going to be added automatically:

Example
INSERT INTO Person (FName,LName)
VALUES ('Bob','Bonbon');
DataCamp
Pros
  • Easy to use with a learn-by-doing approach
  • Offers quality content
  • Gamified in-browser coding experience
  • The price matches the quality
  • Suitable for learners ranging from beginner to advanced
Main Features
  • Free certificates of completion
  • Focused on data science skills
  • Flexible learning timetable
Udacity
Pros
  • Simplistic design (no unnecessary information)
  • High-quality courses (even the free ones)
  • Variety of features
Main Features
  • Nanodegree programs
  • Suitable for enterprises
  • Paid Certificates of completion
Udemy
Pros
  • Easy to navigate
  • No technical issues
  • Seems to care about its users
Main Features
  • Huge variety of courses
  • 30-day refund policy
  • Free certificates of completion

Syntax for SQL Server

In the code examples below we specify the column "ID" to be auto-incremented in the table "Person":

Example
CREATE TABLE Person (
    ID int IDENTITY(1,1) PRIMARY KEY,
    LName varchar(255) NOT NULL,
    FName varchar(255),
    AgeID int
);

Auto-incrementation is started by a keyword IDENTITY in MS SQL Server.

The keywords value starts with 1 and then gets incremented by 1 more for every record.

When defining the column to start with 20 and get incremented by 10 write it like this IDENTITY(20,10).

When writing another data record to the table "Person", we do not need to define a value for the column "ID" because it is going to be added automatically:

Example
INSERT INTO Person (FName,LName)
VALUES ('Bob','Bonbon');

Syntax for Access

In the code examples below we specify the column "ID" to be auto-incremented in the table "Person":

Example
CREATE TABLE Person (
    ID Integer PRIMARY KEY AUTOINCREMENT,
    LName varchar(255) NOT NULL,
    FName varchar(255),
    AgeID int
);

Auto-incrementation is started by a keyword IDENTITY in MS SQL Server.

The keywords value starts with 1 and then gets incremented by 1 more for every record.

When defining the column to start with 20 and get incremented by 10 write it like this IDENTITY(20,10).

When writing another data record to the table "Person", we do not need to define a value for the column "ID" because it is going to be added automatically:

Example
INSERT INTO Person (FName,LName)
VALUES ('Bob','Bonbon');

Syntax for Oracle

In oracle it's a bit different. An auto-incrementation field is needed to be created containing a sequence object which will produce a digit sequence.

In the code example below we use CREATE SEQUENCE:

Example
CREATE SEQUENCE seq_persons
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;

The example above produces an seq_persons sequence object which beings with 1 and an incrementation of 1. Then it caches 10 values to have better performance.

When writing a new data record to the table "Person" the nextval function is needed to be used which brings back the following value in seq_persons sequence:

Example
INSERT INTO Person (ID,FName,LName)
VALUES (seq_persons.nextval,'Bob','Bonbon');