How to Create a Sequence in MySQL Using AUTO_INCREMENT

A Sequence is a series of automatically generated, unique numbers. The numbers generated are in the Ascending number. Sequences are generally used as primary keys where the primary key is of Integer datatype.

We generate Sequences using AUTO_INCREMENT constraint. The column that has AUTO_INCREMENT constraint also has the NOT NULL constraint. Generally, it is used for Primary keys.

To apply the AUTO_INCREMENT, we generally declare it during Create as follows:

CREATE TABLE EMPLOYEES(
    EMP_ID INT AUTO_INCREMENT,
    FIRST_NAME VARCHAR(20) NOT NULL,
    LAST_NAME VARCHAR(20),
    BIRTH_DATE DATE DEFAULT '1900-00-00',
    HIRE_DATE DATE DEFAULT (CURRENT_DATE()),
    PRIMARY KEY(EMP_ID)
);

Here the EMP_ID is AUTO_INCREMENT.

How to insert a record with auto-Increment in MySQL

To insert records into a Column with Auto_increment, we can use the following two methods

Using Default keyword:

If you use the column name in the insert script, we need to specify the default keyword. In this case, MySQL inserts the next available value.

For example, currently in the employee's table, the latest emp_id = 41. Now if I try to insert a record using the default keyword for emp_id then the next record will be inserted with the value 42.

insert into employees(emp_id,first_name,last_name,birth_date,hire_date)
values(default,'Arwen','Mithrandir',default,default);

Insert without the column:

IF we do an insert using specific columns and do not mentioned the Auto-incremented column at all, the record is still inserted with the next available value.

insert into employees(first_name,last_name,birth_date,hire_date)
values('Gil','Galad',default,default);

This still inserts the record with emp_id = 43.

Delete the auto-incremented value in MySQL

The Auto-incremented value is not adjusted even if we delete a few records.

Consider the following sequence.

delete from employees where emp_id = 43;

select max(emp_id) from employees;

insert into employees(first_name,last_name,birth_date,hire_date)
values('Gil','Galad',default,default);

select max(emp_id) from employees;

Here even though we have deleted record with emp_id = 43, the auto_incrment value does not adjust. It stays at 43 and when the next record is added, inserts the value 44.

Insert with User-defined Value in MySQL

We can of course insert our own values in the Auto-increment column. However, for the records inserted after that point, the new value is considered. For example: If we set the auto-increment value to 100 and then insert another record with default value, we will get its emp_id set to 101.

insert into employees(emp_id,first_name,last_name,birth_date,hire_date)
values(100,'Mumfasa','King',default,default),
(default,'Nala','Nala',default,default);

Altering the Auto-increment value in MySQL

To set the auto-increment to some value, we can do:

alter table employees auto_increment = 50;