Auto Increment SQL MySQL Oracle Access

An Explanation with Examples

Auto Increment allows for the automatic generation of a unique number when a new record is added to a table. This feature is commonly used to represent a unique and incrementing field, often found in the primary key column. The AUTO_INCREMENT command is used to utilize this feature.

Note: The starting value for AUTO_INCREMENT is 1, and it increments by 1 for each new record.

source image url

MySQL Usage:

The following SQL command defines the “CustomerID” column in the “Customers” table as an auto-incrementing primary key field:

CREATE TABLE Customers (
    CustomerID INT NOT NULL AUTO_INCREMENT,
    Name VARCHAR(255) NOT NULL,
    City VARCHAR(255),
);

When a new record is added to the table as shown below, the CustomerID will increment automatically:

INSERT INTO Customers (Name, City)
VALUES ('John Doe', 'New York');

To allow the AUTO_INCREMENT sequence to start with a different value, use the following SQL command:

ALTER TABLE Customers AUTO_INCREMENT = 10;

SQL Server Usage:

In MS SQL Server, the IDENTITY command is used to achieve the auto-increment feature. The command…