How to Use Auto Increment in SQL

Auto Increment is used to automatically generate a unique number (usually for primary keys) for each new row inserted into a table.

How to Define Auto Increment Column

1. MySQL

CREATE TABLE employees (    employee_id INT AUTO_INCREMENT PRIMARY KEY,    name VARCHAR(100),    department VARCHAR(50));

• AUTO_INCREMENT automatically increases the employee_id value with each inserted row.

• Only one AUTO_INCREMENT column allowed per table.

• The column is usually a PRIMARY KEY or UNIQUE.

2. PostgreSQL

PostgreSQL uses SERIAL or BIGSERIAL:

CREATE TABLE employees (    employee_id SERIAL PRIMARY KEY,    name VARCHAR(100),    department VARCHAR(50));

• SERIAL creates an integer column with an associated sequence to auto-increment.

3. SQL Server

SQL Server uses IDENTITY:

CREATE TABLE employees (    employee_id INT IDENTITY(1,1) PRIMARY KEY,    name VARCHAR(100),    department VARCHAR(50));

• IDENTITY(1,1) means start at 1 and increment by 1.

4. Oracle

Oracle doesn’t have AUTO_INCREMENT. Instead, you use Sequences and Triggers (or since 12c, IDENTITY columns):

CREATE TABLE employees (    employee_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,    name VARCHAR2(100),    department VARCHAR2(50));

Summary Table

If you want help setting this up in your specific database or have any questions, just ask!