SQL AUTO INCREMENT

SQL AUTO INCREMENT is a feature used in SQL databases to automatically generate a unique value for a column in a table when a new row is inserted. This is particularly useful for primary keys.

Example Using MySQL

1. Creating a Table with AUTO_INCREMENT:

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

   Here, the id column will automatically increment by 1 each time a new row is inserted into the users table. The AUTO_INCREMENT keyword is used to specify that the id column should automatically increment, and PRIMARY KEY ensures that each id is unique.

2. Inserting Data into the Table:

   INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');
   INSERT INTO users (username, email) VALUES ('jane_doe', 'jane@example.com');
   

3. Table Data after Insertions:

   SELECT * FROM users;
   

   Output:

   +----+-----------+-----------------+
   | id | username  | email           |
   +----+-----------+-----------------+
   |  1 | john_doe  | john@example.com|
   |  2 | jane_doe  | jane@example.com|
   +----+-----------+-----------------+
   

   As you can see, the id column values are automatically incremented.

Example Using PostgreSQL

1. Creating a Table with SERIAL:

   CREATE TABLE users (
       id SERIAL PRIMARY KEY,
       username VARCHAR(255) NOT NULL,
       email VARCHAR(255) NOT NULL
   );
   

   In PostgreSQL, the SERIAL keyword is used to create an auto-incrementing integer column.

2. Inserting Data into the Table:

   INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');
   INSERT INTO users (username, email) VALUES ('jane_doe', 'jane@example.com');
   

3. Table Data after Insertions:

   SELECT * FROM users;
   

   Output:

   +----+-----------+-----------------+
   | id | username  | email           |
   +----+-----------+-----------------+
   |  1 | john_doe  | john@example.com|
   |  2 | jane_doe  | jane@example.com|
   +----+-----------+-----------------+
   

Example Using SQLite

1. Creating a Table with AUTOINCREMENT:

   CREATE TABLE users (
       id INTEGER PRIMARY KEY AUTOINCREMENT,
       username VARCHAR(255) NOT NULL,
       email VARCHAR(255) NOT NULL
   );
   

   In SQLite, AUTOINCREMENT ensures that the id column values are unique and increment automatically.

2. Inserting Data into the Table:

   INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');
   INSERT INTO users (username, email) VALUES ('jane_doe', 'jane@example.com');
   

3. Table Data after Insertions:

   SELECT * FROM users;
   

   Output:

   +----+-----------+-----------------+
   | id | username  | email           |
   +----+-----------+-----------------+
   |  1 | john_doe  | john@example.com|
   |  2 | jane_doe  | jane@example.com|
   +----+-----------+-----------------+
   

In each case, the id column automatically increments by 1 for each new row inserted, ensuring each row has a unique identifier.