MySQL AUTO_INCREMENT Field

MySQL Tutorial

MySQL AUTO_INCREMENT Field 

The AUTO_INCREMENT field in MySQL is used to automatically generate unique numbers for a column, usually for primary keys.

 Most commonly used for ID columns.

 What is AUTO_INCREMENT?

  • Automatically assigns a unique numeric value

  • Value increases by 1 for each new row (by default)

  • Eliminates the need to manually insert IDs

  •  Mostly used with PRIMARY KEY

 Why Use AUTO_INCREMENT?

  •  Automatically generates unique IDs
  •  Prevents duplicate key issues
  •  Simplifies INSERT queries
  •  Essential for real-world applications

 Basic Syntax

 Simple Example

Create Table

Insert Data

View Data

Output

  • id generated automatically

 AUTO_INCREMENT with PRIMARY KEY

Rule:
Only one AUTO_INCREMENT column is allowed per table
It must be:

  • Indexed

  • Usually a PRIMARY KEY

 Changing Starting Value

Start AUTO_INCREMENT from 100

  •  Next inserted record will start from 100

 Inserting Explicit Value

You can insert a value manually (not recommended normally):

  • Next auto value will continue after 200

 AUTO_INCREMENT After DELETE

  •  MySQL does NOT reuse deleted IDs

 Reset AUTO_INCREMENT

  •  Deletes all records
  •  Resets AUTO_INCREMENT to 1

 AUTO_INCREMENT with Different Data Types

Data TypeAllowed
INTYes
BIGINTYes
SMALLINTYes
VARCHARNo
  •  Must be a numeric type

 AUTO_INCREMENT vs SEQUENCE (Interview)

FeatureAUTO_INCREMENTSEQUENCE
MySQL supportYes (native)
Auto generatedYesYes
Reset easilyYesDepends
Table dependentYesNo

Interview Questions & MCQs

Q1. What is AUTO_INCREMENT used for?

A) Sorting
B) Unique ID generation
C) Data encryption
D) Indexing

Answer: B

Q2. How many AUTO_INCREMENT columns can a table have?

A) One
B) Two
C) Unlimited
D) None

Answer: A

Q3. AUTO_INCREMENT column must be?

A) VARCHAR
B) Indexed
C) NULL
D) TEXT

Answer: B

Q4. Which command resets AUTO_INCREMENT?

A) DELETE
B) DROP
C) TRUNCATE
D) UPDATE

Answer: C

Q5. Which data type is NOT allowed for AUTO_INCREMENT?

A) INT
B) BIGINT
C) SMALLINT
D) VARCHAR

Answer: D

Q6. What happens if a row is deleted?

A) ID reused
B) ID skipped
C) ID reset
D) Table dropped

Answer: B

Q7. Default starting value of AUTO_INCREMENT?

A) 0
B) 1
C) 10
D) Depends on OS

Answer: B

 Real-Life Use Cases

  •  User ID generation
  •  Order numbers
  •  Invoice IDs
  •  Employee records
  •  Transaction tracking

 Summary

  • AUTO_INCREMENT generates unique numbers automatically

  • Used mainly for primary keys

  • Only one per table

  • IDs are not reused

  • Very important for databases, interviews & exams

You may also like...