Provision
—— XP

Менять данные осторожно: INSERT, UPDATE, DELETE

теория~7 минут+180 XP

До сих пор мы только читали — это безопасно, сколько ни ошибайся. Три других слова SQL данные меняют: INSERT добавляет строки, UPDATE изменяет, DELETE удаляет. Они появились в языке тогда же, когда и SELECT, и устроены похоже: та же таблица, то же условие WHERE. Чего они не делают: не переспрашивают «вы уверены?» и не ведут корзину, из которой можно достать удалённое.

Данные новички теряют обычно одинаково: пишут UPDATE или DELETE, забывают WHERE и жмут Enter. Команда меняет не одну строку, а всю таблицу, и все, кто работает с базой, сразу видят результат. Защита от этого — транзакция: способ сделать изменение черновиком, посмотреть на результат и только потом решить, оставить его или выбросить. Как ею пользоваться перед любой правкой рабочей базы — главное, что ты унесёшь из урока.

Если ты знаешь, как работают транзакции — этот урок можно пролистать и сразу открыть практику «правка в транзакции». Сдалась с первого раза — значит, ничего нового здесь для тебя нет; не сдалась — вернись сюда за тем, на чём споткнулся.

INSERT: добавить строку

INSERT INTO называет таблицу и столбцы, VALUES — значения в том же порядке. Номер id указывать не нужно: его проставит база.

SQL
shop=# INSERT INTO customers (name, city) VALUES ('Егор', 'Самара');
INSERT 0 1

Ответ INSERT 0 1 значит «вставлена одна строка». Если пропустить обязательный столбец, база откажет с ошибкой null value in column "city" violates not-null constraint — это сработало NOT NULL, заданное при создании таблицы.

Добавлять почти безопасно: лишнюю строку видно и её легко удалить. С изменением хуже.

UPDATE: изменить

UPDATE называет таблицу, SET — что поменять, WHERE — в каких строках. Ответ сообщает, сколько строк задето.

SQL
shop=# UPDATE customers SET city = 'Казань' WHERE name = 'Егор';
UPDATE 1

Самая дорогая ошибка в этом разделе — UPDATE или DELETE без WHERE. Условия нет — значит, оно верно для каждой строки, и база честно меняет всю таблицу. Запрос UPDATE orders SET status = 'done'; ответит UPDATE 200000, и прошлые статусы двухсот тысяч заказов исчезнут.

Случается это не от незнания, а от руки: набрал начало, хотел дописать условие, пальцы нажали Enter. Поэтому у опытных людей есть привычка: писать WHERE раньше, чем SET. Сначала UPDATE orders WHERE id = 17, потом курсор назад и вставить середину. Недописанный запрос без SET база просто не примет.

С удалением та же история — и одна защита, которую база даёт сама.

DELETE: удалить

DELETE FROM называет таблицу, WHERE — какие строки убрать.

SQL
shop=# DELETE FROM customers WHERE name = 'Егор';
DELETE 1

Теперь попробуем удалить Анну, у которой сорок тысяч заказов:

SQL
shop=# DELETE FROM customers WHERE id = 1;
ERROR:  update or delete on table "customers" violates foreign key
constraint "orders_customer_id_fkey" on table "orders"
DETAIL:  Key (id)=(1) is still referenced from table "orders".

Это сработала связь REFERENCES, которую мы задали в таблице заказов. Она называется внешним ключом и не даёт оставить заказы, ссылающиеся на несуществующего покупателя. Хорошая база бережёт себя сама, но только там, где ей заранее объяснили правила, — на голый DELETE FROM orders; никакого запрета нет.

Против таких случаев есть один надёжный приём.

Транзакция: черновик с кнопкой «отменить»

Транзакция — это черновик для нескольких изменений сразу: они либо записываются все вместе, либо не записывается ни одно. Начинается черновик словом BEGIN. Всё, что ты сделаешь дальше, видно только тебе, пока ты не скажешь COMMIT — «записать окончательно» — или ROLLBACK — «выбросить всё с момента BEGIN».

  BEGIN ──► UPDATE ──► SELECT: проверить ──┬──► COMMIT    записано
                                          └──► ROLLBACK  как не было

Вот как выглядит ошибка, которую поймали вовремя:

SQL
shop=# BEGIN;
BEGIN
shop=*# DELETE FROM orders;
DELETE 200000
shop=*# ROLLBACK;
ROLLBACK
shop=# SELECT count(*) FROM orders;
 count
--------
 200000
(1 row)

Обрати внимание на звёздочку в приглашении shop=*#: так psql показывает, что ты внутри открытой транзакции. Пока она есть, ничего не записано.

Без BEGIN psql работает в режиме автофиксации: каждая команда сразу становится окончательной, будто после неё сам собой выполнился COMMIT. Отменять в этом режиме нечего, остаётся только восстанавливать из копии — об этом будет отдельный урок.

Порядок действий перед любой правкой

Из всего урока складывается короткий ритуал. Он занимает полминуты и выглядит паранойей ровно до первого случая, когда спасает день.

  1. BEGIN; — открыть черновик.
  2. SELECT count(*) с тем же WHERE, что будет в правке. Запомнить число.
  3. UPDATE или DELETE с этим WHERE. Сравнить число в ответе с запомненным.
  4. Совпало и выглядит правдой — COMMIT;. Не совпало — ROLLBACK; и разбираться.

У ритуала есть цена: пока транзакция открыта, изменённые строки заблокированы, и другое подключение, которое захочет их тронуть, будет ждать. Поэтому транзакцию не оставляют открытой, уходя на обед. Как выглядит такое зависание и как его найти, ты увидишь в миссии раздела.

И ещё одна привычка из той же серии: смотреть в рабочую базу под пользователем, у которого есть право только на чтение. Тогда случайный UPDATE упрётся в permission denied, а на запись переключаются осознанно.

Словарь урока

INSERT
Добавляет строки в таблицу. ↩ к тексту
UPDATE
Изменяет значения в строках, подходящих под WHERE. Без WHERE — во всех. ↩ к тексту
DELETE
Удаляет строки, подходящие под WHERE. Без WHERE — все. ↩ к тексту
Транзакция
Группа изменений между BEGIN и COMMIT, которая применяется целиком или отменяется ROLLBACK. ↩ к тексту
Автофиксация
Режим по умолчанию: каждая команда без BEGIN сразу записывается окончательно. ↩ к тексту
Внешний ключ
Связь между таблицами, которая не даёт сослаться на несуществующую строку.

Что спросят на собеседовании

  • Что такое транзакция и зачем она нужна?
    Группа операций, которая выполняется целиком или не выполняется вовсе. Даёт атомарность: перевод денег не может списать со счёта и не зачислить. В ручной работе — ещё и возможность отменить ошибку через ROLLBACK.
  • Как безопасно выполнить UPDATE в проде?
    Открыть транзакцию, посчитать строки тем же WHERE, выполнить UPDATE, сверить число, и только потом COMMIT. Заранее иметь свежую копию.
  • Чем опасна долго открытая транзакция?
    Она держит блокировки на изменённых строках, и другие запросы встают в очередь. Плюс мешает базе убирать старые версии строк.

Что дальше

В практике ты проделаешь ритуал руками: поправишь статусы заказов, откатишь, поправишь снова и зафиксируешь.

Проверь себя+180 XPдве попытки
1
UPDATE
Что сделает UPDATE orders SET status = 'done'; без WHERE?
2
Транзакция
Ты выполнил BEGIN, потом DELETE, и понял, что удалил лишнее. Что делать?
3
Автофиксация
Ты набрал в psql UPDATE без BEGIN. Когда изменение станет окончательным?
4
Привычки
Что проверяют перед UPDATE в рабочей базе?
5
DELETE
Почему база не дала удалить покупателя, у которого есть заказы?
ответь на все вопросы, чтобы проверить

Нашёл ошибку или непонятное место? Наведи на абзац — слева появится «+», нажми и напиши, что не так. Замечание увидит автор курса.Нашёл ошибку или непонятное место? Нажми «+» справа от заголовка раздела и напиши, что не так. Замечание увидит автор курса.