MySQL : AUTO INCREMENT Field

In MySQL, the AUTO_INCREMENT attribute is used to automatically generate a unique integer value for a column whenever a new row is inserted into a table. It's typically used with integer data types such as INT or BIGINT to create a primary key column with a unique value for each row.

Here's how you can use the AUTO_INCREMENT attribute while creating a table:


CREATE TABLE table_name (
    column_name INT AUTO_INCREMENT PRIMARY KEY,
    ...
);
 

In this syntax:

  • column_name is the name of the column you want to define as an auto-increment column.

  • INT is the data type of the column (it can be INT, BIGINT, etc.).
  • AUTO_INCREMENT specifies that the column should automatically generate a unique value for each new row inserted into the table.
  • PRIMARY KEY specifies that the column is the primary key for the table, which ensures that each value generated by the auto-increment column is unique.

For example, suppose you have a table named employees and you want to create an id column that automatically generates a unique value for each employee:


CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    ...
);
 

In this example, the id column is defined as an auto-increment primary key column. When a new row is inserted into the employees table without specifying a value for the id column, MySQL automatically generates a unique value for the id column.

Using the AUTO_INCREMENT attribute is a convenient way to generate unique identifiers for rows in a table, especially for primary key columns. It simplifies data insertion and retrieval operations and ensures data integrity by guaranteeing the uniqueness of each identifier.