INSERT and auto-generated keys in SQL

Introduction

We use INSERT to add new data into a table. Auto-generated keys help create unique IDs automatically for each new row, so we don't have to make them ourselves.

Adding a new user to a website where each user needs a unique ID.

Storing new orders in a sales database with automatically assigned order numbers.

Inserting new products into inventory with unique product IDs generated by the system.

Recording new blog posts where each post gets a unique post ID automatically.

Syntax

SQL

INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

-- To get the auto-generated key (example in PostgreSQL):
INSERT INTO table_name (column1, column2) VALUES (value1, value2) RETURNING id;

You list the columns you want to fill and then provide the matching values.

Auto-generated keys are often primary keys set to increase automatically (like SERIAL in PostgreSQL or AUTO_INCREMENT in MySQL).

Examples

Adds a new user named Alice with her email.

SQL

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

Adds Bob and returns the new user's auto-generated ID.

SQL

INSERT INTO users (name, email) VALUES ('Bob', '[e-mail]') RETURNING id;

Inserts a new product 'Pen' with price 1.5.

SQL

INSERT INTO products (product_name, price) VALUES ('Pen', 1.5);

Sample Program

This creates a customers table with an auto-incrementing customer_id. Then it inserts a new customer and returns the new customer_id.

SQL

CREATE TABLE customers (
  customer_id SERIAL PRIMARY KEY,
  name VARCHAR(50),
  email VARCHAR(50)
);

INSERT INTO customers (name, email) VALUES ('John Doe', '[e-mail]') RETURNING customer_id;

Important Notes

Different databases have different ways to auto-generate keys: SERIAL in PostgreSQL, AUTO_INCREMENT in MySQL, IDENTITY in SQL Server.

Using RETURNING or similar clauses helps you get the new key right after inserting.

Always specify columns in INSERT to avoid errors if table structure changes.

Summary

INSERT adds new rows to a table.

Auto-generated keys create unique IDs automatically.

RETURNING lets you get the new ID immediately after insert.