SQL AUTO INCREMENT Field

SQL

SQL AUTO INCREMENT Field : SQL AUTO INCREMENT Field allows a unique number to be generated automatically when a new record is inserted into a table.

AUTO INCREMENT Field

  • Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.
  • Often this is the primary key field that we would like to be created automatically every time a new record is inserted.

Syntax for MySQL

  • The following SQL statement defines the “ID” column to be an auto-increment primary key field in the “Persons” table:

CREATE TABLE Persons (
    ID int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

  • MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature.
  • By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.
  • To let the AUTO_INCREMENT sequence start with another value, use the following SQL statement:

ALTER TABLE Persons AUTO_INCREMENT=100;

  • To insert a new record into the “Persons” table, we will NOT have to specify a value for the “ID” column (a unique value will be added automatically):

INSERT INTO Persons (FirstName,LastName)
VALUES (‘Lars’,’Monsen’);

  • The SQL statement above would insert a new record into the “Persons” table. The “ID” column would be assigned a unique value. The “FirstName” column would be set to “Lars” and the “LastName” column would be set to “Monsen”.

Syntax for SQL Server

  • The following SQL statement defines the “ID” column to be an auto-increment primary key field in the “Persons” table:

CREATE TABLE Persons (
    ID int IDENTITY(1,1) PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

  • The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature.
  • In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record.

Tip:

To specify that the “ID” column should start at value 10 and increment by 5, change it to IDENTITY(10,5).

  • To insert a new record into the “Persons” table, we will NOT have to specify a value for the “ID” column (a unique value will be added automatically):

INSERT INTO Persons (FirstName,LastName)
VALUES (‘Lars’,’Monsen’);

  • The SQL statement above would insert a new record into the “Persons” table. The “ID” column would be assigned a unique value. The “FirstName” column would be set to “Lars” and the “LastName” column would be set to “Monsen”.

Syntax for Access

  • The following SQL statement defines the “ID” column to be an auto-increment primary key field in the “Persons” table:

CREATE TABLE Persons (
    ID Integer PRIMARY KEY AUTOINCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

  • The MS Access uses the AUTOINCREMENT keyword to perform an auto-increment feature.
  • By default, the starting value for AUTOINCREMENT is 1, and it will increment by 1 for each new record.

Tip:

To specify that the “ID” column should start at value 10 and increment by 5, change the autoincrement to AUTOINCREMENT(10,5).

  • To insert a new record into the “Persons” table, we will NOT have to specify a value for the “ID” column (a unique value will be added automatically):

INSERT INTO Persons (FirstName,LastName)
VALUES (‘Lars’,’Monsen’);

  • The SQL statement above would insert a new record into the “Persons” table. The “P_Id” column would be assigned a unique value. The “FirstName” column would be set to “Lars” and the “LastName” column would be set to “Monsen”.

Syntax for Oracle

  • In Oracle the code is a little bit more tricky.
  • You will have to create an auto-increment field with the sequence object (this object generates a number sequence).
  • Use the following CREATE SEQUENCE syntax:

CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;

  • The code above creates a sequence object called seq_person, that starts with 1 and will increment by 1.
  • It will also cache up to 10 values for performance.
  • The cache option specifies how many sequence values will be stored in memory for faster access.
  • To insert a new record into the “Persons” table, we will have to use the nextval function (this function retrieves the next value from seq_person sequence):

INSERT INTO Persons (ID,FirstName,LastName)
VALUES (seq_person.nextval,’Lars’,’Monsen’);

  • The SQL statement above would insert a new record into the “Persons” table.
  • The “ID” column would be assigned the next number from the seq_person sequence.
  • The “FirstName” column would be set to “Lars” and the “LastName” column would be set to “Monsen”.