SQL AUTO INCREMENT

Introduction

In this chapter, we will focus on the AUTO INCREMENT feature in SQL. Auto increment is used to generate unique identifiers for new rows in a table automatically. This feature is commonly used for primary key columns to ensure that each row has a unique identifier. This chapter will cover the definition, syntax, and provide examples to help you understand how to use auto increment effectively.

What is AUTO INCREMENT?

The AUTO INCREMENT attribute allows a unique number to be generated automatically when a new record is inserted into a table. Typically, this feature is used for primary key columns.

Syntax for AUTO INCREMENT

MySQL

CREATE TABLE table_name (
    column_name INT AUTO_INCREMENT,
    other_columns datatype,
    PRIMARY KEY (column_name)
);

SQL Server

CREATE TABLE table_name (
    column_name INT IDENTITY(1,1),
    other_columns datatype,
    PRIMARY KEY (column_name)
);

PostgreSQL

CREATE TABLE table_name (
    column_name SERIAL,
    other_columns datatype,
    PRIMARY KEY (column_name)
);

Step-by-Step Example

Sample Table

First, let's create a sample table named employees with an auto-incrementing primary key.

MySQL

CREATE TABLE employees (
    id INT AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    PRIMARY KEY (id)
);

SQL Server

CREATE TABLE employees (
    id INT IDENTITY(1,1),
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    PRIMARY KEY (id)
);

PostgreSQL

CREATE TABLE employees (
    id SERIAL,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    PRIMARY KEY (id)
);

Inserting Data

When inserting data into the table, you do not need to specify a value for the auto-increment column. It will automatically generate a unique value.

INSERT INTO employees (first_name, last_name, email)
VALUES
('Ramesh', 'Kumar', 'ramesh.kumar@example.com'),
('Sita', 'Patel', 'sita.patel@example.com'),
('Arjun', 'Singh', 'arjun.singh@example.com');

Retrieving Data

To verify the data, you can retrieve all rows from the employees table.

SELECT * FROM employees;

Output

id	first_name	last_name	email
1	Ramesh	Kumar	ramesh.kumar@example.com
2	Sita	Patel	sita.patel@example.com
3	Arjun	Singh	arjun.singh@example.com

Using AUTO INCREMENT with Other Constraints

You can use the AUTO INCREMENT attribute in conjunction with other constraints such as UNIQUE, NOT NULL, and FOREIGN KEY.

Example

CREATE TABLE departments (
    department_id INT AUTO_INCREMENT,
    department_name VARCHAR(50),
    PRIMARY KEY (department_id)
);

CREATE TABLE employees (
    id INT AUTO_INCREMENT,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    email VARCHAR(100),
    department_id INT,
    PRIMARY KEY (id),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Changing the Starting Value

You can change the starting value of an auto-increment column.

MySQL

ALTER TABLE employees AUTO_INCREMENT = 100;

SQL Server

DBCC CHECKIDENT ('employees', RESEED, 100);

PostgreSQL

ALTER SEQUENCE employees_id_seq RESTART WITH 100;

Resetting the Auto Increment Value

In some cases, you may want to reset the auto-increment value.

MySQL

ALTER TABLE employees AUTO_INCREMENT = 1;

SQL Server

DBCC CHECKIDENT ('employees', RESEED, 0);

PostgreSQL

ALTER SEQUENCE employees_id_seq RESTART WITH 1;

Conclusion

The AUTO INCREMENT feature is essential for generating unique identifiers automatically for new rows in a table. This chapter covered the basic syntax for using auto increment in MySQL, SQL Server, and PostgreSQL, provided examples to illustrate its use, and demonstrated how to change and reset the auto-increment value. Understanding how to use auto increment effectively will greatly enhance your ability to manage unique identifiers in a relational database.