INSERT and auto-generated keys in SQL - Step-by-Step Execution

Concept Flow - INSERT and auto-generated keys

Start INSERT command

↓

Check if key is auto-generated?

Yes→Database generates key

↓

Insert row with generated key

↓

Insert row with provided key

↓

Return generated key

↓

Commit row

↓

Finish

When inserting a row, the database checks if the key is auto-generated. If yes, it creates the key, inserts the row, and returns the key.

Execution Sample

SQL

INSERT INTO users (name) VALUES ('Alice');
-- id is auto-generated

SELECT * FROM users;

Insert a new user without specifying id; database generates id automatically.

Execution Table

Step	Action	Input Data	Auto-generated Key	Row Inserted	Output
1	Start INSERT	name='Alice'	N/A	No	No output yet
2	Check auto-generated key	id column is auto-increment	Generate id=1	No	No output yet
3	Insert row	id=1, name='Alice'	1	Yes	No output yet
4	Return generated key	id=1	1	Yes	Returns id=1
5	Finish	N/A	N/A	Yes	Row inserted with id=1

💡 Insertion stops after row is inserted and generated key is returned

Variable Tracker

Variable	Start	After Step 2	After Step 3	Final
id	NULL	1 (generated)	1	1
name	NULL	Alice	Alice	Alice

Key Moments - 3 Insights

Why don't we specify the id value in the INSERT statement?

Because the id column is auto-generated by the database, as shown in execution_table step 2 where the id is generated automatically.

How does the database know what id to assign?

The database uses the auto-increment property of the id column to generate the next available id, as seen in execution_table step 2.

What happens if we try to insert a row with a specified id when the column is auto-generated?

Usually, the database will either override the provided id or raise an error depending on settings; this is not shown here but is important to know.

Visual Quiz - 3 Questions

Look at the execution_table, what is the generated id after step 2?

A Alice

B 1

C NULL

D 0

💡 Hint

Check the 'Auto-generated Key' column in step 2 of execution_table

At which step is the row actually inserted into the table?

A Step 3

B Step 1

C Step 4

D Step 5

💡 Hint

Look at the 'Row Inserted' column in execution_table

If the id column was not auto-generated, what would change in the execution_table?

A Step 4 would return a generated key

B Step 2 would generate a key anyway

C Step 2 would be skipped and id must be provided

D The row would never be inserted

💡 Hint

Refer to the 'Check if key is auto-generated?' decision in concept_flow

Concept Snapshot

INSERT INTO table (columns) VALUES (values);
If a key column is auto-generated (like auto-increment), omit it in INSERT.
Database generates the key automatically during insertion.
The generated key can be returned after insertion.
If key is not auto-generated, you must provide it explicitly.

Full Transcript

This visual execution shows how an INSERT statement works when the table has an auto-generated key column. First, the INSERT command starts. The database checks if the key column is auto-generated. If yes, it generates a new key value. Then it inserts the row with the generated key and the provided data. Finally, it returns the generated key to the user. Variables like 'id' change from NULL to the generated value during execution. Key moments include understanding why the id is not specified and how the database generates it. The quiz questions help reinforce these steps by referencing the execution table and flow.

Practice

1. What does the INSERT statement do in SQL when adding a new row to a table with an auto-generated key?

easy

A. It updates the existing row with a new key.

B. It deletes an existing row based on the key.

C. It adds a new row and automatically creates a unique ID for that row.

D. It only creates a key without adding any row.

Solution

1. Step 1: Understand the purpose of INSERT

   The INSERT statement adds new rows to a table in a database.

2. Step 2: Recognize auto-generated keys

   When a table has an auto-generated key, the database automatically creates a unique ID for each new row inserted.

3. Final Answer:

   It adds a new row and automatically creates a unique ID for that row. -> Option C

4. Quick Check:

   INSERT adds row + auto key = It adds a new row and automatically creates a unique ID for that row. [OK]

Hint: INSERT adds rows; auto keys create unique IDs [OK]

Common Mistakes:

• Thinking INSERT deletes or updates rows
• Assuming keys must be manually provided
• Confusing INSERT with SELECT or DELETE

2. Which of the following is the correct syntax to insert a new row into a table users with columns name and email, letting the database auto-generate the id?

easy

A. INSERT INTO users (id, name, email) VALUES (1, 'Alice', '[e-mail]');

B. INSERT users (name, email) VALUES ('Alice', '[e-mail]');

C. INSERT INTO users VALUES ('Alice', '[e-mail]');

D. INSERT INTO users (name, email) VALUES ('Alice', '[e-mail]');

Solution

1. Step 1: Check correct INSERT syntax

   The correct syntax specifies the table name, columns, and values. Omitting the auto-generated key column lets the database handle it.

2. Step 2: Analyze each option

   INSERT INTO users (name, email) VALUES ('Alice', '[e-mail]'); correctly inserts only name and email, letting the database auto-generate the id. INSERT INTO users (id, name, email) VALUES (1, 'Alice', '[e-mail]'); provides an explicit id=1, overriding auto-generation. INSERT INTO users VALUES ('Alice', '[e-mail]'); misses column names and provides wrong number of values (2 vs 3 columns), causing error. INSERT users (name, email) VALUES ('Alice', '[e-mail]'); has wrong syntax (missing INTO).

3. Final Answer:

   INSERT INTO users (name, email) VALUES ('Alice', '[e-mail]'); -> Option D

4. Quick Check:

   Omit auto key column to auto-generate [OK]

Hint: Omit auto key column in INSERT to auto-generate [OK]

Common Mistakes:

• Including auto-generated key column with a manual value
• Missing INTO keyword in INSERT
• Not specifying columns when table has auto keys

3. Given the table orders with columns order_id (auto-generated), product, and quantity, what will this query return?

INSERT INTO orders (product, quantity) VALUES ('Book', 3) RETURNING order_id;

medium

A. The new order_id generated for the inserted row.

B. The number of rows inserted (1).

C. An error because RETURNING is not valid here.

D. The product and quantity values inserted.

Solution

1. Step 1: Understand RETURNING clause

   RETURNING lets you get values from the inserted row immediately, such as auto-generated keys.

2. Step 2: Analyze the query

   The query inserts a new order with product 'Book' and quantity 3, then returns the auto-generated order_id.

3. Final Answer:

   The new order_id generated for the inserted row. -> Option A

4. Quick Check:

   INSERT ... RETURNING returns new keys [OK]

Hint: RETURNING fetches new auto-generated keys after insert [OK]

Common Mistakes:

• Thinking RETURNING returns row count
• Assuming RETURNING is invalid syntax
• Expecting all inserted columns returned by default

4. What is wrong with this SQL statement if id is an auto-generated key?

INSERT INTO customers (id, name) VALUES (NULL, 'John');

medium

A. Using NULL for id may cause an error or fail to auto-generate the key.

B. The syntax is correct and will auto-generate id.

C. You must provide a valid id value manually.

D. The table name is missing.

Solution

1. Step 1: Understand auto-generated key behavior

   Auto-generated keys (like SERIAL in Postgres or AUTO_INCREMENT) generate a value when the column is omitted, explicitly set to NULL, or DEFAULT. Explicit non-NULL values override it.

2. Step 2: Analyze the statement

   INSERT INTO customers (id, name) VALUES (NULL, 'John'); explicitly sets id to NULL, which invokes the default generator, so the database auto-generates the next unique id. Syntax is correct.

3. Final Answer:

   The syntax is correct and will auto-generate id. -> Option B

4. Quick Check:

   NULL in auto-key column triggers generation [OK]

Hint: NULL in auto-key triggers generation; omit also works [OK]

Common Mistakes:

• Thinking explicit NULL causes an error or inserts NULL
• Believing you must omit the column and can't use NULL
• Confusing NULL with providing a manual non-NULL id

5. You want to insert multiple rows into a products table with an auto-generated product_id and get all new IDs immediately. Which SQL approach is best?

hard

A. Use a single INSERT with multiple rows and RETURNING to get all new IDs at once.

B. Use multiple INSERT statements each with RETURNING to get IDs one by one.

C. Insert rows without RETURNING and then query the table for new IDs by timestamp.

D. Manually generate IDs in your application and insert them with the rows.

Solution

1. Step 1: Understand batch insert with RETURNING

   Modern SQL supports inserting multiple rows in one statement and returning all generated keys at once.

2. Step 2: Compare options

   Use a single INSERT with multiple rows and RETURNING to get all new IDs at once. is efficient and returns all new IDs immediately. Use multiple INSERT statements each with RETURNING to get IDs one by one. is slower with multiple statements. Insert rows without RETURNING and then query the table for new IDs by timestamp. is unreliable and complex. Manually generate IDs in your application and insert them with the rows. defeats auto-generation purpose.

3. Final Answer:

   Use a single INSERT with multiple rows and RETURNING to get all new IDs at once. -> Option A

4. Quick Check:

   Batch INSERT + RETURNING = all IDs fast [OK]

Hint: Batch insert with RETURNING gets all keys at once [OK]

Common Mistakes:

• Running many single inserts instead of batch
• Querying after insert instead of RETURNING
• Manually creating keys instead of auto-generation