SQL Tutorial

SQL Introduction SQL Aggregate Functions SQL Aliases SQL And SQL Any All SQL Avg SQL Between SQL Case SQL Comments SQL Count SQL Delete SQL Distinct SQL Exists SQL Groupby SQL Having SQL In SQL Insert_into SQL Is Not Null SQL Join SQL Full Outer Join SQL Inner Join SQL Left Join SQL Right Join SQL Self Join SQL Like SQL Min Max SQL NOT Operator SQL Null SQL Operators SQL OR operator SQL OrderBy SQL Select SQL Select Into SQL Top Limit Fetch SQL Store Procedures SQL Sum SQL Union SQL Update SQL Where SQL Wildcards

SQL Database

SQL Alter Table SQL Auto increment SQL BackupDB SQL Check SQL Constrains SQL Create View SQL CreateDB SQL CreateTable SQL Data types SQL Dates SQL DefaultConstrain SQL DropDB SQL DropTable SQL Foreign Key SQL Hosting SQL Index SQL injections SQL Not NULL SQL PrimaryKey SQL Unique SQL Views

SQL AUTO INCREMENT Tutorial

Example Table

We will use the following table named users for our examples:


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

    INSERT INTO users (username, email) VALUES
    ('Alice', 'alice@example.com'),
    ('Bob', 'bob@example.com'),
    ('Charlie', 'charlie@example.com');
    

Users Table

user_id username email
1 Alice alice@example.com
2 Bob bob@example.com
3 Charlie charlie@example.com

Using SQL AUTO INCREMENT

The AUTO_INCREMENT attribute is used to generate a unique identifier for new rows in a table. It is typically used for the primary key column.

Example: Creating a Table with AUTO INCREMENT

To create a table named users with an AUTO_INCREMENT column:


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

Result:

Query CREATE TABLE users (user_id INT AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50), email VARCHAR(100));
Result
  • Table users created with columns user_id, username, and email.
  • user_id is an AUTO_INCREMENT column.

Example: Inserting Data into an AUTO INCREMENT Column

To insert data into the users table without specifying the user_id:


    INSERT INTO users (username, email) VALUES
    ('David', 'david@example.com');
    

Result:

Query INSERT INTO users (username, email) VALUES ('David', 'david@example.com');
Result
  • New row added with user_id automatically set to the next available value (e.g., 4).
  • username: David, email: david@example.com

Example: Resetting the AUTO INCREMENT Value

To reset the AUTO_INCREMENT value of the users table:


    ALTER TABLE users AUTO_INCREMENT = 1;
    

Result:

Query ALTER TABLE users AUTO_INCREMENT = 1;
Result
  • The next user_id value will be reset to 1 (if no rows exist) or the next available value.