SQL Auto increment

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

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:

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:

Example: Resetting the AUTO INCREMENT Value

To reset the AUTO_INCREMENT value of the users table:

    ALTER TABLE users AUTO_INCREMENT = 1;
    

Result: