Which two statements are true about AUTO_INCREMENT?

Question

Options

• AAUTO_INCREMENT values allocated to a transaction that is rolled back are not reused.
• BA table can have multiple AUTO_INCREMENT columns.
• CA server restart always resets the AUTO_INCREMENT value to largest value in the
• DThe decimal data type supports AUTO_INCREMENT.
• EAn AUTO_INCREMENT column must be indexed.

• A
  86% (18)
• B
  10% (2)
• C
  5% (1)

Explanation

A is correct because MySQL intentionally discards AUTO_INCREMENT values used in rolled-back transactions - this is a deliberate design choice to avoid the overhead of tracking and reclaiming those values, which means gaps in the sequence are expected and normal.

E is correct because an AUTO_INCREMENT column must be the first column in an index (typically the PRIMARY KEY or a UNIQUE index) - MySQL enforces this requirement at table creation.

B is wrong - a table can only have one AUTO_INCREMENT column, and it must be a key column.

C is wrong - since MySQL 8.0, the AUTO_INCREMENT counter is persisted to the redo log, so a server restart no longer resets it to the max value; in older versions (pre-8.0) the restart did recalculate it, but the statement says "always," making it unreliable and incorrect as a blanket truth.

D is wrong - AUTO_INCREMENT only works with integer data types (TINYINT, SMALLINT, INT, BIGINT, etc.); DECIMAL is not supported.

Memory tip: Think "Always Endure" - AUTO_INCREMENT values are Abandoned on rollback (never reused) and must always Exist within an index. If you remember these two behaviors, A and E lock in naturally.

No community discussion yet for this question.