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 | |
---|---|---|
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 |
|
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 |
|
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 |
|