SQL AUTO_INCREMENT / IDENTITY

SQL AUTO_INCREMENT / IDENTITY

To auto-generate unique values (usually for primary keys), databases offer built-in features like AUTO_INCREMENT (MySQL), IDENTITY (SQL Server), and GENERATED AS IDENTITY (PostgreSQL, Oracle).

🔹 Basic Usage by DBMS

Copy

-- MySQL
CREATE TABLE employees (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100)
);

-- SQL Server
CREATE TABLE employees (
  id INT IDENTITY(1,1) PRIMARY KEY, -- (start, increment)
  name VARCHAR(100)
);

-- PostgreSQL & Oracle (modern versions)
CREATE TABLE employees (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(100)
);

🔹 Insert Example

Copy

INSERT INTO employees (name) VALUES ('Alice');
-- No need to provide `id`; it auto-generates

🔹 Controlling Identity (Optional)

• MySQL: You can set AUTO_INCREMENT = value to restart or offset.
• SQL Server: Use DBCC CHECKIDENT to reseed identity values.
• PostgreSQL: Use sequences behind the scenes (can ALTER SEQUENCE).
• Oracle: Uses sequences automatically with IDENTITY.

🧠 Quick Recap

💡 Use auto-increment/identity columns to simplify primary key management and ensure uniqueness without manual effort.