Целостность данных легко нарушить. Бывает так, что в поле price попадает значение 0 из-за ошибки в коде приложения (периодически всплывают новости, как в том или ином инет-магазине продавали товары по 0 долларов). Или бывает, что удалили юзера из таблицы, но какие-то данные о нем остались в других таблицах, и эти данные вылезли в каком-то интерфейсе.
PostgreSQL, как и любая другая СУБД, умеет делать некоторые проверки при вставке/изменении данных, и этим обязательно нужно уметь пользоваться. Давайте посмотрим, что мы можем проверять:
1. Кастомный подтип через ключевое слово DOMAIN
В PostgreSQL вы можете создать свой тип, основанный на каком-нибудь int или text с дополнительной проверкой каких-то вещей:
CREATE DOMAIN us_postal_code AS TEXT
CHECK(
VALUE ~ '^d{5}$'
OR VALUE ~ '^d{5}-d{4}$'
);
Мы создаем тип us_postal_code, в котором регулярками проверяем различные варианты его написания. Теперь никто не сможет туда по ошибке написать “улица Бармалеева”, там будет только индекс:
CREATE TABLE users (
id integer,
name text,
email text,
postal_code us_postal_code
) ;
Кроме того, это улучшает читабельность кода, так как тип сам поясняет, что в нем лежит, в отличие от безликих integer или text.
2. Check (особенно актуально для проверки jsonb и hstore)
Выше мы использовали us_postal_code использовали оператор CHECK. Точно такой же можно написать и в конструкции CREATE TABLE.
CREATE TABLE users (
id integer,
name text,
email text,
postal_code us_postal_code,
CHECK (length(name) >= 1 AND length(name) <= 300)
) ;
Или в таблице с товарами можно поставить check (price > 0), тогда вы не будете продавать ноуты по 0 рублей. Или можно написать хранимку и использовать check(superCheckFunction(price)), а в этой хранимке кучу логики проверять.
Кстати, тип varchar(100) — это тоже самое, что и тип text с дополнительным check по длине.
Надо понимать, что check происходит при каждом insert или update, поэтому, если в вашу таблицу идет 100500 записей в секунду, то check возможно делать не стоит.
Бывает важно обвешать чеками универсальные типы данных, такие как jsonb или hstore, потому что туда можно напихать что угодно. Можно проверять существование каких-то ключей в json или что их значение соответствует тому, что там должно быть.
3. Проверка на уникальность, как простая, так и частичная.
Простая проверка, что email у разных пользователей должен быть разный:
CREATE TABLE users (
id integer,
name text,
email text,
postal_code us_postal_code,
deleted boolean,
UNIQUE(email)
) ;
Однако иногда нужно проверять уникальность не по всей таблице, а только, например, у пользователей с определенным статусом.
Вместо простого UNIQUE вы можете добавить такой уникальный индекс:
CREATE UNIQUE INDEX users_unique_idx ON users(email) WHERE deleted = false;
Тогда уникальность email будет проверяться только у неудаленных юзеров. В where можно вставлять любые условия.
Надо еще отметить, что можно делать уникальные индексы по двум и более полям сразу, т.е. проверять уникальную комбинацию.
4. EXCLUDE
С помощью оператора EXCLUDE можно сделать еще один вид уникальности. Дело в том, что в посгресе множество типов данных, как встроенных, так и добавляемых через расширения. Например, есть тип данных ip4r, с его помощью можно хранить диапазон ip-адресов в одном поле.
И, допустим, надо в таблице хранить непересекающиеся диапазоны. Вообще, проверить, пересекаются ли два диапазона можно с помощью оператора &&, например SELECT ‘127.0.0.0/24’ && ‘127.0.0.1/32’ вернет true.
В итоге делаем просто:
CREATE TABLE ip_ranges (
ip_range ip4r,
EXCLUDE USING gist (ip_range WITH &&)
);
И тогда при вставке/апдейте postgres будет смотреть каждую строку, не пересекается ли она со вставляемой (т.е. не возвращает ли использование оператора && истину). Благодаря gist индексу эта проверка очень быстрая.
5. NOT NULL
Тут всё понятно, колонка не может принимать значение NULL. Зачастую (но необязательно) идет в связке с DEFAULT.
Например:
CREATE TABLE users (
id integer,
name text NOT NULL,
email text NOT NULL,
postal_code us_postal_code,
is_married BOOLEAN NOT NULL DEFAULT true,
UNIQUE(email)
) ;
При добавлении новой колонки с not null в существующую таблицу надо быть осторожным. Дело в том, что обычную колонку, где допустимо null, PostgreSQL добавляет мгновенно, даже если таблица очень большая, к примеру, десятки миллионов строк. Потому что ему не надо физически менять данные, лежащие на диске, null в postgres не занимают места. Однако если вы добавите колонку name text not null default ‘Вася’, то посгрес по факту полезет делать update каждой строки, и это может занять много времени, что может быть недопустимо в некоторых ситуациях.
Поэтому часто в огромные таблицы такие колонки добавляются в два этапа, т.е. сначала пачками заполняют данные новой колонки, и только потом ставят ей not null.
6. Primary key, т.е. первичный ключ
Раз это первичный ключ, то оно должен быть уникальным и не может быть пустым. В общем, в PostgreSQL PRIMARY KEY работает как комбинация UNIQUE и NOT NULL.
В других базах данных PRIMARY KEY делает еще и другие вещи, к примеру, если не ошибаюсь, в MySQL (Innodb), данные еще и автоматически кластеризуются вокруг PK для ускорения доступа по этому полю. (В посгресе, кстати, тоже так можно сделать, но вручную, командой CLUSTER. Но обычно в этом нет необходимости)
7. FOREIGN KEY
Например, у вас есть таблица
CREATE TABLE items (
id bigint PRIMARY KEY,
name varhar(1000),
status_id int
);
и таблица со статусами
CREATE TABLE status_dictionary (
id int PRIMARY KEY,
status_name varchar(100)
);
Вы можете указать базе, что колонка status_id соответствует Id из таблице status_dictionary. Например, так:
CREATE TABLE items (
id bigint PRIMARY KEY,
name varhar(1000),
status_id int REFERENCES status_dictionary(id)
);
Теперь вы сможете в status_id записать только null или Id из таблицы status_dictionaries, и больше ничего.
Также можно это делать по двум полям:
FOREIGN KEY (a,b) REFERENCES other_table(x,y);
При вставке опять же есть некоторый оверхед, потому что при каждой вставке СУБД вынуждена лочить довольно много вещей. Поэтому при (очень) интенсивной вставке возможно не стоит злоупотреблять использовнием Foreign key
8. DEFERRABLE
Если для производительности надо отложить проверку констрейнтов, констрейнты можно пометить ключевым словом DEFERRABLE.
Они бывают разных видов, например если вы сделаете UNIQUE(email) DEFERRABLE INITIALLY DEFERRED, то внутри транзакции можно написать
SET CONSTRAINTS ALL DEFERRED
И тогда все проверки будут Отложены и по факту произойдут только перед словом commit
Это сработает для UNIQUE, PRIMARY KEY и REFERENCES, но не сработает для NOT NULL и CHECK.
Автор: varanio
