-->

MySQL Sequence

Summary: in this chapter, we will show you how to use MySQL sequence to automatically generate unique numbers for ID columns of tables.

Creating MySQL sequence

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., employee id 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 a primary key column. You must follow rules below when you use AUTO_INCREMENT attribute:
  • Each table has only one AUTO_INCREMENT column whose data type is typically the integer.
  • The AUTO_INCREMENT column must be indexed, which means it can be either PRIMARY KEY or UNIQUE index.
  • The AUTO_INCREMENT column must have a NOT NULL constraint. When you set the AUTO_INCREMENT attribute to a column, MySQL will make it NOT NULL  for you in case you don’t define it explicitly.

MySQL sequence example

The following example creates employees table whose emp_no column is the AUTO_INCREMENT column:
1
2
3
4
5
CREATE TABLE employees(
  emp_no INT(4) AUTO_INCREMENT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50)
)ENGINE = INNODB;

Set AUTO_INCREMENT starting value

Now that we’ve created a table using the AUTO_INCREMENT attribute, how can we change the starting value for the AUTO_INCREMENT field if we don’t want to start at 1?
We can use the ALTER TABLE statement to change or set the next value assigned by the AUTO_INCREMENT.

Syntax

In MySQL, the syntax to change the starting value for an AUTO_INCREMENT column using the ALTER TABLE statement is:
1
ALTER TABLE table_name AUTO_INCREMENT = start_value;
  • table_name:  The name of the table whose AUTO_INCREMENT value you wish to change. Since a table in MySQL can only contain one AUTO_INCREMENT column, we are only required to specify the table name that contains the sequence. We do not need to specify the name of the column that contains the AUTO_INCREMENT value.
start_value:  The next value in the sequence to assign in the AUTO_INCREMENT column.

Example

Let’s look at an example of how to change the starting value for the AUTO_INCREMENT column in a table in MySQL.
For example:
1
ALTER TABLE employees AUTO_INCREMENT = 50;
This MySQL AUTO_INCREMENT example would change the next value in the AUTO_INCREMENT field (i.e. next value in the sequence) to 50 for the emp-no field in the employees table.
In this chapter, we have learned how to use MySQL sequence to generate unique numbers for a primary key column by assigning the column AUTO_INCREMENT attribute.
MySQL Foreign Key MySQL ALTER TABLE

at
Labels: