Introduction

If a table has an auto-increment primary key, you normally do not insert that column yourself. You insert the non-key values, and the database generates the next identifier automatically. If the table already exists and does not yet have such a column, you first alter the schema and then change your INSERT statements to omit the key.

How Auto-Increment Columns Work

An auto-increment column is a numeric column whose next value is produced by the database engine. Different systems use different names:

• MySQL uses AUTO_INCREMENT
• PostgreSQL commonly uses GENERATED ... AS IDENTITY
• SQL Server uses IDENTITY

The principle is the same in all of them. The key column belongs to the table definition, not the insert statement. Once the column is defined, your insert statements should usually name only the business columns.

Inserting Rows Into a Table That Already Has an Auto Key

In MySQL, if the table is already defined correctly, the insert is simple:

1CREATE TABLE users (
2    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
3    username VARCHAR(100) NOT NULL,
4    email VARCHAR(255) NOT NULL
5);
6
7INSERT INTO users (username, email)
8VALUES ('mark', '[email protected]');

The database assigns id = 1 for the first row, then 2, then 3, and so on. The important detail is that id is not listed in the INSERT.

If you want the generated key back, many drivers support that directly. In MySQL SQL, you can ask for it with:

SELECT LAST_INSERT_ID();

That returns the last auto-generated value for the current connection.

Adding an Auto-Increment Key to an Existing Table

Sometimes the real problem is that the table already exists without a suitable primary key. In MySQL, one common fix is:

ALTER TABLE users
ADD COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

After that, existing rows receive generated values and future inserts can omit the id column.

For PostgreSQL, a modern identity-based version looks like this:

1ALTER TABLE users
2ADD COLUMN id BIGINT GENERATED BY DEFAULT AS IDENTITY;
3
4ALTER TABLE users
5ADD PRIMARY KEY (id);

With an existing populated table, the database fills values for current rows and continues generating new ones for later inserts.

Insert Multiple Rows at Once

Auto-increment works just as well for bulk inserts. You still omit the key:

1INSERT INTO users (username, email)
2VALUES
3    ('ada', '[email protected]'),
4    ('grace', '[email protected]'),
5    ('linus', '[email protected]');

Each row gets a unique key. You do not need to manually calculate the next id value, and you should not try to do that in application code.

When You Do Specify the Key

Some systems allow you to explicitly insert an id value, but that is usually for migrations or controlled imports. It is not the normal path for day-to-day inserts.

For example:

INSERT INTO users (id, username, email)
VALUES (1000, 'imported-user', '[email protected]');

That can work, but you must understand how it interacts with the auto-increment counter. In many databases, the next generated value may need to catch up to avoid duplicates.

Application Code Example

A parameterized insert from application code looks the same conceptually. The application sends only the non-key values:

1import sqlite3
2
3conn = sqlite3.connect(":memory:")
4cur = conn.cursor()
5
6cur.execute("""
7CREATE TABLE users (
8    id INTEGER PRIMARY KEY AUTOINCREMENT,
9    username TEXT NOT NULL,
10    email TEXT NOT NULL
11)
12""")
13
14cur.execute(
15    "INSERT INTO users (username, email) VALUES (?, ?)",
16    ("alice", "[email protected]"),
17)
18
19conn.commit()
20
21cur.execute("SELECT id, username, email FROM users")
22print(cur.fetchall())

The same pattern applies across drivers: omit the key column, bind the actual data columns, and let the database own id generation.

Choosing the Right Schema Change

If you are modifying an existing production table, take care with these questions:

• Does the table already have a primary key
• Do foreign keys reference it
• Are there duplicate candidate values that would block a new key definition
• Does your database allow the exact ALTER TABLE syntax you expect

That is why "auto increment" is partly a data-model question, not just an INSERT syntax question.

Common Pitfalls

The most common mistake is including the auto-increment column in every insert and manually trying to calculate the next value. That creates race conditions and defeats the point of the feature.

Another issue is altering a populated table without checking constraints, foreign keys, or database-specific syntax differences. Developers also sometimes assume one vendor's keywords work everywhere, but AUTO_INCREMENT, IDENTITY, and SERIAL are not interchangeable.

Summary

• If a table has an auto-increment key, omit that column from normal INSERT statements.
• Add the key at the schema level first if the existing table does not already have one.
• Database engines use different syntax, but the workflow is the same.
• Bulk inserts work normally and still get unique generated ids.
• Let the database generate primary keys instead of computing them in application code.