Auto Increment in SQL
? Auto Increment in SQL
Auto Increment is a feature that automatically generates a unique number for a column when a new row is inserted. It's commonly used for primary keys to ensure each row has a unique identifier.
? How it works
The database automatically increments the value by 1 (by default) for each new row.
You don’t need to specify a value when inserting data.
The column is usually an integer type.
? Syntax & Usage in Different Databases
| Database | Syntax Example |
|---|---|
| MySQL | sql<br>CREATE TABLE users (<br> id INT AUTO_INCREMENT PRIMARY KEY,<br> name VARCHAR(100)<br>); |
| PostgreSQL | Uses SERIAL type:sql<br>CREATE TABLE users (<br> id SERIAL PRIMARY KEY,<br> name VARCHAR(100)<br>); |
| SQL Server | Uses IDENTITY:sql<br>CREATE TABLE users (<br> id INT IDENTITY(1,1) PRIMARY KEY,<br> name VARCHAR(100)<br>); |
| SQLite | Uses INTEGER PRIMARY KEY AUTOINCREMENT:sql<br>CREATE TABLE users (<br> id INTEGER PRIMARY KEY AUTOINCREMENT,<br> name TEXT<br>); |
? Example: MySQL
CREATE TABLE employees ( emp_id INT AUTO_INCREMENT PRIMARY KEY, emp_name VARCHAR(50), emp_salary DECIMAL(10, 2));INSERT INTO employees (emp_name, emp_salary) VALUES ('Alice', 50000);INSERT INTO employees (emp_name, emp_salary) VALUES ('Bob', 60000);-- emp_id auto-increments automatically: 1, 2, ...? Key Points
Only one auto-increment column allowed per table.
Usually defined as the primary key.
You do not provide a value for this column during insertion.
The auto-increment value is unique and sequential (unless reset).
Let me know if you want examples on resetting auto-increment or working with it during INSERT or UPDATE!
