(495) 925-0049, ITShop интернет-магазин 229-0436, Учебный Центр 925-0049
  Главная страница Карта сайта Контакты
Поиск
Вход
Регистрация
Рассылки сайта
 
 
 
 
 

Ограничения (сonstraints) PostgreSQL: exclude, частичный unique, отложенные ограничения

Источник: habrahabr

Целостность данных легко нарушить. Бывает так, что в поле 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.

Ссылки по теме


 Распечатать »
 Правила публикации »
  Обсудить материал в конференции Дискуссии и обсуждения общего плана »
Написать редактору 
 Рекомендовать » Дата публикации: 04.10.2016 
 

Магазин программного обеспечения   WWW.ITSHOP.RU
NauDoc Enterprise 10 рабочих мест
TeeChart Pro VCL/FMX with source code single license
Microsoft Office 365 Профессиональный Плюс. Подписка на 1 рабочее место на 1 год
Avira Antivirus Pro - Business Edition, 5 ПК, 1 год
SAP Crystal Reports 2008 INTL WIN NUL License
 
Другие предложения...
 
Курсы обучения   WWW.ITSHOP.RU
 
Другие предложения...
 
Магазин сертификационных экзаменов   WWW.ITSHOP.RU
 
Другие предложения...
 
3D Принтеры | 3D Печать   WWW.ITSHOP.RU
 
Другие предложения...
 
Новости по теме
 
Рассылки Subscribe.ru
Информационные технологии: CASE, RAD, ERP, OLAP
Безопасность компьютерных сетей и защита информации
Программирование на Microsoft Access
CASE-технологии
OS Linux для начинающих. Новости + статьи + обзоры + ссылки
Реестр Windows. Секреты работы на компьютере
СУБД Oracle "с нуля"
 
Статьи по теме
 
Новинки каталога Download
 
Исходники
 
Документация
 
Обсуждения в форумах
Пишу программы на заказ профессионально (2310)
Пишу программы на заказ на языках Pascal (численные методы, списки, деревья, прерывания) под...
 
Надстройка "Поиск решения" MS Excel (6)
Помогите решить задачу с помощью " поиск решения" мука яйца ...
 
Ищу программиста для написания программы (22)
Ищу программиста ,владеющего Вижуал Бэйсик и программированием в Экселе, для написания...
 
Модульный метод создания сайтов (4)
Модульный метод создания (компоновки) сайтов на основе наборов типовых отлаженных функциональных...
 
Corel. Сохранение файла в старом формате. (27)
у меня есть горящий вопрос по Корелу и никто мне не может дать на него ответ. Я работаю в...
 
 
 



    
rambler's top100 Rambler's Top100