Sequences

A sequence is a database object that is used to generate unique numeric values automatically. Sequences are ideally suited for generating unique key values, e.g, primary key column.

The values can be generated at defined intervals, usually in ascending order.

Creating a sequence with a real-life example

You are working on an employee management application. You've created a table to store employees' data.

CREATE TABLE employees(
  employee_id INT NOT NULL,
  first_name VARCHAR(30),
  last_name VARCHAR(30)
  PRIMARY KEY(employee_id)
);

For the employee_id column, each employee should have a unique ID, without skipping any value. Now, let's say you decide to do this in your application code. How will you go about it? Start by declaring a variable, initializing it, and then inserting it into the 'employees' table. Then you increment the variable on every insert into the table.

However, there are 2 problems with this approach:

1. Managing concurrency when 2 sessions are inserted simultaneously.

2. What if the application is restarted?

A better option would be to set the AUTO_INCREMENT attribute for the employee_id column, allowing MySQL to automatically generate unique numbers for the column when inserting data.

CREATE TABLE employees(
  employee_id INT NOT NULL AUTO_INCREMENT,
  first_name VARCHAR(30),
  last_name VARCHAR(30)
  PRIMARY KEY(employee_id)
);

INSERT INTO employees (first_name,last_name) VALUES ('Haruna','Smith');

INSERT INTO employees (first_name,last_name) VALUES ('Lewis','Adams');

When inserting data into the table, you only need to specify values for the other columns because the database will generate the next employee_id automatically. This is what a sequence is. Using sequences has saved many lines of code, plus it is more efficient.

Restrictions for AUTO_INCREMENT

There are a few things to keep in mind about the AUTO_INCREMENT attribute in MySQL.

1. A table can have only one AUTO_INCREMENT column.

2. The column should have an integer data type.

3. MySQL would automatically add a NOT NULL constraint to the column if it is not specified.

The default starting value for AUTO_INCREMENT is 1. To change the initial value, we must alter the table.

ALTER TABLE employees AUTO_INCREMENT = 50;

In the example above, we've created an AUTO_INCREMENT column that will start from 50.

Advantages of using sequences

Sequences have the following advantages.

1. The database server keeps track of the numbers generated, hence, uniqueness is maintained even if the database or the application is restarted.

2. Sequences avoid concurrency and performance problems by ensuring that no two sessions get the same number from the sequence.

3. No special table needs to be created.

Conclusion

Sequences are an efficient and hassle-free way of generating unique integer values for a column, especially for unique or primary key columns. They are used in many applications mainly for ID columns, for example, employee ID in an HR application, order ID in an e-commerce application, customer ID in CRM (customer relationship management software), transaction ID in a banking application, and so on. Remember, that sequences are generated in MySQL by assigning the AUTO_INCREMENT attribute to the column.