AUTO_INCREMENT
In this page:
column_name INT AUTO_INCREMENT PRIMARY KEYUnderstanding AUTO_INCREMENT
AUTO_INCREMENT tells MySQL to generate the next integer in sequence automatically whenever a new row is inserted, which is why it's almost always paired with a primary-key ID column.
Example: Understanding AUTO_INCREMENT
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));Changing the Start Value
By default the sequence starts at 1 and increases by 1 each time, but you can set a custom starting value — useful when migrating data and needing new IDs to avoid colliding with an old system's existing range.
Example: Changing the Start Value
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT = 1000;Adding AUTO_INCREMENT to Existing Tables
You can add AUTO_INCREMENT to an existing column via ALTER TABLE, but that column must already be defined as a key (typically the primary key) — MySQL won't auto-generate values for an unindexed column.
Example: Adding AUTO_INCREMENT to Existing Tables
ALTER TABLE users MODIFY id INT AUTO_INCREMENT;Removing AUTO_INCREMENT
Removing AUTO_INCREMENT stops the automatic number generation going forward, meaning future inserts must supply their own value for that column explicitly or rely on a DEFAULT instead.
Example: Removing AUTO_INCREMENT
ALTER TABLE users MODIFY id INT;Retrieving the Last Inserted ID
LAST_INSERT_ID() returns the auto-generated ID from your most recent insert within the same session, which is exactly what you need when inserting a parent row and then immediately inserting related child rows referencing it.
Example: Retrieving the Last Inserted ID
CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));
INSERT INTO users (name) VALUES ('Amit');
SELECT LAST_INSERT_ID();- Inserting a value into the
AUTO_INCREMENTcolumn and then being surprised that the sequence continues from it instead of filling gaps. - Assuming
AUTO_INCREMENTnumbers are gap-free, when deleted rows and failed inserts leave gaps that are not reused. - Declaring
AUTO_INCREMENTon a column that is not a key, which is an error because it must be indexed.
- Constraints such as
NOT NULL,UNIQUE,DEFAULT, andCHECKenforce rules on column values. PRIMARY KEYidentifies each row, andFOREIGN KEYlinks tables together.AUTO_INCREMENTgenerates unique numbers automatically for new rows.
Chapter Quiz — Complete all 8 topics to unlock
0/8 topics done
Complete these topics first: