Глава 3 из 28

3. Создание таблиц: ключи, ограничения, значения по умолчанию

Таблица — это не только колонки и типы. Ограничения (constraints) делают так, что в таблицу физически нельзя записать мусор: заказ без клиента, отрицательную цену, два пользователя с одним email. Приложение может ошибаться, но база обязана не пропустить некорректные данные. Эта глава про то, как это настроить.

Минимальная таблица и первичный ключ

Начнём с таблицы клиентов:

sql

CREATE TABLE customers (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    name       text NOT NULL,
    email      text NOT NULL UNIQUE,
    city       text,
    created_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO customers (name, email, city)
VALUES ('Анна', 'anna@example.com', 'Москва'),
       ('Борис', 'boris@example.com', NULL);

SELECT id, name, email, city FROM customers ORDER BY id;

код

 id | name  |       email       |  city
----+-------+-------------------+--------
  1 | Анна  | anna@example.com  | Москва
  2 | Борис | boris@example.com |

Разберём, что тут произошло.

PRIMARY KEY означает «уникально и не NULL» и создаёт индекс по колонке. У каждой таблицы должен быть первичный ключ: без него нельзя однозначно сослаться на строку.

GENERATED ALWAYS AS IDENTITY — автоматическая нумерация. Мы не указывали id, и база подставила 1 и 2. Это современная замена типа serial: она стандартна и аккуратнее с правами. Вариант ALWAYS не даст случайно вписать свой номер, а BY DEFAULT позволит.

NOT NULL запрещает пустое значение. Колонка city его не имеет, поэтому у Бориса города нет.

UNIQUE запрещает повторы. Два клиента с одним email не получатся.

DEFAULT now() подставляет значение, если его не указали. Время создания записи — классический случай.

Что происходит при нарушении

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

sql

INSERT INTO customers (name, email) VALUES ('Анна-2', 'anna@example.com');

код

ERROR:  duplicate key value violates unique constraint "customers_email_key"
DETAIL:  Key (email)=(anna@example.com) already exists.

sql

INSERT INTO customers (name, email) VALUES (NULL, 'x@example.com');

код

ERROR:  null value in column "name" of relation "customers" violates not-null constraint

Текст ошибки называет ограничение и значение, поэтому её можно обработать в приложении. Для уникальности есть код 23505, для NOT NULL — 23502. Ловить конкретные коды лучше, чем разбирать текст.

Две вещи про идентификатор. Если вставка отменилась из-за ошибки, номер из последовательности всё равно израсходован: в нумерации появятся пропуски. Это нормально, нумерация гарантирует уникальность, а не отсутствие дыр. А ещё ALWAYS не примет явный id:

sql

INSERT INTO customers (id, name, email) VALUES (100, 'Вера', 'vera@example.com');

код

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.

CHECK: правило для значений

CHECK проверяет произвольное условие для строки. Заведём таблицу заказов:

sql

CREATE TABLE orders (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers (id),
    status      text NOT NULL DEFAULT 'new'
                CHECK (status IN ('new', 'paid', 'shipped', 'cancelled')),
    amount      numeric(12, 2) NOT NULL CHECK (amount >= 0),
    created_at  timestamptz NOT NULL DEFAULT now()
);

INSERT INTO orders (customer_id, amount) VALUES (1, 1500.00), (1, 320.50), (2, 99.90);

SELECT id, customer_id, status, amount FROM orders ORDER BY id;

код

 id | customer_id | status | amount
----+-------------+--------+---------
  1 |           1 | new    | 1500.00
  2 |           1 | new    |  320.50
  3 |           2 | new    |   99.90

Статус мы не указали, и сработало значение по умолчанию new. Отрицательную сумму или статус с опечаткой база не пропустит:

sql

INSERT INTO orders (customer_id, status, amount) VALUES (1, 'payed', 100);

код

ERROR:  new row for relation "orders" violates check constraint "orders_status_check"

Ограничения CHECK проверяют только саму строку. Сравнить значение с другой таблицей оно не может: для этого есть внешние ключи.

FOREIGN KEY: связь между таблицами

Конструкция REFERENCES customers (id) — внешний ключ. Он гарантирует, что в orders.customer_id лежит id существующего клиента:

sql

INSERT INTO orders (customer_id, amount) VALUES (999, 10);

код

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(999) is not present in table "customers".

Внешний ключ работает и в обратную сторону: удалить клиента, у которого есть заказы, не получится.

sql

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".

Что делать с дочерними строками при удалении родителя, задают правилом ON DELETE:

Правило	Поведение
`NO ACTION` (по умолчанию)	ошибка, если остались дочерние строки
`RESTRICT`	то же, но проверка сразу, а не в конце транзакции
`CASCADE`	удалить и дочерние строки
`SET NULL`	обнулить ссылку (колонка должна допускать `NULL`)

CASCADE удобен для зависимых данных, например «позиции заказа», но опасен для остального: одно удаление может незаметно стереть много строк. Для заказов и платежей по умолчанию лучше оставить запрет: заказы клиента не должны исчезать молча.

Одна неочевидная деталь: PostgreSQL создаёт индекс для первичного ключа, но не для внешнего. Колонка orders.customer_id индекса не получила, и запрос «все заказы клиента» будет читать всю таблицу. Индекс на внешние ключи нужно создавать самим, подробнее в главе про индексы.

Составные ключи и уникальность по нескольким колонкам

Ограничение может охватывать несколько колонок. Например, в таблице позиций заказа товар не должен повторяться в рамках одного заказа:

sql

CREATE TABLE order_items (
    order_id   bigint NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
    product_id bigint NOT NULL,
    quantity   int NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (order_id, product_id)
);

INSERT INTO order_items VALUES (1, 10, 2), (1, 11, 1), (2, 10, 5);

SELECT * FROM order_items ORDER BY order_id, product_id;

Пара (order_id, product_id) уникальна, а сами значения могут повторяться по отдельности. Позиции при этом зависят от заказа, поэтому для них удобен ON DELETE CASCADE: удалили заказ, исчезли и его строки.

Как изменить таблицу потом

Структуру правят командой ALTER TABLE:

sql

ALTER TABLE customers ADD COLUMN phone text;
ALTER TABLE customers ADD CONSTRAINT customers_name_not_blank CHECK (length(trim(name)) > 0);

SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = current_schema() AND table_name = 'customers'
ORDER BY ordinal_position;

Добавить колонку без значения по умолчанию, а на современных версиях и с постоянным значением, быстро: таблица не переписывается. А вот ADD CONSTRAINT проверяет все существующие строки и на большой таблице надолго блокирует её. Блокировки разобраны в платной главе, там же способ добавить ограничение без остановки: сначала NOT VALID, потом VALIDATE CONSTRAINT.

Частые ошибки

Таблица без первичного ключа. Дубли не отсечь, на строку не сослаться, для логической репликации потребуется дополнительная настройка.
Правила только в приложении. Приложений бывает несколько, а ручные правки и скрипты миграций обходят код. Ограничения в базе защищают всех.
Забыть NOT NULL. Колонка по умолчанию допускает NULL, и позже в данных появятся «пустые» значения, которые ломают отчёты.
Повесить ON DELETE CASCADE на всё подряд. Одно удаление клиента сотрёт заказы, платежи и историю.
Не создать индекс на внешний ключ. Запросы по связи и удаление родителя будут сканировать дочернюю таблицу целиком.
Использовать serial в новом коде. Предпочтительна GENERATED ... AS IDENTITY.

Создание таблиц и ограничения разобраны на заданиях в курсе «SQL с нуля».

Откройте остальные главы

Ещё 20 глав: внутреннее устройство, оптимизация, администрирование. Покупка разовая, доступ навсегда.

илиОткрыть с подпиской Pro

НазадТипы данных: числа, текст, даты, boolean, uuid ДальшеЗапросы: SELECT, фильтры, сортировка, NULL