Beginner LevelSQL & MySQL
How do AUTO_INCREMENT and IDENTITY columns generate surrogate keys?
❓ Interview Prompt: Explain `AUTO_INCREMENT` (MySQL) and `IDENTITY` / `GENERATED ALWAYS AS IDENTITY` (PostgreSQL/SQL Server) surrogate key generation.
'AUTO_INCREMENT' and 'IDENTITY' columns automatically generate sequential, unique numeric surrogate keys whenever new rows are inserted into a table:
Mechanism:
•
The database engine maintains an internal counter, incrementing it automatically on each 'INSERT'.
•
Ensures primary key uniqueness without requiring client applications to compute sequence numbers.
Code Implementation Example
sql
CREATE TABLE categories (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
-- Insertion automatically populates id column with next sequence number
INSERT INTO categories (name) VALUES ('Electronics'), ('Books');💡Creating auto-incrementing primary key columns in MySQL.
🎯 Interview Key Takeaway
Use AUTO_INCREMENT or IDENTITY columns to generate unique numeric surrogate primary keys automatically.
#SQL#AUTO_INCREMENT#IDENTITY#Surrogate Key#Beginner
Back to all SQL & MySQL Questions→