Lesson 14 of 17 · Changing Data
INSERT
Add data to a database with SQL INSERT. Insert one row or many, understand automatic ids and learn the rules a table enforces on new data.
- 7 min read
- Beginner
- Reviewed
- How to add a new row to a table
- How to add several rows in one statement
- How ids are assigned automatically
- What happens when new data breaks a table's rules
From reading to writing
Every query so far has only read data. SELECT never changes anything. This module covers the statements that do: adding rows, changing them and removing them.
Adding one row
INSERT INTO adds a row. Name the table, list the columns, then give a value for each.
INSERT INTO courses (title, category, hours)
VALUES ('Git and GitHub', 'Programming', 7);
SELECT * FROM courses;The values are matched to the columns by position: the first value goes into the first column listed, and so on. Text goes in single quotes, numbers do not.
Where did the id come from?
The new course has an id of 8, yet the statement never mentioned one. The id column is the table's primary key, and the database fills it in automatically with the next free number. This is how ids are normally created: you never choose them yourself.
Adding several rows
Separate the rows with commas. One statement is quicker than many, and either all the rows are added or none are.
INSERT INTO students (name, country, age, joined)
VALUES ('Noah Fischer', 'Germany', 30, '2026-06-10'),
('Aiko Mori', 'Japan', 22, '2026-06-11'),
('Zainab Ali', 'Kenya', 27, '2026-06-12');
SELECT id, name, country
FROM students
WHERE id > 10;Leaving a column out
A column you do not mention gets no value, so it becomes NULL.
INSERT INTO students (name, age)
VALUES ('Leo Costa', 33);
SELECT id, name, country, age, joined
FROM students
WHERE name = 'Leo Costa';Rules that protect the data
A table can refuse data that breaks its rules. The name column of students is marked as required, so a student without a name is rejected.
INSERT INTO students (country, age)
VALUES ('Kenya', 30);The statement fails with NOT NULL constraint failed: students.name, and nothing is added.
Primary keys must be unique, so reusing an existing id is also refused.
INSERT INTO courses (id, title, category, hours) VALUES (1, 'Another Course', 'Programming', 5);
This time the message is UNIQUE constraint failed: courses.id. These rules are called constraints, and you will set them up yourself in the lesson on creating tables.
Adding a row that points to other rows
To enrol a student in a course, add a row to enrollments with the two foreign keys. Here student 11, who had no courses, starts course 2.
INSERT INTO enrollments (student_id, course_id, progress) VALUES (11, 2, 0); SELECT s.name, c.title, e.progress, e.score FROM enrollments AS e JOIN students AS s ON s.id = e.student_id JOIN courses AS c ON c.id = e.course_id WHERE s.id = 11;
No score was given, so it is NULL, which is right for a course that has just begun.
Common mistakes
Add the course Git and GitHub to the Programming category, with a length of 7 hours. The SELECT already in the editor will show it once your INSERT works.
-- Add a new course: -- title: Git and GitHub, category: Programming, hours: 7 -- Write your INSERT above the SELECT. Do not change the SELECT. SELECT title, hours FROM courses WHERE category = 'Programming' ORDER BY id;
You insert a row but leave out a column that has no default value. What is stored in that column?
Summary
INSERT INTO table (columns) VALUES (values);adds a row.- Values are matched to columns by position.
- Separate several rows with commas to add them in one statement.
- The
idis normally assigned automatically. - Columns you leave out become
NULL, and constraints reject invalid rows.
Next: changing and removing existing rows.
Finished reading? Tick it off to track your progress.