Constraints в PostgreSQL, или о том, как попытаться спокойно жить
Данный материал был создан на основе одноимённого доклада на PGConf.Online, вошедшего в число самых популярных выступлений конференции. Поскольку тема ограничений по-прежнему сохраняет свою актуальность, а смотреть видео с мероприятий любят не все, появилась эта статья.
Концепция “тупого хранилища”
В последние годы разработчики ПО всё чаще утверждают, что база в их проекте “всего лишь тупое хранилище, и поэтому никакой логики в ней нет”. Откуда такой подход? Обычно он объясняется сложностями миграции, развёртывания, неудобствами при работе с системами контроля исходного кода. Не стоит списывать со счетов и простую человеческую лень: раз всё и так нормально, зачем связываться с логикой в СУБД? Создали таблицы (или, ещё лучше, пусть ORM их создаст!), и всё отлично.
NoSQL для документов
Случай с NoSQL ещё проще – не надо ничего создавать, контролировать и напрягать мозги, всё уже автоматизировано, оно само работает. Этого вполне достаточно, если из базы нужно просто доставать документы по идентификатору, но если требуется решать задачи посложнее, то всё-таки выбирают SQL СУБД. Их использование, однако, ограничивается созданием таблиц и индексов, логика на стороне СУБД и в этом случае видится избыточной.
СУБД: не только технология, но и бизнес-инструмент
Такой подход является очень распространённым (люди вообще ленивы!). Тем не менее, крайне наивно дистанцироваться от хороших возможностей только из-за нежелания заморачиваться и приобретать новые навыки. СУБД – это очень изощрённая система хранения (чтобы понять это, достаточно почитать про уровни изоляции или процедуры резервного копирования). СУБД помогает синхронизировать бизнес-процессы и избежать реальных убытков, иногда в очень крупном размере.
Ecommerce: проблемы в системах учёта
Конечно, если у вас на складе товар в одном экземпляре, а вы его продали сразу двум покупателям, ваш бизнес от этого не сильно пострадает. В самом худшем случае вы из-за этой ситуации потеряете одного клиента-физлицо. Но что будет, если случатся более серьёзные неприятности? Осенью 2020 года я слышал о скандале внутри крупного российского онлайн-ритейлера. Тогда на складе обнаружили целые залежи товаров, ожидающих отправки покупателям ещё с весны. Виноваты, как всегда, были все сразу и никто конкретно.
“Сюрпризы” в коде
Концепция “СУБД – тупое хранилище” не может предотвратить такие случаи. Более того, она создаёт для них предпосылки. Это всего лишь вопрос времени, когда случится подобное. Даже лучший в мире разработчик рано или поздно совершит ошибку. Проект может существовать с этой ошибкой годами, до момента, пока не будут созданы условия, в которых она проявится. Тушить такой внезапный пожар всегда проблематично. Необходимо иметь в виду, что даже давно проверенный старый код однажды может преподнести неприятные сюрпризы.
Почему “умная” база лучше?
Как же уберечься от подобного рода неприятностей или хотя бы уменьшить вероятность их возникновения? Стоит вернуться к логике в СУБД, сделать её “умным” хранилищем. С помощью ограничений (constraints) можно сделать так, чтобы СУБД не допускала перевод себя в явно некорректное состояние. Конечно, определить все некорректные состояния раз и навсегда вряд ли получится. Однако, можно хотя бы составить список условий, в которых база точно никогда не должна находиться.
Когда ограничения стоят денег
Однажды знакомый разработчик ошибся при расчёте обменного курса криптовалют и отправил нескольким получателям по триста тысяч долларов каждому вместо ожидаемых трёхсот. Это могла бы предотвратить обычная нудная проверка, если бы она была. “Для такой-то пары значение отправленной суммы должно быть не больше 1/5 от значения полученной”, — если бы такое ограничение было вовремя установлено, можно было бы сберечь время и нервы:
check(case when in_ticker='BTC' and out_ticker='ETH' then out_amt/in_amt>5 else true end)Намного приятнее и спокойнее разбираться, почему платёж не ушёл, чем выяснять, почему он ушёл не туда или с неправильной суммой.
Дело не только в производительности
Таких жизненных историй я знаю очень много, поэтому стандартные возражения против ограничений целостности кажутся мне нелепыми. Естественно, ограничения снижают производительность, зато никому не придется продавать почку, если что-то пойдет не так. (Вряд ли кому-то захочется иметь дело с взысканием миллионного ущерба на работе.) Знаю, что многие молодые разработчики искренне верят в надежность своего кода. C годами это проходит, но зачем же ждать так долго?
Двойная защита для любых ценностей
Мой опыт подсказывает, что в реальном приложении, работающем с деньгами или другими материальными ценностями, любая проверка должна дублироваться. При этом желательно, чтобы код для каждой из проверок писали разные люди, которые не знакомы друг с другом и думают по-разному. Если вернуться к вышеупомянутому примеру с криптовалютами, одна проверка должна быть для грубой оценки – “не более 1/5”, а вторая – проверять, чтобы вычисленный курс не отличался от реального более чем на 2%:
(select abs(1-(select (out_amt/in_amt)/r.rate from rates r where r.ask_ticker='TCKR1' and r.bid_ticker='TCKR2'))<0.02)Зачем дублировать проверки? Рано или поздно какая-то из этих проверок сломается, и в отсутствие “запасной” проверки придётся работать без ограничений, а это чревато убытками.
Паранойя или управление рисками?
Расскажу ещё об одном случае из моей практики. Мне нужно было убедиться, что заказы не размещаются повторно. Для этого я создал уникальный индекс по идентификатору товара плюс предусмотрел простенькую проверку того, не был ли такой же товар добавлен в таблицу ранее. Казалось бы, паранойя на грани сумасшествия: зачем проверять то, что и так проверяется, причем не строго корректным образом (корректная реализация проверки на уникальность хоть и несложна, но, тем не менее, немного нетривиальна)? Тем не менее, в процессе очередной реорганизации уникальный индекс по недосмотру был заменен на обычный. Двойные продажи товаров начались бы, если бы не резервная проверка. Замена индекса обнаружилась только дней десять спустя, тем не менее, проблемных ситуаций не возникло. Так что у паранойи тоже есть практичная сторона. Конечно, такие дублирующиеся проверки подходят не для всех случаев. Но если нарушение бизнес-процессов грозит заметными потерями, благоразумно иметь две проверки или даже больше.
В наиболее критичных ситуациях отдельные разработчики рекомендуют ставить не менее трех серверов и выполнять операцию только если минимум два из них приходят к консенсусу (замечание в сторону: именно такое поведение автоматически получается в случае использования блокчейна).
Почему гибкость не всегда хороша
Ещё одно распространённое возражение против ограничений – отсутствие гибкости. Разумеется, база данных станет негибкой! Но при применении ограничений в этом и состоит наша цель. Так, например, при описании таблицы с примыкающими диапазонами дат (“с -infinty по 2020-01-01, с 2020-01-01 по 2020-06-01, с 2020-06-01 по 2021-01-01, с 2021-01-01 по infinity”) вставить в середину отдельный диапазон не так-то просто. Для этого потребуются либо отложенные (deferrable) проверки и не просто вставка нужной строки, но и изменение соседних, либо придётся обновить таблицу целиком.
create constraint trigger check_daterange_consistency_iu after insert or update on fees deferrable initially deferred for each row execute procedure check_fees_table_consistency(); create or replace function wb.check_fees_table_consistency() returns trigger as $code$ begin perform pg_advisory_xact_lock(hashtext('fees'), hash_record(row(new.period_start, new.period_end))); if new.period_start<>'-infinity' and not exists(select * from fees f where f.period_end=new.period_start) then raise sqlstate '23Q01' using message=format('Invalid period_start:%s',new); end if; if new.period_end<>'infinity' and not exists(select * from fees f where f.period_start=new.period_end) then raise sqlstate '23Q01' using message=format('Invalid period_start:%s',new); end if; if exists(select * from fees f where (f.period_start,f.period_end)overlaps(new.period_start, new.period_end) and f.period_start<>new.period_start and f.period_end<>new.period_end ) then raise sqlstate '23Q01' using message=format('Invalid date range:%s',new); end if; end $code$ language plpgsqlНо ведь именно это и является целью ограничений – ни в коем случае не допустить непримыкающих диапазонов дат. Нам необходимо гарантировать, что диапазоны, во-первых, примыкают к друг другу и, во-вторых, не перекрываются.
В реальной жизни настолько жесткие ограничения встречаются не столь часто, как хотелось бы. Если бы большее число компаний и организаций пользовались возможностями СУБД в полном объёме, мы реже слышали бы о гигантских убытках и недовольных пользователях.
Осиротевшие строки и неопределённость
Какова же стандартная практика? Обычно у каждой таблицы есть первичный ключ, хотя встречаются – и даже слишком часто – таблицы и без него. Реже, но всё же довольно часто применяют ограничение not null. Что касается внешних ключей (foreign keys), вполне можно наблюдать базы без них. А если в базе нет внешних ключей, и она уже достаточно стара и велика, есть 100% вероятности, что в её дочерних таблицах есть осиротевшие строки. Это плохо само по себе, плюс закономерно предположить, что если родитель для одной строки потерян, то и для другой он может быть указан некорректно.
“Экзотические” ограничения и ecommerce
Некоторые ограничения практически не встречаются. Например, EXCLUDE позволяет не допускать перекрывающихся диапазонов. Проверка JSON или XML на соответствие схеме также довольно экзотична. Совсем редки проверки в триггерах, хотя последние предназначены в том числе и для нестандартных проверок. Так, стандартными средствами СУБД невозможно обеспечить ограничение целостности вроде “каждый заказчик может иметь не более трех неоплаченных заказов” или “сумма неоплаченных заказов не должна превышать определенного значения”. Для реализации этого ограничения сначала потребуется написать триггер на добавление либо обновление удаление строки в таблице “Заказы”. Потом в нём обязательно нужно использовать блокировку, чтобы предотвратить обновление строки пользователя в транзакции, либо придётся использовать advisory-блокировку, используя хеш от идентификатора пользователя в качестве ключа блокировки. (Пример можно видеть выше в тексте триггерной функции:
pg_advisory_xact_lock(hashtext('fees'), hash_record(row(new.period_start, new.period_end)));Использование advisory-блокировки является отступлением от стандарта, но при большом количестве операций модификации она даёт выигрыш по производительности.
Триггеры и финтех
Не получится обойтись без триггеров и в случае проверки значения. Например, в России рублевый счет коммерческой нефинансовой организации в банке обязательно должен начинаться на 40702810. Проверку можно описать как CHECK, но в более сложных случаях придется обратиться к таблице-справочнику (40701 – финансовые организации, 40703 – некоммерческие, 408 – частные лица и т.п.). Правда, подобный триггер обычно имеет достаточно простой вид:
if not exists(select * from account_chart ac where ac.prefix=substring(new.account from 1 for 5)) then raise sqlstate ‘23Q03’ using message=’Invalid account for …’; end if;ASSERTION: круто, но не реализовано
В стандарте SQL описано такое полезное ограничение уровня БД, как ASSERTION. Будь оно реализовано, оно позволяло бы делать замечательные вещи. Так, ограничение на количество неоплаченных заказов было бы совершенно тривиальным. Ограничения по суммам продаж, по датам отгрузки тоже не представляли бы никакой проблемы. К сожалению, это ограничение не реализовано в Postgres. В оправдание Postgres можно сказать, что оно вообще не реализовано ни в одной из популярных СУБД. Честно говоря, не очень понятно, как его вообще можно эффективно реализовать.
Зачем писать триггеры?
Таким образом, если требуются достаточно нестандартные ограничения, придётся создавать триггеры или регулярно запускать скрипты проверки. (Это справедливо для ситуаций вида “не больше трех неоплаченных заказов”, “оплаченный заказ не может оставаться неотгруженным более суток” и им подобных.) Почему требуются какие-то дополнительные скрипты? Потому что со временем вполне корректные состояния могут оказываться некорректными. Например, товар, предназначенный для отправки, не может находиться на складе позднее какой-то даты:
for r in select * from goods g where g.shipment_date is null and g.received_date>current_date-1 loop perform send_notification_to_manager_on_stalled_delivery(r); end loop;Так как штатных триггеров по времени в СУБД не предусмотрено, придётся эмулировать их самостоятельно. Можно даже создать целый фреймворк для этого, но это уже тема другого разговора.
Основные тезисы
Какие основные тезисы этой статьи стоит запомнить? Я бы остановился на нескольких:
- База данных – это не “тупое хранилище”.
- База данных не должна допускать явно некорректные операции.
- База данных должна обнаруживать некорректные состояния.
Вывод
Понятно, что реализация подхода “база данных – умное хранилище” требует дополнительных усилий. Тем не менее, без этих усилий обойтись не удастся, если данные в СУБД являются частью сколько-нибудь серьезного бизнеса. Даже если проверки и другие ограничения не решат всех проблем, они позволят обнаруживать их раньше. А время – деньги.
Constraint postgresql что это
При определении таблиц и их столбцов в SQL мы можем использовать ряд атрибутов, которые накладывают определенные ограничения. Рассмотрим эти атрибуты.
PRIMARY KEY
С помощью выражения PRIMARY KEY столбец можно сделать первичным ключом.
CREATE TABLE Customers ( Id SERIAL PRIMARY KEY, FirstName CHARACTER VARYING(30), LastName CHARACTER VARYING(30), Email CHARACTER VARYING(30), Age INTEGER )
Первичный ключ уникально идентифицирует строку в таблице. В качестве первичного ключа необязательно должны выступать столбцы с типом SERIAL , они могут представлять любой другой тип.
Установка первичного ключа на уровне таблицы:
CREATE TABLE Customers ( Id SERIAL, FirstName CHARACTER VARYING(30), LastName CHARACTER VARYING(30), Email CHARACTER VARYING(30), Age INTEGER, PRIMARY KEY(Id) );
Первичный ключ может быть составным (compound key). Такой ключ может потребоваться, если у нас сразу два столбца должны уникально идентифицировать строку в таблице. Например:
CREATE TABLE OrderLines ( OrderId INTEGER, ProductId INTEGER, Quantity INTEGER, Price MONEY, PRIMARY KEY(OrderId, ProductId) );
Здесь поля OrderId и ProductId вместе выступают как составной первичный ключ. То есть в таблице OrderLines не может быть двух строк, где для обоих из этих полей одновременно были бы одни и те же значения.
UNIQUE
Если мы хотим, чтобы столбец имел только уникальные значения, то для него можно определить атрибут UNIQUE .
CREATE TABLE Customers ( Id SERIAL PRIMARY KEY, FirstName CHARACTER VARYING(20), LastName CHARACTER VARYING(20), Email CHARACTER VARYING(30) UNIQUE, Phone CHARACTER VARYING(30) UNIQUE, Age INTEGER );
В данном случае столбцы, которые представляют электронный адрес и телефон, будут иметь уникальные значения. И мы не сможем добавить в таблицу две строки, у которых значения для этих столбцов будет совпадать.
Также мы можем определить этот атрибут на уровне таблицы:
CREATE TABLE Customers ( Id SERIAL PRIMARY KEY, FirstName CHARACTER VARYING(20), LastName CHARACTER VARYING(20), Email CHARACTER VARYING(30), Phone CHARACTER VARYING(30), Age INTEGER, UNIQUE(Email, Phone) );
CREATE TABLE Customers ( Id SERIAL PRIMARY KEY, FirstName CHARACTER VARYING(20), LastName CHARACTER VARYING(20), Email CHARACTER VARYING(30), Phone CHARACTER VARYING(30), Age INTEGER, UNIQUE(Email), UNIQUE(Phone) );
NULL и NOT NULL
Чтобы указать, может ли столбец принимать значение NULL, при определении столбца ему можно задать атрибут NULL или NOT NULL . Если этот атрибут явным образом не будет использован, то по умолчанию столбец будет допускать значение NULL. Исключением является тот случай, когда столбец выступает в роли первичного ключа - в этом случае по умолчанию столбец имеет значение NOT NULL.
CREATE TABLE Customers ( Id SERIAL PRIMARY KEY, FirstName CHARACTER VARYING(20) NOT NULL, LastName CHARACTER VARYING(20) NOT NULL, Age INTEGER );
DEFAULT
Атрибут DEFAULT определяет значение по умолчанию для столбца. Если при добавлении данных для столбца не будет предусмотрено значение, то для него будет использоваться значение по умолчанию.
CREATE TABLE Customers ( Id SERIAL PRIMARY KEY, FirstName CHARACTER VARYING(20), LastName CHARACTER VARYING(20), Age INTEGER DEFAULT 18 );
Здесь для столбца Age предусмотрено значение по умолчанию 18.
CHECK
Ключевое слово CHECK задает ограничение для диапазона значений, которые могут храниться в столбце. Для этого после слова CHECK указывается в скобках условие, которому должен соответствовать столбец или несколько столбцов. Например, возраст клиентов не может быть меньше 0 или больше 100:
CREATE TABLE Customers ( Id SERIAL PRIMARY KEY, FirstName CHARACTER VARYING(20), LastName CHARACTER VARYING(20), Age INTEGER DEFAULT 18 CHECK(Age >0 AND Age < 100), Email CHARACTER VARYING(30) UNIQUE CHECK(Email !=''), Phone CHARACTER VARYING(20) UNIQUE CHECK(Phone !='') );
Здесь также указывается, что столбцы Email и Phone не могут иметь пустую строку в качестве значения (пустая строка не эквивалентна значению NULL).
Для соединения условий используется ключевое слово AND . Условия можно задать в виде операций сравнения больше (>), меньше (<), не равно (!=).
Также с помощью CHECK можно создать ограничение в целом для таблицы:
CREATE TABLE Customers ( Id SERIAL PRIMARY KEY, Age INTEGER DEFAULT 18, FirstName CHARACTER VARYING(20), LastName CHARACTER VARYING(20), Email CHARACTER VARYING(30) UNIQUE, Phone CHARACTER VARYING(20) UNIQUE, CHECK((Age >0 AND Age<100) AND (Email !='') AND (Phone !='')) );
Оператор CONSTRAINT. Установка имени ограничений.
С помощью ключевого слова CONSTRAINT можно задать имя для ограничений. В качестве ограничений могут использоваться PRIMARY KEY, UNIQUE, CHECK.
Имена ограничений можно задать на уровне столбцов. Они указываются после CONSTRAINT перед атрибутами:
CREATE TABLE Customers ( Id SERIAL CONSTRAINT customer_Id PRIMARY KEY, Age INTEGER CONSTRAINT customers_age_check CHECK(Age >0 AND Age < 100), FirstName CHARACTER VARYING(20) NOT NULL, LastName CHARACTER VARYING(20) NOT NULL, Email CHARACTER VARYING(30) CONSTRAINT customers_email_key UNIQUE, Phone CHARACTER VARYING(20) CONSTRAINT customers_phone_key UNIQUE );
В принципе необязательно задавать имена ограничений, при установке соответствующих атрибутов SQL Server автоматически определяет их имена. Но, зная имя ограничения, мы можем к нему обращаться, например, для его удаления.
И также можно задать все имена ограничений через атрибуты таблицы:
CREATE TABLE Customers ( Id SERIAL, Age INTEGER, FirstName CHARACTER VARYING(20) NOT NULL, LastName CHARACTER VARYING(20) NOT NULL, Email CHARACTER VARYING(30), Phone CHARACTER VARYING(20), CONSTRAINT customer_Id PRIMARY KEY(Id), CONSTRAINT customers_age_check CHECK(Age >0 AND Age < 100), CONSTRAINT customers_email_key UNIQUE(Email), CONSTRAINT customers_phone_key UNIQUE(Phone) );
Вне зависимости от того, используется оператор CONSTRAINT для создания ограничений или нет (в этом случае при установке ограничений PostgreSQL сам дает им имена), мы можем просмотреть все ограничения в pgAdmin в узле базы данных в подузле :
Constraint postgresql что это
Типы данных сами по себе ограничивают множество данных, которые можно сохранить в таблице. Однако для многих приложений такие ограничения слишком грубые. Например, столбец, содержащий цену продукта, должен, вероятно, принимать только положительные значения. Но такого стандартного типа данных нет. Возможно, вы также захотите ограничить данные столбца по отношению к другим столбцам или строкам. Например, в таблице с информацией о товаре должна быть только одна строка с определённым кодом товара.
Для решения подобных задач SQL позволяет вам определять ограничения для столбцов и таблиц. Ограничения дают вам возможность управлять данными в таблицах так, как вы захотите. Если пользователь попытается сохранить в столбце значение, нарушающее ограничения, возникнет ошибка. Ограничения будут действовать, даже если это значение по умолчанию.
5.3.1. Ограничения-проверки
Ограничение-проверка — наиболее общий тип ограничений. В его определении вы можете указать, что значение данного столбца должно удовлетворять логическому выражению (проверке истинности). Например, цену товара можно ограничить положительными значениями так:
CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price > 0) );
Как вы видите, ограничение определяется после типа данных, как и значение по умолчанию. Значения по умолчанию и ограничения могут указываться в любом порядке. Ограничение-проверка состоит из ключевого слова CHECK , за которым идёт выражение в скобках. Это выражение должно включать столбец, для которого задаётся ограничение, иначе оно не имеет большого смысла.
Вы можете также присвоить ограничению отдельное имя. Это улучшит сообщения об ошибках и позволит вам ссылаться на это ограничение, когда вам понадобится изменить его. Сделать это можно так:
CREATE TABLE products ( product_no integer, name text, price numeric CONSTRAINT positive_price CHECK (price > 0) );
То есть, чтобы создать именованное ограничение, напишите ключевое слово CONSTRAINT , а за ним идентификатор и собственно определение ограничения. (Если вы не определите имя ограничения таким образом, система выберет для него имя за вас.)
Ограничение-проверка может также ссылаться на несколько столбцов. Например, если вы храните обычную цену и цену со скидкой, так вы можете гарантировать, что цена со скидкой будет всегда меньше обычной:
CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price > 0), discounted_price numeric CHECK (discounted_price > 0), CHECK (price > discounted_price) );
Первые два ограничения определяются похожим образом, но для третьего используется новый синтаксис. Оно не связано с определённым столбцом, а представлено отдельным элементом в списке. Определения столбцов и такие определения ограничений можно переставлять в произвольном порядке.
Про первые два ограничения можно сказать, что это ограничения столбцов, тогда как третье является ограничением таблицы, так как оно написано отдельно от определений столбцов. Ограничения столбцов также можно записать в виде ограничений таблицы, тогда как обратное не всегда возможно, так как подразумевается, что ограничение столбца ссылается только на связанный столбец. (Хотя Postgres Pro этого не требует, но для совместимости с другими СУБД лучше следовать это правилу.) Ранее приведённый пример можно переписать и так:
CREATE TABLE products ( product_no integer, name text, price numeric, CHECK (price > 0), discounted_price numeric, CHECK (discounted_price > 0), CHECK (price > discounted_price) );
CREATE TABLE products ( product_no integer, name text, price numeric CHECK (price > 0), discounted_price numeric, CHECK (discounted_price > 0 AND price > discounted_price) );
Ограничениям таблицы можно присваивать имена так же, как и ограничениям столбцов:
CREATE TABLE products ( product_no integer, name text, price numeric, CHECK (price > 0), discounted_price numeric, CHECK (discounted_price > 0), CONSTRAINT valid_discount CHECK (price > discounted_price) );
Следует заметить, что ограничение-проверка удовлетворяется, если выражение принимает значение true или NULL. Так как результатом многих выражений с операндами NULL будет значение NULL, такие ограничения не будут препятствовать записи NULL в связанные столбцы. Чтобы гарантировать, что столбец не содержит значения NULL, можно использовать ограничение NOT NULL, описанное в следующем разделе.
5.3.2. Ограничения NOT NULL
Ограничение NOT NULL просто указывает, что столбцу нельзя присваивать значение NULL. Пример синтаксиса:
CREATE TABLE products ( product_no integer NOT NULL, name text NOT NULL, price numeric );
Ограничение NOT NULL всегда записывается как ограничение столбца и функционально эквивалентно ограничению CHECK ( имя_столбца IS NOT NULL) , но в Postgres Pro явное ограничение NOT NULL работает более эффективно. Хотя у такой записи есть недостаток — назначить имя таким ограничениям нельзя.
Естественно, для столбца можно определить больше одного ограничения. Для этого их нужно просто указать одно за другим:
CREATE TABLE products ( product_no integer NOT NULL, name text NOT NULL, price numeric NOT NULL CHECK (price > 0) );
Порядок здесь не имеет значения, он не обязательно соответствует порядку проверки ограничений.
Для ограничения NOT NULL есть и обратное: ограничение NULL . Оно не означает, что столбец должен иметь только значение NULL, что конечно было бы бессмысленно. Суть же его в простом указании, что столбец может иметь значение NULL (это поведение по умолчанию). Ограничение NULL отсутствует в стандарте SQL и использовать его в переносимых приложениях не следует. (Оно было добавлено в Postgres Pro только для совместимости с некоторыми другими СУБД.) Однако некоторые пользователи любят его использовать, так как оно позволяет легко переключать ограничения в скрипте. Например, вы можете начать с:
CREATE TABLE products ( product_no integer NULL, name text NULL, price numeric NULL );
и затем вставить ключевое слово NOT , где потребуется.
Подсказка
При проектировании баз данных чаще всего большинство столбцов должны быть помечены как NOT NULL.
5.3.3. Ограничения уникальности
Ограничения уникальности гарантируют, что данные в определённом столбце или группе столбцов уникальны среди всех строк таблицы. Ограничение записывается так:
CREATE TABLE products ( product_no integer UNIQUE, name text, price numeric );
в виде ограничения столбца и так:
CREATE TABLE products ( product_no integer, name text, price numeric, UNIQUE (product_no) );
в виде ограничения таблицы.
Чтобы определить ограничение уникальности для группы столбцов, запишите его в виде ограничения таблицы, перечислив имена столбцов через запятую:
CREATE TABLE example ( a integer, b integer, c integer, UNIQUE (a, c) );
Такое ограничение указывает, что сочетание значений перечисленных столбцов должно быть уникально во всей таблице, тогда как значения каждого столбца по отдельности не должны быть (и обычно не будут) уникальными.
Вы можете назначить уникальному ограничению имя обычным образом:
CREATE TABLE products ( product_no integer CONSTRAINT must_be_different UNIQUE, name text, price numeric );
При добавлении ограничения уникальности будет автоматически создан уникальный индекс-B-дерево для столбца или группы столбцов, перечисленных в ограничении. Условие уникальности, распространяющееся только на некоторые строки, нельзя записать в виде ограничения уникальности, однако такое условие можно установить, создав уникальный частичный индекс.
Вообще говоря, ограничение уникальности нарушается, если в таблице оказывается несколько строк, у которых совпадают значения всех столбцов, включённых в ограничение. Однако два значения NULL при сравнении никогда не считаются равными. Это означает, что даже при наличии ограничения уникальности в таблице можно сохранить строки с дублирующимися значениями, если они содержат NULL в одном или нескольких столбцах ограничения. Это поведение соответствует стандарту SQL, но мы слышали о СУБД, которые ведут себя по-другому. Имейте в виду эту особенность, разрабатывая переносимые приложения.
5.3.4. Первичные ключи
Ограничение первичного ключа означает, что образующий его столбец или группа столбцов может быть уникальным идентификатором строк в таблице. Для этого требуется, чтобы значения были одновременно уникальными и отличными от NULL. Таким образом, таблицы со следующими двумя определениями будут принимать одинаковые данные:
CREATE TABLE products ( product_no integer UNIQUE NOT NULL, name text, price numeric );
CREATE TABLE products ( product_no integer PRIMARY KEY, name text, price numeric );
Первичные ключи могут включать несколько столбцов; синтаксис похож на запись ограничений уникальности:
CREATE TABLE example ( a integer, b integer, c integer, PRIMARY KEY (a, c) );
При добавлении первичного ключа автоматически создаётся уникальный индекс-B-дерево для столбца или группы столбцов, перечисленных в первичном ключе, и данные столбцы помечаются как NOT NULL .
Таблица может иметь максимум один первичный ключ. (Ограничений уникальности и ограничений NOT NULL, которые функционально почти равнозначны первичным ключам, может быть сколько угодно, но назначить ограничением первичного ключа можно только одно.) Теория реляционных баз данных говорит, что первичный ключ должен быть в каждой таблице. В Postgres Pro такого жёсткого требования нет, но обычно лучше ему следовать.
Первичные ключи полезны и для документирования, и для клиентских приложений. Например, графическому приложению с возможностями редактирования содержимого таблицы, вероятно, потребуется знать первичный ключ таблицы, чтобы однозначно идентифицировать её строки. Первичные ключи находят и другое применение в СУБД; в частности, первичный ключ в таблице определяет целевые столбцы по умолчанию для сторонних ключей, ссылающихся на эту таблицу.
5.3.5. Внешние ключи
Ограничение внешнего ключа указывает, что значения столбца (или группы столбцов) должны соответствовать значениям в некоторой строке другой таблицы. Это называется ссылочной целостностью двух связанных таблиц.
Пусть у вас уже есть таблица продуктов, которую мы неоднократно использовали ранее:
CREATE TABLE products ( product_no integer PRIMARY KEY, name text, price numeric );
Давайте предположим, что у вас есть таблица с заказами этих продуктов. Мы хотим, чтобы в таблице заказов содержались только заказы действительно существующих продуктов. Поэтому мы определим в ней ограничение внешнего ключа, ссылающееся на таблицу продуктов:
CREATE TABLE orders ( order_id integer PRIMARY KEY, product_no integer REFERENCES products (product_no), quantity integer );
С таким ограничением создать заказ со значением product_no , отсутствующим в таблице products (и не равным NULL), будет невозможно.
В такой схеме таблицу orders называют подчинённой таблицей, а products — главной. Соответственно, столбцы называют так же подчинённым и главным (или ссылающимся и целевым).
Предыдущую команду можно сократить так:
CREATE TABLE orders ( order_id integer PRIMARY KEY, product_no integer REFERENCES products, quantity integer );
то есть, если опустить список столбцов, внешний ключ будет неявно связан с первичным ключом главной таблицы.
Внешний ключ также может ссылаться на группу столбцов. В этом случае его нужно записать в виде обычного ограничения таблицы. Например:
CREATE TABLE t1 ( a integer PRIMARY KEY, b integer, c integer, FOREIGN KEY (b, c) REFERENCES other_table (c1, c2) );
Естественно, число и типы столбцов в ограничении должны соответствовать числу и типам целевых столбцов.
Ограничению внешнего ключа можно назначить имя стандартным способом.
Таблица может содержать несколько ограничений внешнего ключа. Это полезно для связи таблиц в отношении многие-ко-многим. Скажем, у вас есть таблицы продуктов и заказов, но вы хотите, чтобы один заказ мог содержать несколько продуктов (что невозможно в предыдущей схеме). Для этого вы можете использовать такую схему:
CREATE TABLE products ( product_no integer PRIMARY KEY, name text, price numeric ); CREATE TABLE orders ( order_id integer PRIMARY KEY, shipping_address text, . ); CREATE TABLE order_items ( product_no integer REFERENCES products, order_id integer REFERENCES orders, quantity integer, PRIMARY KEY (product_no, order_id) );
Заметьте, что в последней таблице первичный ключ покрывает внешние ключи.
Мы знаем, что внешние ключи запрещают создание заказов, не относящихся ни к одному продукту. Но что делать, если после создания заказов с определённым продуктом мы захотим удалить его? SQL справится с этой ситуацией. Интуиция подсказывает следующие варианты поведения:
Запретить удаление продукта
Удалить также связанные заказы
Для иллюстрации давайте реализуем следующее поведение в вышеприведённом примере: при попытке удаления продукта, на который ссылаются заказы (через таблицу order_items ), мы запрещаем эту операцию. Если же кто-то попытается удалить заказ, то удалится и его содержимое:
CREATE TABLE products ( product_no integer PRIMARY KEY, name text, price numeric ); CREATE TABLE orders ( order_id integer PRIMARY KEY, shipping_address text, . ); CREATE TABLE order_items ( product_no integer REFERENCES products ON DELETE RESTRICT, order_id integer REFERENCES orders ON DELETE CASCADE, quantity integer, PRIMARY KEY (product_no, order_id) );
Ограничивающие и каскадные удаления — два наиболее распространённых варианта. RESTRICT предотвращает удаление связанной строки. NO ACTION означает, что если зависимые строки продолжают существовать при проверке ограничения, возникает ошибка (это поведение по умолчанию). (Главным отличием этих двух вариантов является то, что NO ACTION позволяет отложить проверку в процессе транзакции, а RESTRICT — нет.) CASCADE указывает, что при удалении связанных строк зависимые от них будут так же автоматически удалены. Есть ещё два варианта: SET NULL и SET DEFAULT . При удалении связанных строк они назначают зависимым столбцам в подчинённой таблице значения NULL или значения по умолчанию, соответственно. Заметьте, что это не будет основанием для нарушения ограничений. Например, если в качестве действия задано SET DEFAULT , но значение по умолчанию не удовлетворяет ограничению внешнего ключа, операция закончится ошибкой.
Аналогично указанию ON DELETE существует ON UPDATE , которое срабатывает при изменении заданного столбца. При этом возможные действия те же, а CASCADE в данном случае означает, что изменённые значения связанных столбцов будут скопированы в зависимые строки.
Обычно зависимая строка не должна удовлетворять ограничению внешнего ключа, если один из связанных столбцов содержит NULL. Если в объявление внешнего ключа добавлено MATCH FULL , строка будет удовлетворять ограничению, только если все связанные столбцы равны NULL (то есть при разных значениях (NULL и не NULL) гарантируется невыполнение ограничения MATCH FULL ). Если вы хотите, чтобы зависимые строки не могли избежать и этого ограничения, объявите связанные столбцы как NOT NULL .
Внешний ключ должен ссылаться на столбцы, образующие первичный ключ или ограничение уникальности. Таким образом, для связанных столбцов всегда будет существовать индекс (определённый соответствующим первичным ключом или ограничением), а значит проверки соответствия связанной строки будут выполняться эффективно. Так как команды DELETE для строк главной таблицы или UPDATE для зависимых столбцов потребуют просканировать подчинённую таблицу и найти строки, ссылающиеся на старые значения, полезно будет иметь индекс и для подчинённых столбцов. Но это нужно не всегда, и создать соответствующий индекс можно по-разному, поэтому объявление внешнего ключа не создаёт автоматически индекс по связанным столбцам.
Подробнее об изменении и удалении данных рассказывается в Главе 6. Вы также можете подробнее узнать о синтаксисе ограничений внешнего ключа в справке CREATE TABLE .
5.3.6. Ограничения-исключения
Ограничения-исключения гарантируют, что при сравнении любых двух строк по указанным столбцам или выражениям с помощью заданных операторов, минимум одно из этих сравнений возвратит false или NULL. Записывается это так:
CREATE TABLE circles ( c circle, EXCLUDE USING gist (c WITH &&) );
При добавлении ограничения-исключения будет автоматически создан индекс того типа, который указан в объявлении ограничения.
| Пред. | Наверх | След. |
| 5.2. Значения по умолчанию | Начало | 5.4. Системные столбцы |
Constraint postgresql что это
SET CONSTRAINTS — установить время проверки ограничений для текущей транзакции
Синтаксис
SET CONSTRAINTS < ALL | имя [, . ] >
Описание
SET CONSTRAINTS определяет, когда будут проверяться ограничения в текущей транзакции. Ограничения IMMEDIATE проверяются в конце каждого оператора, а ограничения DEFERRED откладываются до фиксации транзакции. Режим IMMEDIATE или DEFERRED задаётся для каждого ограничения независимо.
При создании ограничение получает одну из следующих характеристик: DEFERRABLE INITIALLY DEFERRED (откладываемое, изначально отложенное), DEFERRABLE INITIALLY IMMEDIATE (откладываемое, изначально немедленное) или NOT DEFERRABLE (неоткладываемое). Третий вариант всегда подразумевает IMMEDIATE и на него команда SET CONSTRAINTS не влияет. Первые два варианта запускаются в каждой транзакции в указанном режиме, но их поведение можно изменить в рамках транзакции командой SET CONSTRAINTS .
SET CONSTRAINTS со списком имён ограничений меняет режим только этих ограничений (все они должны быть откладываемыми). Имя любого ограничения можно дополнить схемой. Если имя схемы не указано, в поисках первого подходящего имени будет просматриваться текущий путь поиска схем. SET CONSTRAINTS ALL меняет режим всех откладываемых ограничений.
Когда SET CONSTRAINTS меняет режим ограничения с DEFERRED на IMMEDIATE , новый режим начинает действовать в обратную сторону: все изменения данных, ожидающие проверки в конце транзакции, вместо этого проверяются в момент выполнения команды SET CONSTRAINTS . Если какое-либо ограничение нарушается, при выполнении SET CONSTRAINTS происходит ошибка (и режим проверки не меняется). Таким образом, с помощью SET CONSTRAINTS можно принудительно проверить ограничения в определённом месте транзакции.
В настоящее время это распространяется только на ограничения UNIQUE , PRIMARY KEY , REFERENCES (внешний ключ) и EXCLUDE . Ограничения NOT NULL и CHECK всегда проверяются немедленно в момент добавления или изменения строки ( не в конце оператора). Ограничения уникальности и ограничения-исключения, объявленные без указания DEFERRABLE , так же проверяются немедленно.
Срабатывание триггеров, объявленных как « триггеры ограничений » так же зависит от этой команды — они срабатывают в момент, когда должно проверяться соответствующее ограничение.
Замечания
Так как Postgres Pro не требует, чтобы имена ограничений были уникальны в схеме (достаточно уникальности в таблице), возможно, что для заданного имени найдётся несколько соответствующих ограничений. В этом случае SET CONSTRAINTS подействует на все эти ограничения. Для имён без указания схемы, её действие будет распространяться только на ограничение(я), найденное в первой из схем; другие схемы просматриваться не будут.
Эта команда меняет поведение ограничений только в текущей транзакции. При выполнении этой команды вне блока транзакции выдаётся предупреждение и больше ничего не происходит.
Совместимость
Эта команда реализует поведение, описанное в стандарте SQL, с одним исключением — в Postgres Pro она не влияет на проверку ограничений NOT NULL и CHECK . Кроме того, Postgres Pro проверяет неоткладываемые ограничения уникальности немедленно, а не в конце оператора, как предлагает стандарт.
| Пред. | Наверх | След. |
| SET | Начало | SET ROLE |