MySQL AUTO_INCREMENT Field

MySQL AUTO_INCREMENT Field

The AUTO_INCREMENT attribute in MySQL is used to automatically generate a unique number for a column whenever a new row is inserted.
It is commonly used for primary key columns to uniquely identify each record.

⚠️ Only one AUTO_INCREMENT column is allowed per table, and it must be of an integer type (INT, BIGINT, SMALLINT, etc.).

🔹 Syntax (During Table Creation)

• AUTO_INCREMENT automatically increases the column value for each new row.

• Usually paired with PRIMARY KEY.

🔹 Example 1: Simple AUTO_INCREMENT

• student_id will automatically increment for each new student: 1, 2, 3, …

• No need to provide a value for student_id when inserting data.

🔹 Example 2: Inserting Data Without AUTO_INCREMENT Column

Resulting Table:

• student_id values are automatically generated.

🔹 Example 3: Setting Starting Value and Increment

• The first row will have emp_id = 1001

• Subsequent rows increment automatically: 1002, 1003, …

To change the increment step (default is 1), you can set:

🔹 Key Points

1. AUTO_INCREMENT automatically generates unique numbers for new rows.

2. Must be integer type and usually a primary key.

3. Only one AUTO_INCREMENT column per table.

4. Can set starting value and optionally modify increment step.

5. Eliminates the need to manually manage unique IDs.