INSERT

Definition

INSERT adds a new row to a table. It's the most basic DML statement, and the entry point for all data that ever ends up in a database.

How It Works

sql
INSERT INTO employees (id, name, department, salary)
VALUES (9, 'Farah', 'Engineering', 75000);

Naming the columns explicitly ((id, name, department, salary)) is a best practice: it makes the statement immune to breaking if the table's column order ever changes, and it lets you omit columns that have a DEFAULT or allow NULL. Omitting the column list (INSERT INTO employees VALUES (9, 'Farah', 'Engineering', 75000)) works too, but relies entirely on positional order matching the table's current physical column order — a fragile assumption once a schema evolves.

Edge Cases and Pitfalls

• Omitting a column that is NOT NULL with no DEFAULT causes the INSERT to fail — this is intentional (see Chapter 10's constraint topics), not a bug to work around.
• Inserting a value that violates a UNIQUE, CHECK, or FOREIGN KEY constraint fails the entire statement — no partial insert happens.
• INSERT triggers (Chapter 30) can fire automatically as a side effect, meaning a single INSERT might do more than what's visible in the statement itself — worth knowing when debugging unexpected extra behavior.

Key Takeaways

• INSERT INTO table (columns) VALUES (values) adds one new row.
• Naming columns explicitly is safer than relying on positional order.
• Constraint violations reject the whole INSERT, not just the offending value.