SQL INSERT INTO — Add Rows to Tables

Table Hierarchy & Schema

Database → tables

CREATE TABLE (reference)json

1CREATE TABLE users (
2  user_id   INTEGER PRIMARY KEY,
3  email     VARCHAR(255) NOT NULL UNIQUE,
4  is_active BOOLEAN NOT NULL DEFAULT TRUE
5);
6
7CREATE TABLE orders (
8  order_id     INTEGER PRIMARY KEY,
9  customer_id  INTEGER NOT NULL,
10  status       VARCHAR(20) NOT NULL DEFAULT 'pending',
11  total_amount DECIMAL(10, 2) NOT NULL,
12  FOREIGN KEY (customer_id) REFERENCES users (user_id)
13);

Entity-Relationship Diagram

Insert order → must reference existing user

PK/FK Insert Flow

Foreign key check on INSERT

Sample Data (Starting Point)

usersjson

1| user_id | email               | is_active |
2|---------|---------------------|-----------|
3| 1       | [email protected]   | true      |
4| 2       | [email protected]     | false     |
5| 3       | [email protected]   | true      |

ordersjson

1| order_id | customer_id | status   | total_amount |
2|----------|-------------|----------|--------------|
3| 1001     | 1           | paid     | 49.99        |
4| 1002     | 1           | shipped  | 12.50        |
5| 1003     | 3           | pending  | 8.00         |

Seed scriptjson

1INSERT INTO users (user_id, email, is_active) VALUES
2  (1, '[email protected]', TRUE),
3  (2, '[email protected]', FALSE),
4  (3, '[email protected]', TRUE);
5
6INSERT INTO orders (order_id, customer_id, status, total_amount) VALUES
7  (1001, 1, 'paid', 49.99),
8  (1002, 1, 'shipped', 12.50),
9  (1003, 3, 'pending', 8.00);

Single-Row INSERT

Bob's first orderjson

1INSERT INTO orders (order_id, customer_id, status, total_amount)
2VALUES (1004, 2, 'pending', 19.99);

Insert order

Parent row first when creating both: insert into users before orders that reference them.

Multi-Row INSERT

Add a new user and two orders in one statement eachjson

1INSERT INTO users (user_id, email, is_active) VALUES
2  (4, '[email protected]', TRUE);
3
4INSERT INTO orders (order_id, customer_id, total_amount) VALUES
5  (1005, 4, 15.00),
6  (1006, 4, 22.00);

status and is_active use DEFAULT when omitted.

FK Violation Example

This INSERT fails — user 99 does not existjson

1INSERT INTO orders (order_id, customer_id, total_amount)
2VALUES (1007, 99, 10.00);
3-- ERROR: foreign key constraint failed

INSERT … SELECT Preview

Copy pending orders to an archive table (pattern)json

1INSERT INTO orders_archive (order_id, customer_id, status, total_amount)
2SELECT order_id, customer_id, status, total_amount
3FROM orders
4WHERE status = 'pending';

Constraints Reminder

Constraint	Role on INSERT
PRIMARY KEY (user_id / order_id)	Uniquely identifies each row; join anchor
FOREIGN KEY (customer_id)	Order must reference a valid users.user_id
NOT NULL	Required columns cannot be missing on INSERT/UPDATE
UNIQUE (email)	No duplicate emails in users
DEFAULT (status, is_active)	Value applied when column omitted on INSERT

Common Mistakes

Duplicate primary key or unique email → insert rejected.
Inserting child (order) before parent (user) when FK is enforced.
Omitting NOT NULL columns without DEFAULT.

Practice Prompts

Insert a new active user and their first paid order in two statements.
What error do you expect inserting duplicate email [email protected]?
Add two orders for Carol in one multi-row INSERT.

Try These Tools

SQL Data Generator

Generate INSERT scripts for sample tables

Open tool

SQL Validator

Validate INSERT syntax

Open tool

Continue Learning

Relational Databases & Tables

PK/FK fundamentals

UPDATE — Changing Data

Modify rows after inserting

Frequently Asked Questions

What columns must I include in INSERT?

All NOT NULL columns without a DEFAULT. Primary keys must be unique. Foreign keys must reference existing parent rows.

Can I omit columns with DEFAULT?

Yes. status defaults to pending and is_active defaults to TRUE in our schema when omitted (vendor-specific DEFAULT syntax).

What happens if customer_id does not exist?

The database rejects the INSERT with a foreign key violation — the order cannot point to a missing user.

Single-row vs multi-row INSERT?

Multi-row INSERT adds several value tuples in one statement — faster and keeps related rows in one transaction.

What is INSERT … SELECT?

Inserts rows produced by a query — useful for copying or transforming data from another table.