×
☰ See All Chapters

Building Sequences in MySQL

In MySQL, a sequence is a list of integers generated in the ascending order i.e., 1, 2, 3… Many applications need sequences to generate unique numbers mainly for identification e.g., customer ID in CRM, employee number in HR, equipment number in services management system, etc. To create a sequence in MySQL automatically, you set the AUTO_INCREMENT attribute to a column, which typically is primary key column. The following are rules that you must follow when you use AUTO_INCREMENT attribute:

  • Each table has only one AUTO_INCREMENT column whose data type is typically integer or float which is very rare. 

  • The AUTO_INCREMENT column must be indexed, which means it can be either PRIMARY KEY or UNIQUE index. 

  • The AUTO_INCREMENT column must have NOT NULL constraint. When you set AUTO_INCREMENT attribute to a column, MySQL will make it NOT NULL for you in case you don’t define it explicitly. 

How MySQL sequence works

The AUTO_INCREMENT column has the following attributes:

  • The starting value of an AUTO_INCREMENT column is 1 and it is increased by 1 when you insert NULL value into the column or when you omit its value in the INSERT statement, or we can insert exact incremented value. 

  • If you delete any row, then sequence number of deleted row (either last inserted row/any row in the middle of the table) will not be used once again. 

CREATE TABLE employees(

emp_no INT(4) AUTO_INCREMENT PRIMARY KEY,

first_name VARCHAR(50),

last_name  VARCHAR(50)

)

INSERT INTO employees(first_name,last_name)

VALUES ('Manu','M') ,('Jack','Lee'),('Adi','Temp');

mysql-sequences-0
 

After deleting all rows and reinserting same data

mysql-sequences-1
 

Adding Auto-Incrementing Columns to a Table

Sometimes it is necessary to create a column that auto-increments itself to give a unique sequence number for a particular row. You could do this for many reasons, such as not having a natural key for the data, or wanting to use a unique sequence number to sort the data.

In MySQL, the implementation provides the SERIAL method to produce a truly unique value for the table.

Example:

CREATE TABLE TEST_INCREMENT(

ID SERIAL,

TEST_NAME VARCHAR(20));

INSERT INTO TEST_INCREMENT (TEST_NAME)

VALUES ('Manu'),('Advith'),('Likitha'),('Manjunatha');

mysql-sequences-2
 
All Chapters
Author