SQL Auto Increment Field

In this lesson, you will learn how to create an auto-increment field using SQL.

What Is Auto-Increment Field

A table with an auto-increment field means that any newly inserted record will automatically have a value for that field calculated by the database server.

The auto-increment field is mostly used to make every record have a unique identifier number (ID), which allow us to distinguish records based on these unique numbers.

Preparing A Database

Execute the following query to create and select a new database.

-- If there is already a database named "demo" delete it
DROP DATABASE IF EXISTS demo;

-- Create a new database called "demo"
CREATE DATABASE demo;

-- Select the "demo" database
USE demo;
    

Create Auto Increment Field

The syntax of creating an auto-increment field varies from one database to another.

• In MySQL, we can use INT AUTO_INCREMENT
• In SQL Server, we can use IDENTITY(1,1)
• In Access and SQLite, we can use AUTOINCREMENT
• In Oracle, we can use CREATE SEQUENCE seq-name MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10;

In MySQL, you can create a define a field as primary key and auto-increment as follows.

CREATE TABLE table-name (
    column-name-1 INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    column-name-2 data-type,
    ...
)

The following query creates a table called "users" that has an ID field defined as auto-increment and primary key.

CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    email VARCHAR(100)
);
    

Insert Record With Auto Increment Field

The following query inserts 5 records in the users table.
Note: we didn't set values for the id field.

INSERT INTO users (username, email) VALUES ('mhamad', 'mhamad@example.com');
INSERT INTO users (username, email) VALUES ('hala', 'hala@example.com');
INSERT INTO users (username, email) VALUES ('adam', 'adam@example.com');
INSERT INTO users (username, email) VALUES ('jane', 'jane@example.com');
INSERT INTO users (username, email) VALUES ('sarah', 'sarah@example.com');
    

Now, if you retrieve all the records from the users table you will see that every record has a unique id number.

SELECT * FROM users;
    

Auto Increment Exercise

1- Write a query that creates a new database called "training".
Note: you should recreate it if it already exists.

2- Write a query that creates a table called "products" and check whether it exists or not. the table should contain the following fields:

• id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
• title VARCHAR(100)
• description VARCHAR(500)
• price DECIMAL(7,2)

3- Write a query that inserts 3 rows in the "products" table.

4- Write a query that selects all the records from the "products" table.

DROP DATABASE IF EXISTS training;

CREATE DATABASE training;

USE training;

CREATE TABLE IF NOT EXISTS products (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100),
    description VARCHAR(500),
    price DECIMAL(7,2)
);

INSERT INTO products (title, description, price) VALUES ('Electric Chain Saw', '6 Inch Electric Chain Saw For Makita 18V Lithium Ion Battery Mini Cordless Garden Logging Saw Woodworking Cutting Power Tool', 33.12);
INSERT INTO products (title, description, price) VALUES ('Warm Parka Jacket', 'Thick Men New Warm Parka Jackets Winter Casual Men\'s Outwear Coats Solid Stand Collar Male Windbreak Cotton Padded Down Jacket', 4.79);
INSERT INTO products (title, description, price) VALUES ('Short Boots Leather', 'Women Short Boots Leather Buckle Decor Ankle Boots 2022 Runway Female Shoes Slip On Motorcycle Boots For Women', 24.79);

SELECT * FROM products;