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');
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
Coursera
Pros
  • Professional service
  • Flexible timetables
  • A variety of features to choose from
Main Features
  • Professional certificates of completion
  • University-level courses
  • Multiple Online degree programs
Datacamp
Pros
  • Great user experience
  • Offers quality content
  • Very transparent with their pricing
Main Features
  • Free certificates of completion
  • Focused on data science skills
  • Flexible learning timetable

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');

Latest Udacity Coupon Found:

Verified STAFF PICK

75% OFF COURSES

Udacity Black Friday Offer

The best time to save on Udacity courses is now - follow this coupon to access a 75% Udacity Black Friday discount & enjoy learning at a very low cost!

Expiration date: 19/01/2021
3176 People Used
Only 97 Left
Rating
5.0