MySQL AUTO_INCREMENT Field

🔢 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.

1️⃣ 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

2️⃣ Why Use AUTO_INCREMENT?

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

3️⃣ Basic Syntax



 

4️⃣ Simple Example

Create Table



 

Insert Data



 

View Data

SELECT * FROM students;

Output

id | name | marks
1 | Amit | 75
2 | Ravi | 82
3 | Neha | 68

✔ id generated automatically

5️⃣ AUTO_INCREMENT with PRIMARY KEY ⭐

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

  • Indexed

  • Usually a PRIMARY KEY

id INT AUTO_INCREMENT PRIMARY KEY

6️⃣ Changing Starting Value

Start AUTO_INCREMENT from 100

ALTER TABLE students AUTO_INCREMENT = 100;

👉 Next inserted record will start from 100

7️⃣ Inserting Explicit Value

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



 

➡ Next auto value will continue after 200

8️⃣ AUTO_INCREMENT After DELETE ❓

DELETE FROM students WHERE id = 3;

📌 MySQL does NOT reuse deleted IDs

9️⃣ Reset AUTO_INCREMENT

TRUNCATE TABLE students;

✔ Deletes all records
✔ Resets AUTO_INCREMENT to 1

🔟 AUTO_INCREMENT with Different Data Types

Data Type Allowed
INT ✔
BIGINT ✔
SMALLINT ✔
VARCHAR ❌

📌 Must be a numeric type

1️⃣1️⃣ AUTO_INCREMENT vs SEQUENCE ⭐ (Interview)

Feature AUTO_INCREMENT SEQUENCE
MySQL support ✔ ❌ (native)
Auto generated Yes Yes
Reset easily Yes Depends
Table dependent Yes No

📌 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...