Auto Increment in sql

What is autoincrement?

Autoincrement is a function that works with numeric data types. It automatically generates consecutive numeric values ​​each time a record is inserted into the table for a field defined as autoincrement.

When to use autoincrement?

In the Database Normalization tutorial, we looked at how you can store data with minimal redundancy by storing data in many small tables that are related to each other using primary and foreign keys.

The primary key must be unique because it uniquely identifies a row in the database. But how can we guarantee that the primary key is always unique? One possible solution would be to use a formula to generate a primary key that checks if the key exists in the table before adding data. This may work well, but as you can see, the approach is complex and not error-proof. To avoid this complexity and ensure that the primary key is always unique, we can use MySQL's auto-increment feature to generate primary keys. Auto increment is used with the INT data type. The INT data type supports both signed and unsigned values.Unsigned data types can only contain positive numbers. It is recommended to define an unsigned constraint on an auto-increment primary key.

Autoincrement Syntax

Let's now look at the script used to create the movie category table.

CREATE TABLE `categories` (
  `category_id` int(11) AUTO_INCREMENT,
  `category_name` varchar(150) DEFAULT NULL,
  `remarks` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`category_id`)
);

Notice the "AUTO_INCREMENT" in the category_id field. This causes the category ID to be automatically generated each time a new row is added to the table. It is not provided when inserting data into a table, MySQL generates it.

Let's take a look at the current contents of the category table.

SELECT * FROM `categories`;

Running the above script in MySQL Workbench against myflixdb gives us the following results.

Let's now insert a new category into the categories table.

INSERT INTO `categories` (`category_name`) VALUES ('Cartoons');

Running the above script for myflixdb in MySQL Workbench gives us the following results shown below.

SELECT LAST_INSERT_ID();