Auto Increment In MySQL

In MySQL, The AUTO_INCREMENT Keyword automatically generates a unique integer value when a new row or record is inserted into a table.

This is commonly used for creating primary key columns that need to have unique values for each record.

Overview of Auto Increment In MySQL Table:

Primary Key Requirement: AUTO_INCREMENT keywords in the column are typically used with primary keys to ensure the uniqueness of each record and row in the Table.
Only One per Table: It allow only one "AUTO_INCREMENT" column per table.
Data Type: AUTO_INCREMENT Keywords can be used with an integer data type (INT, BIGINT).
Manual Insertions: While inserting values manually using an "AUTO_INCREMENT" column will set the sequence to the next value above the inserted value.

Auto Increment Syntax In MySQL Table:

CREATE TABLE yourTableName (
    columnName1 INT AUTO_INCREMENT PRIMARY KEY,
    columnName2 VARCHAR(50)
);

"columnName1" is set as an INT column with "AUTO_INCREMENT" and designated as the table's primary key.
When we insert a new record into this table and do not provide a value for "columnName1", SQL will automatically generate a unique integer value for it.

Create Table With AUTO_INCREMENT Keyword In MySQL Table:

In the below command using the `AUTO_INCREMENT` Keyword we have created the `products` Table.

Example:

CREATE TABLE products (
    product_id INT AUTO_INCREMENT,
    product_name VARCHAR(50) NOT NULL,
    product_description VARCHAR(200) NOT NULL,
    product_price DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (product_id)
);

Insert record into MySQL Table:

Syntax:

INSERT INTO table_name (columnName1, columnName2, ...) VALUES ("columnName1_value", 'columnName2_value', ...);

Omit AUTO_INCREMENT Keyword From Insert Statement In MySQL Table:

In the below command of INSERT INTO Command, we have the omit the columnName1 value from Query, SQL will automatically generate the next available unique value and assign it to `product_id`.

Example:

INSERT INTO products (product_name, product_description, product_price) VALUES ('iPad', "New Generation", 400);

Include AUTO_INCREMENT Keyword Explicity In MySQL Table:

we can also explicitly specify a value for the auto-increment column (perhaps for a specific reason), we can include that value in the INSERT statement.

Example:

ALTER TABLE products AUTO_INCREMENT = 100;

<-- Create Index