# How to Create a MySQL Sequence?

MySQLMySQLi Database

## What is Sequence in MySQL?

In MySQL, a sequence refers to a list of integers that is generated in the ascending order beginning from 1 or 0 if specified. Many applications require sequences that will be used to generate unique numbers especially for identification.

## Example

Examples include customer ID in CRM, employee numbers in HR, and equipment numbers in the services management system.

To create a sequence in MySQL automatically, the AUTO_INCREMENT attribute for a column needs to be set. This would typically be a primary key column.

## Rules

The following rules need to be followed while using the AUTO_INCREMENT attribute −

Every table has only one AUTO_INCREMENT column whose data type would be an integer typically.

The AUTO_INCREMENT column needs to be indexed. This means it can either be a PRIMARY KEY or a UNIQUE index.

The AUTO_INCREMENT column must have a NOT NULL constraint on it.

When the AUTO_INCREMENT attribute is set to a column, MySQL automatically adds the NOT NULL constraint to the column on its own.

## Create a MySQL Sequence

Let us see an example of creating a MySQL sequence −

CREATE TABLE tableName (
emp_no INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50)
)

Let us understand how the AUTO_INCREMENT column works −

• The starting value of an AUTO_INCREMENT column is usually 1. It is increased by 1 when a NULL value is inserted into the column or when a value is omitted in the INSERT statement.

• To obtain the last generated sequence number, the LAST_INSERT_ID() function can be used.

• The last generated sequence is unique across sessions.

• If another connection generates a sequence number, it can be obtained by using the LAST_INSERT_ID() function.

• If a new row is inserted into a table and a value is specified for the sequence column, MySQL ensures to insert the sequence number if the sequence number doesn’t exist in the column.

Published on 09-Mar-2021 13:28:47