DailyRNG
Login
Basic

Adding rows safely

I added a row and then added the same row again, and the database let me, so I had two of everything. The lesson is that INSERT never checks, and a rule you add once can make it check for you.

What you will see

sqlite> Error: UNIQUE constraint failed: staff.id

About 2 min of reading

Try It

Add rows, then add a rule that makes the database protect the table for you.

1

Add one row the plain way.

INSERT INTO users (id, name) VALUES (4, "Edith");

Before you run it, say out loud what it will print. Then open the answer.

What it prints
sqlite>
2

Add two rows in one statement with comma-separated value groups.

INSERT INTO users (id, name) VALUES (5, "Linus"), (6, "Guido");

Before you run it, say out loud what it will print. Then open the answer.

What it prints
sqlite>
3

Count the rows. You started with three and added three, so six.

SELECT COUNT(*) FROM users;

Before you run it, say out loud what it will print. Then open the answer.

What it prints
count 6 sqlite>
4

Now draw a table where the id is a PRIMARY KEY, which means unique and required.

CREATE TABLE staff (id INTEGER PRIMARY KEY, role TEXT);

Before you run it, say out loud what it will print. Then open the answer.

What it prints
sqlite>
5

Insert a row into staff.

INSERT INTO staff (id, role) VALUES (10, "dev");

Before you run it, say out loud what it will print. Then open the answer.

What it prints
sqlite>
6

Try the same id again. The rule rejects it and the error names the rule you broke.

INSERT INTO staff (id, role) VALUES (10, "lead");

Before you run it, say out loud what it will print. Then open the answer.

What it prints
sqlite> Error: UNIQUE constraint failed: staff.id
The error is not a crash. The bad row was not added, the table is untouched, and you can type a corrected INSERT right away.

Data Flow

INSERT names a table and values, the database appends a row, and a column rule can reject a bad value before it lands.
1 INSERT names table and columns
↓
2 VALUES gives the data
↓
3 A row is appended
↓
4 A column rule can reject it
Why this works
INSERT is blind

INSERT does exactly what you say and nothing more. It does not look for a matching row, does not update an existing one, does not warn you about a repeat. If you run the same INSERT twice you get two rows. That is a feature, not a bug, but it surprises people the first time.

Make the table do the checking

When you create a table you can attach rules to a column:

  • PRIMARY KEY means unique and not blank, and usually makes it the main identifier.
  • NOT NULL means a value is required.
  • UNIQUE means no two rows share it.

The rule lives on the table, so every future INSERT is checked against it for free.

Examples

One row at a time

INSERT INTO users (id, name) VALUES (4, "Edith");

This appends one row. The column names in the first parentheses and the values in the second must line up in count and type. Skip a column and it gets a blank.

Several rows, one go

INSERT INTO users (id, name) VALUES (5, "Linus"), (6, "Guido");

Separate value groups with commas. One statement, two rows. Faster to type and the database does both or neither if something is wrong.

Refuse a duplicate

INSERT INTO users (id, name) VALUES (1, "Ada again");

By default this works and you now have two rows with id 1. To stop it, mark the column unique when you create the table. Then a repeat is rejected with an error.

Words this lesson uses

Each one is defined here on this site. Open the entry for the full definition and the other words it points at.

← Stitching two tables
Previous Lesson
Stitching two tables
Next Lesson
Changing one row
Changing one row →