3.5. Создание ограничений

Ограничение (constraint) представляет собой особый атрибут таблицы, который устанавливает критерии допустимости для содержимого ее полей. Соблюдение этих правил помогает предотвратить заполнение базы ошибочными или неподходящими данными.

Ограничения задаются в секции CONSTRAINT при создании таблицы командой CREATE TABLE. Они делятся на два типа - ограничения полей и ограничения таблиц.

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

В команде CREATE TABLE ограничения полей задаются сразу же после определения поля, тогда как ограничение таблицы устанавливается в специальном блоке, отделенном запятой от всех определений полей. Поля, на которые распространяется ограничение таблицы, задаются самим определением, а не его расположением в команде.

Ниже описаны различные правила, устанавливаемые при помощи ограничений.

При выполнении команды \h CREATE TABLE клиент psql выводит несколько подробных синтаксических диаграмм для ограничений, которые могут устанавливаться для таблиц. Список команд интерактивного терминала приведен в приложении Г. Синтаксис ограничения поля выглядит так:

[CONSTRAINT ограничение]

{NOT NULL | UNIQUE | PRIMARY KEY | DEFAULT значение |

CHECK (условие) | REFERENCES таблица [(поле)]

[MATCH FULL | MATCH PARTIAL] [ON DELETE операция]

[ON UPDATE операция] [DEFERRABLE | NOT DEFERRABLE]

[INITIALLY DEFERRED | INITIALLY IMMEDIATE]}

Определение следует в команде CREATE TABLE сразу же за типом ограничиваемого поля и предшествует запятой, отделяющей его от следующего поля. Ограничения могут устанавливаться для любого количества полей, а ключевое слово CONSTRAINT и идентификатор ограничение не обязательны.

Существует шесть типов ограничений полей, задаваемых при помощи специальных ключевых слов. Некоторые из них косвенно устанавливаются при создании ограничений другого типа. Типы ограничений полей перечислены ниже.

NOT NULL. Поле не может содержать псевдозначение NULL. Ограничение NOT NULL эквивалентно ограничению CHECK (поле NOT NULL).

UNIQUE. Поле не может содержать повторяющиеся значения. Следует учитывать, что ограничение UNIQUE допускает многократное вхождение псевдозначений NULL, поскольку формально NULL не совпадает ни с каким другим значением.

PRIMARY KEY. Автоматически устанавливает ограничения UNIQUE и NOT NULL, а для заданного поля создается индекс. В таблице может устанавливаться только одно ограничение первичного ключа.

DEFAULT значение. Пропущенные значения поля заменяются заданной величиной. Значение по умолчанию должно относиться к типу данных, соответствующему типу поля

CHECK (условие). Команда INSERT или UPDATE для записи завершается успешно лишь при выполнении заданного условия (выражения, возвращающего логический результат). При установке ограничения поля в секцииCHECK может использоваться только поле, для которого устанавливается ограничение.

REFERENCES. Это ограничение состоит из нескольких секций. REFERENCES таблица [(поле)]. Входные значения ограничиваемого поля сравниваются со значениями другого поля в заданной таблице. Если совпадения отсутствуют, команда INSERT или UPDATE завершается неудачей. Если параметр поле не указан, проверка выполняется по первичному ключу.

MATCH FULL | MATCH PARTIAL. Секция MATCH указывает, разрешается ли смешивание значений NULL и обычных значений при вставке в таблицу, у которой внешний ключ ссылается на несколько полей. Таким образом, на практике секция MATCH приносит пользу лишь в ограничениях таблиц, хотя формально она может использоваться и при ограничении полей. Конструкция MATCH FULL запрещает вставку данных, у которых часть полей внешнего ключа содержит псевдозначение NULL (кроме случая, когда NULL содержится во всех полях). Если секция MATCH отсутствует, считается, что поля с псевдозначениями NULL удовлетворяют ограничению. Также будет уместно напомнить, что ограничения полей относятся лишь к одному полю, поэтому секция MATCH используется лишь в ограничениях таблиц.

ON DELETE операция. При выполнении команды DELETE для заданной таблицы с ограничиваемым полем выполняется одна из следующих операций: NO ACTION (если удаление приводит к нарушению целостности ссылок, происходит ошибка; используется по умолчанию, если операция не указана), RESTRICT (аналогично NO ACTION), CASCADE (удаление всех записей, содержащих ссылки на удаляемую запись), SET NULL (поля, содержащие ссылки на удаляемую запись, заменяются псевдозначениями NULL), SET DEFAULT (полям, содержащим ссылки на удаляемую запись, присваивается значение по умолчанию).

ON UPDATE операция. При выполнении команды UPDATE для заданной таблицы выполняется одна из операций, описанных выше. По умолчанию используется значение NO ACTION. Если выбрана операция CASCADE, все записи, содержащие ссылки на обновляемую запись, обновляются новым значением (вместо удаления, как в случае с ON DELETE CASCADE).

DEFERRABLE | NOT DEFERRABLE. Значение DEFERRABLE позволяет отложить выполнение ограничения до конца транзакции (вместо немедленного выполнения после завершения команды). Значение NOT DEFERRABLEозначает, что ограничение всегда проверяется сразу же после завершения очередной команды. В этом случае пользователь не может отложить проверку ограничения до конца транзакции. По умолчанию выбирается именно этот вариант.

INITIALLY DEFERRED | INITIALLY IMMEDIATE. Секция INITIALLY задается только для ограничений, определенных с ключевым словом DEFERRED. Значение INITIALLY DEFERRED откладывает проверку ограничения до конца транзакции, а при установке значения INITIALLY IMMEDIATE проверка производится после каждой команды. При отсутствии секции INITIALLY по умолчанию используется значение INITIALLY IMMEDIATE.

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

[CONSTRAINT ограничение] {UNIQUE (поле [...]) |

PRIMARY KEY (поле [...]) | CHECK (условие) |

FOREIGN KEY (поле [...]) REFERENCES таблица [(поле [...])]

[MATCH FULL | MATCH PARTIAL] [ON DELETE операция]

[ON UPDATE операция] [DEFERRABLE | NOT DEFERRABLE]

[INITIALLY DEFERRED | INITIALLY IMMEDIATE]

Секция CONSTRAINT ограничение определяет необязательное имя. Ограничениям рекомендуется присваивать содержательные имена вместо автоматически сгенерированных имен, не несущих никакой полезной информации. В будущем имя также может пригодиться и для удаления ограничения (например, в секции DROP CONSTRAINT команды ALTER TABLE). Другие секции относятся к четырем разновидностям ограничений таблиц.

PRIMARY KEY (поле [...]). Ограничение таблицы PRIMARY KEY имеет много общего с аналогичным ограничением поля. В ограничении таблицы PRIMARY KEY могут перечисляться несколько полей, разделенных запятыми. Для перечисленных полей автоматически строится индекс. Как и в случае с ограничением поля, комбинация значений всех полей должна быть уникальной и не может содержать NULL.

UNIQUE (поле [...]). Ограничение означает, что комбинация значений полей, перечисленных за ключевым словом UNIQUE, принимает только уникальные значения. Допускается многократное вхождение псевдозначения NULL, поскольку оно формально не совпадает ни с одним значением.

CHECK (условие). Команда INSERT или UPDATE для записи завершается успешно лишь при выполнении заданного условия (выражения, возвращающего логический результат). Используется по аналогии с ограничениями полей, но в секции CHECK может содержать ссылки на несколько полей.

FOREIGN KEY (поле [...]) REFERENCES таблица [(поле [...])]. В качестве прототипа для секции REFERENCES можно перечислить несколько полей. Синтаксис части, следующей за секцией FOREIGN KEY, идентичен синтаксису ограничения REFERENCES для полей.

Команда ALTER TABLE позволяет включать ограничения в существующую таблицу. Установка ограничений в команде ALTER TABLE имеет следующий синтаксис:

ALTER TABLE таблица ADD | DROP [CONSTRAINT ограничение]

{CHECK (условие) | FOREIGN KEY (поле [...])

REFERENCES таблица [(поле [....])] [MATCH FULL | MATCH PARTIAL]

[ON DELETE операция] [ON UPDATE операция]

[DEFERRABLE | NOT DEFERRABLE]

[INITIALLY DEFERRED | INITIALLY IMMEDIATE]}

Контрольные вопросы

1.  Какими правами можно наделять пользователей?

2.  Какими свойствами обладают транзакции?

3.  Для чего нужен откат транзакций?

4.  Можно ли осуществить откат части транзакции? Если да, то как?

5.  Каким образом можно исправить структуру таблицы?

6.  Какой формат имеет команда, с помощью которой можно скопировать структуру таблицы?

7.  Каким образом можно просмотреть структуру базы данных, вызвать справку для конкретной команды?

8.  Как можно удалить таблицу, базу данных?

9.  Как сохранить базу данных в файл?

10.  Как восстановить базу данных из файла?

11.  Как можно ограничить поле таким образом, чтобы данные в нем соответствовали заданным условиям?

Список источников и литературы

1.  Журналы онлайн (русский) [Электронный ресурс]: Журнал Linux Format за ноябрь 2006 года. – Режим доступа: http://av5.com/journals-magazines-online/ . - загл. с экрана.

2.  Дж Уорсли Дж., Дрейк Дж. PostgreSQL. Для профессионалов - СПб.: Питер, 20с.

3.  PostgreSQL (русский) [Электронный ресурс]: Документация по PostgreSQL. – Режим доступа: http://postgresql. . - загл. с экрана.

4.  Википедия свободная энциклопедия (русский) [Электронный ресурс]: PostgreSQL. – Режим доступа: http://ru. wikipedia. org. - загл. с экрана.

5.  Журналы онлайн (русский) [Электронный ресурс]: Журнал Системный Администратор за октябрь 2004 года. – Режим доступа: http://av5.com/journals-magazines-online/ . - загл. с экрана.

6.  Компьютерная документация (русский) [Электронный ресурс]: Функции для работы с датой и временем. – Режим доступа: http://www. *****/ . - загл. с экрана.

7.  Linux и Windows: помощь админам и пользователям (русский) [Электронный ресурс]: 15 команд для управления PostgreSQL. – Режим доступа: http://www. ***** . - загл. с экрана.

8.  Учебники по программированию (русский) [Электронный ресурс]: SQL-сервер в Linux. – Режим доступа: http://www. *****/ . - загл. с экрана.

9.  Мини-портал Linux (русский) [Электронный ресурс]: Оптимизация Apache + PHP + PostgreSQL. – Режим доступа: http://linux. ***** . - загл. с экрана.