AUTO_INCREMENT behavior in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does AUTO_INCREMENT do in a SQL table?

AUTO_INCREMENT automatically generates a unique number for a column when a new row is added, usually used for primary keys.

intermediate

Can you insert a specific value into an AUTO_INCREMENT column?

Yes, you can insert a specific value manually, but it must not conflict with existing values to avoid errors.

intermediate

What happens if you delete the last row with the highest AUTO_INCREMENT value?

The AUTO_INCREMENT counter does not decrease automatically; new rows continue from the last highest value plus one.

advanced

How can you reset the AUTO_INCREMENT counter in MySQL?

You can reset it using: ALTER TABLE table_name AUTO_INCREMENT = value; where value is the new starting number.

beginner

Why is AUTO_INCREMENT useful in real-life applications?

It helps create unique IDs automatically, like customer IDs or order numbers, so you don’t have to manage them manually.

What type of column usually uses AUTO_INCREMENT?

A Foreign key

B Text column

C Date column

D Primary key

AUTO_INCREMENT is typically used for primary key columns to generate unique IDs automatically.

If you insert a row without specifying the AUTO_INCREMENT column, what happens?

A The column gets a unique incremented number automatically

B The insert fails

C The column is set to NULL

D The column is set to 0

When you omit the AUTO_INCREMENT column in an insert, the database assigns the next unique number automatically.

After deleting rows, what happens to the AUTO_INCREMENT counter?

A It stays the same and continues incrementing

B It resets to 1 automatically

C It decreases by the number of deleted rows

D It causes an error on next insert

Deleting rows does not reset the AUTO_INCREMENT counter; it keeps increasing from the last highest value.

How do you manually reset the AUTO_INCREMENT value in MySQL?

A UPDATE table_name SET AUTO_INCREMENT = new_value;

B ALTER TABLE table_name AUTO_INCREMENT = new_value;

C RESET AUTO_INCREMENT table_name TO new_value;

D DROP AUTO_INCREMENT FROM table_name;

The correct syntax to reset AUTO_INCREMENT in MySQL is using ALTER TABLE with AUTO_INCREMENT = new_value.

What happens if you try to insert a duplicate value into an AUTO_INCREMENT column?

A The duplicate is allowed

B The database changes the value automatically

C The insert fails with an error

D The row is ignored

Inserting a duplicate value into an AUTO_INCREMENT column causes an error because the column must be unique.

Explain how AUTO_INCREMENT works when adding new rows to a table.

Think about how IDs are assigned automatically without you typing them.

Describe how to reset the AUTO_INCREMENT counter and why you might want to do that.

Consider when you want your IDs to start fresh or from a specific number.