How To Use MySQL AUTO INCREMENT – With Examples

In this tutorial, we will learn about the MySQL AUTO INCREMENT Option and how to use as a Primary Key with the help of simple examples:

This option is used to generate a unique identifier for a row in the MySQL table. Once MySQL AUTOINCREMENT is enabled for a column then those values need not be specified by the user while inserting records in a table.

It’s important to note that AUTOINCREMENT works on numeric data types like INT.

=> Take A Look At The MYSQL Beginners Guide Here

Using AUTO INCREMENT as PRIMARY KEY

Since MySQL AUTO_INCREMENT generates a unique ID for each row, the columns having AUTO INCREMENT applied can also be used as PRIMARY KEYS. The numbers/identities are generated sequentially for the AUTO INCREMENTED columns. Also, it might not always be possible to define PRIMARY KEYS on the table depending on the data being stored.

In such cases having AUTO_INCREMENT columns can be a choice for unique keys.

Syntax

AUTO INCREMENT is specified along with the column name during table creation.

columnName columnType AUTO INCREMENT

Example: Let’s create a table employee with emp_id as an AUTO INCREMENT column and emp_name of type VARCHAR.

CREATE TABLE employees (emp_id SMALLINT(5) NOT NULL AUTO_INCREMENT, 
emp_name CHAR(128) DEFAULT NULL, PRIMARY KEY (emp_id));

Here, we have declared column emp_id with AUTO INCREMENT option and also defined the same column as PRIMARY KEY.

While inserting values in the column marked as AUTO INCREMENTED, the values are assigned starting with the initial value as 1.

Let’s see inserting some records.

INSERT INTO employees(emp_name) VALUES("David Johnson"),("Anita Sharma"),("Steve Smith");

SELECT * FROM employees;

Important Points

#1) Deleting a row from a table that has an auto_increment column as the primary key.

• In the above table, for example, if we delete rows with emp_id 2 and insert a new record – the emp_id generated would still be 4 i.e the index would continue from where the last record was added.

DELETE FROM employees WHERE emp_id = 2

SELECT * FROM employees;

INSERT INTO employees(emp_name) VALUES("Eric Lee");

SELECT * FROM employees;

As you can see above, the new record inserted has emp_id assigned as 4 even though we removed a row in between.

• If the last record from the table is deleted, even then the new emp_id assigned would be 4 as the auto_increment index points to 5
  Let’s now delete the record that we added in the point above – So let’s delete the record with emp_id = 4 and insert a new record.

DELETE FROM employees WHERE emp_id >= 4

INSERT INTO employees(emp_name) VALUES("Shane Watson");

SELECT * FROM employees;

Note: It’s important to note here, that AUTO INCREMENT option can be applied to only numeric fields.

For example, let’s try assigning the AUTO_INCREMENT option to a VARCHAR field – in this case, emp_name

CREATE TABLE employees (emp_id SMALLINT(5) NOT NULL, emp_name CHAR(128) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (emp_id));

If you execute the above command, you will get an error stating an Incorrect column specifier.

Error Code: 1063. Incorrect column specifier for column 'emp_name'

Assigning a Field as AUTO INCREMENT After Table Creation

A lot of times, we come to know at a later point in time, if we want to consider making a column value AUTO INCREMENT after its initial schema has already been created.

This can be done using the MySQL ALTER TABLE command.

We can specify the AUTO INCREMENT option for an existing column having NUMERIC DATA type.

CREATE TABLE employees (emp_id SMALLINT(5) NOT NULL, emp_name CHAR(128) DEFAULT NULL,
PRIMARY KEY (emp_id));

INSERT INTO employees VALUES(1000, "David Johnson"),(1001, "Anita Sharma"),(1002, "Steve Smith");

SELECT * FROM employees;

As you can see below, we are using the ALTER TABLE command and modifying the emp_id column to have an AUTO_INCREMENT option.

ALTER TABLE employees MODIFY COLUMN emp_id SMALLINT AUTO_INCREMENT

Let’s now try inserting a new record in the table (without specifying) any emp_id

INSERT INTO employees(emp_name) VALUES("David Jacob");

SELECT * FROM employees;

From the above output, you can see that a new record was inserted with emp_id 1003 which is an auto-incremented value of the last inserted existing row in the table (i.e row having emp_id 1002).

MySQL RESET AUTO INCREMENT

At times, we want to reset the AUTO_INCREMENT index to a user-defined value.

The default values start from 1,2,3 etc. Suppose we want to change the default setting and start the column values from 2001, 2002 etc.. (E.g. a lot of hotel rooms have numbers like – 1001, 1002, etc)

Let’s see how we can rest the AUTO_INCREMENT counter.

ALTER TABLE employees AUTO_INCREMENT=5000

Let’s now try adding a new record in the EMPLOYEES table.

INSERT INTO employees(emp_name) VALUES("Shane Watson");

SELECT * FROM employees;

As we can see above, the AUTO INCREMENT has now reset to 5000 and would start from the set index while new records are added.

Let’s also understand, what would happen if someone assigns a value to a column that is declared to be AUTO INCREMENT.

We can see with an example against the table we created earlier with AUTO_INCREMENT set to 5000.

We will insert a record with a value less than the current AUTO_INCREMENT index.

INSERT INTO employees(emp_id, emp_name) values(100, "Henry Smith");
INSERT INTO employees(emp_name) values("Shikha Sharma");

SELECT * from employees;

In the above example, you can see, even though we added a row with an explicit value of 100, the AUTO_INCREMENT index remains the same, and continues to the next number when new records are inserted.

Let’s look at another example, where we add an explicit record with a number greater than the current AUTO_INCREMENT index, here in this case it’s set to 5001.

INSERT INTO employees(emp_id, emp_name) values(6000, "Michael Lapel");
INSERT INTO employees(emp_name) values("Darren Hilton");

SELECT * from employees;

In the above output, you can see that inserting a record with a value greater than the AUTO_INCREMENT index would result in the value resetting and new records would follow the newly set index – in this case, record with emp_id 6001

Frequently Asked Questions

Q #1) How do you set a field as AUTO INCREMENT in MySQL?

Answer: A column can be defined to have AUTO INCREMENT property during table creation or later using the ALTER TABLE method. The default value of AUTO INCREMENT is 1 and it moves by 1 after every successful insertion.

Please note that any deletions that would happen in the table would not impact the AUTO INCREMENT index and the new records would still keep getting incremented index values.

The columns having an AUTO INCREMENT option are usually used to indicate PRIMARY KEYS where uniqueness is desired and any intrinsic values or fields are unable to uniquely identify a record.

Q #2) Can we auto increment varchar?

Answer: Only numeric fields like INT, SMALLINT, MEDIUMINT, BIGINT can be assigned the AUTO_INCREMENT option.

Assigning AUTO_INCREMENT to an incompatible data type would return in an error being thrown during query execution.

Q #3) How do I reset AUTO_INCREMENT?

Answer: MySQL AUTO INCREMENT can be reset using the ALTER TABLE command. This is useful when you want to change the current index of the AUTO_INCREMENT column.

Sample query that could be used to reset is given below:

ALTER TABLE employees AUTO_INCREMENT=5000

The above query would reset the AUTO_INCREMENT counter to 5000.

Q #4) Can we have multiple columns with AUTO_INCREMENT enabled?

Answer: That is not possible. A table can have at most one column with an AUTO INCREMENT option applied.

If you try defining more than one column as AUTO INCREMENT, then you could get an error like the below:

Q #5) What happens when a row with AUTO INCREMENTED value is deleted from the table?

Answer: The current index of the AUTO INCREMENT column is independent of any row deletions that happen from the table. The newly inserted records would still be against the next value of the AUTO INCREMENTED index.

Conclusion

In this tutorial, we learned about the MySQL AUTO INCREMENT option that could be used to create column values that could uniquely represent a row.

The AUTO INCREMENT values can be reset using the ALTER TABLE command anytime later after the declaration.

AUTO INCREMENT is used in many real-world applications as PRIMARY KEY as it guarantees uniqueness for new records being inserted into the tables.

=> Check ALL MYSQL Tutorials Here