MySQL AUTO INCREMENT Field

An AUTO INCREMENT field in MySQL allows the automatic generation of a unique value for a column when a new record is inserted into a table. This is particularly useful for primary key fields, where each record needs a unique identifier.

How AUTO INCREMENT Works

AUTO_INCREMENT is typically used for primary key columns.
When a new record is inserted, MySQL automatically assigns a unique, incrementing value to the column that has the AUTO_INCREMENT attribute.
The value of AUTO_INCREMENT starts at 1 by default and increments by 1 for each new record. However, you can customize the starting value and increment value.

Syntax to Define AUTO_INCREMENT

To define an AUTO_INCREMENT column in a table, use the AUTO_INCREMENT keyword while creating or modifying the table.

CREATE TABLE table_name (
    column_name datatype NOT NULL AUTO_INCREMENT,
    ...
    PRIMARY KEY (column_name)
);

Example of AUTO_INCREMENT in a Table

The following SQL creates a table where the Personid column is defined as the auto-incrementing primary key:

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

In this example:

The Personid field will automatically be assigned a unique number starting from 1 and incremented by 1 for each new record.
The LastName, FirstName, and Age fields will store data as usual.

Inserting Data into AUTO_INCREMENT Column

When inserting data into a table with an AUTO_INCREMENT column, you do not need to specify a value for that column. MySQL will automatically assign the next available number.

INSERT INTO Persons (FirstName, LastName, Age)
VALUES ('Lars', 'Monsen', 35);

In this example:

Personid is not specified in the INSERT statement.
MySQL will automatically assign a unique value to Personid.

Customizing the Starting Value of AUTO_INCREMENT

You can change the starting value of the AUTO_INCREMENT sequence using the ALTER TABLE statement. For example, to start the sequence at 100, you can use:

ALTER TABLE Persons AUTO_INCREMENT=100;

Now, the next inserted record will have Personid set to 100, and the following records will be 101, 102, and so on.

Example of Inserting Data After Customizing the AUTO_INCREMENT

If the AUTO_INCREMENT value was set to 100, inserting a record will automatically assign the next value starting from 100:

INSERT INTO Persons (FirstName, LastName, Age)
VALUES ('John', 'Doe', 30);

The Personid for this record will be automatically set to 100.

Important Notes:

Uniqueness: The AUTO_INCREMENT field ensures that each record has a unique value.
Primary Key: The AUTO_INCREMENT field is usually set as the primary key because primary keys require uniqueness.
Resetting AUTO_INCREMENT: The sequence can be reset if necessary (e.g., if records are deleted, the sequence can be reset to continue from the last used value).
```
ALTER TABLE table_name AUTO_INCREMENT = 1;
```
Gap in Sequence: If records are deleted, the AUTO_INCREMENT value does not get reset automatically, leading to gaps in the sequence.

Summary:

AUTO_INCREMENT generates unique values automatically for a column, typically the primary key.
By default, it starts at 1 and increments by 1 for each new record, but the starting value and increment can be customized.
It simplifies inserting new records by eliminating the need to manually specify values for the auto-increment column.