AUTO_INCREMENT behavior in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the AUTO_INCREMENT attribute do in MySQL?

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

intermediate

Can you manually insert a value into an AUTO_INCREMENT column?

Yes, you can insert a specific value manually, but it must not conflict with existing values or the next auto-generated value.

intermediate

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

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

advanced

How can you reset the AUTO_INCREMENT counter in MySQL?

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

advanced

Does AUTO_INCREMENT guarantee no gaps in the sequence of generated numbers?

No, gaps can occur due to deleted rows or rolled-back transactions, so AUTO_INCREMENT values are unique but not always consecutive.

What is the default behavior of AUTO_INCREMENT in MySQL when inserting a new row?

A It assigns the next available unique number automatically.

B It requires you to provide a number manually.

C It assigns a random number.

D It assigns NULL.

AUTO_INCREMENT automatically assigns the next unique number for the column.

If you delete the row with the highest AUTO_INCREMENT value, what happens to the next inserted row's AUTO_INCREMENT value?

A It throws an error.

B It reuses the deleted row's number.

C It resets to 1.

D It continues from the last highest value plus one.

The counter does not decrease; it continues from the last highest value plus one.

How do you reset the AUTO_INCREMENT counter to start from 100 in a table named 'users'?

A ALTER TABLE users AUTO_INCREMENT = 100;

B RESET AUTO_INCREMENT users TO 100;

C SET AUTO_INCREMENT users = 100;

D UPDATE users SET AUTO_INCREMENT = 100;

The correct syntax is ALTER TABLE users AUTO_INCREMENT = 100;

Can AUTO_INCREMENT columns have gaps in their sequence?

A No, they are always consecutive.

B Only if you manually insert values.

C Yes, gaps can occur due to deletions or rollbacks.

D Only if the table is corrupted.

Gaps can happen because of deleted rows or rolled-back transactions.

Which data type is commonly used with AUTO_INCREMENT columns?

A TEXT

B INTEGER

C VARCHAR

D DATE

AUTO_INCREMENT is typically used with integer types to generate unique numbers.

Explain how AUTO_INCREMENT works in MySQL and what happens when rows are deleted.

Think about how MySQL keeps track of the next number to assign.

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

Consider the SQL command that changes table structure.