MySQL surrogate keys: Which column property is used in MySQL to generate surrogate key values automatically for each inserted row (monotonic integers without manual assignment)?

Introduction / Context:
Surrogate keys are system-generated identifiers that do not carry business meaning. In MySQL, a popular way to implement them is by using a special column attribute that auto-generates a new number for each inserted row, avoiding manual key management and race conditions.

Given Data / Assumptions:

• You are designing a primary key with no inherent business semantics.
• MySQL is the database platform.
• You prefer a built-in mechanism rather than triggers.

Concept / Approach:

The AUTO_INCREMENT attribute on an integer column instructs MySQL to assign the next available numeric value automatically when a row is inserted (unless explicitly provided). It is commonly combined with PRIMARY KEY. While sequences are used in some other DBMS products, classic MySQL (prior to introducing sequence objects in later versions) uses AUTO_INCREMENT for this behavior across storage engines like InnoDB.

Step-by-Step Solution:

Verification / Alternative check:

Run INSERT statements and observe that LAST_INSERT_ID() reflects the generated key, confirming correct AUTO_INCREMENT behavior.

Why Other Options Are Wrong:

UNIQUE: enforces uniqueness but does not generate values.

SEQUENCE: not the classic MySQL mechanism (used in other DBMSs).

“Not implemented”: incorrect—AUTO_INCREMENT is standard in MySQL.

Common Pitfalls:

Resetting AUTO_INCREMENT without understanding gaps; assuming sequential values are guaranteed without gaps after deletes or rollbacks.

Final Answer:

AUTO_INCREMENT