Нетривиальное использование таблиц В PostgreSQL предусмотрено несколько вариантов ограничения данных, участвующих в операциях вставки и обновления. Один из них заключается в установке ограничений для таблиц и полей. Кроме того, в PostgreSQL поддерживается механизм наследования, характерный для объектно-реляционных СУБД. Наследование позволяет установить между таблицами связи типа «предок—потомок» и создать иерархию полей. В этом разделе будут рассмотрены обе темы. Кроме того, речь пойдет о создании и практическом применении производных таблиц. Ограничения в таблицах Ограничение (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 значение. Пропущенные значения поля заменяются заданной величиной. Значение по умолчанию должно относиться к типу данных, соответствующему типу поля. В PostgreSQL 7.1.x значение по умолчанию не может задаваться при помощи подзапроса. CHECK условие. Команда INSERT или UPDATE для записи завершается успешно лишь при выполнении заданного условия (выражения, возвращающего логический результат). При установке ограничения поля в секции CHECK может использоваться только поле, для которого устанавливается ограничение. REFERENCES. Это ограничение состоит из нескольких секций, которые перечислены ниже. REFERENCES таблица [ ( поле ) ]. Входные значения ограничиваемого поля сравниваются со значениями другого поля в заданной таблице. Если совпадения отсутствуют, команда INSERT или UPDATE завершается неудачей. Если параметр поле не указан, проверка выполняется по первичному ключу. Ограничение REFERENCES похоже на ограничение таблицы FOREIGN KEY, описанное в следующем пункте этого подраздела. Действительно, между этими ограничениями есть много общего. Пример таблицы, созданной с ограничением FOREIGN KEY, приведен в листинге 7.8. MATCH FULL | MATCH PARTIAL. Секция MATCH указывает, разрешается ли смешивание значений NULL и обычных значений при вставке в таблицу, у которой внешний ключ ссылается на несколько полей. Таким образом, на практике секция MATCH приносит пользу лишь в ограничениях таблиц, хотя формально она может использоваться и при ограничении полей. Конструкция MATCH FULL запрещает вставку данных, у которых часть полей внешнего ключа содержит псевдозначение NULL (кроме случая, когда NULL содержится во всех полях). В PostgreSQLV.l.x конструкция MATCH PARTIAL не поддерживается. Если секция MATCH отсутствует, считается, что поля с псевдозначениями NULL удовлетворяют ограничению. Также будет уместно напомнить, что ограничения полей относятся лишь к одному полю, поэтому секция MATCH используется лишь в ограничениях таблиц. ON DELETE операция. При выполнении команды DELETE для заданной таблицы с ограничиваемым полем выполняется одна из следующих операций: N0 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. В листинге 7.7 приведен пример создания таблицы employees с несколькими ограничениями. Листинг 7.7. Создание таблицы с ограничениями полей booktown=# CREATE TABLE employees booktown-f (id integer PRIMARY KEY CHECK (id > 100). booktown(# lastjiame text NOT NULL. booktown(# first_name text); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'employeesjjkey' for table 'employees' CREATE В листинге 7.7 создается поле id типа integer, для которого устанавливаются ограничения PRIMARY KEY и CHECK. Ограничение PRIMARY KEY также подразумевает ограничения NOT NULL и UNIQUE и приводит к автоматическому созданию индекса empl oyees_pkey для ограничиваемого поля. Ограничение CHECK гарантирует, что значение поля id всегда больше 100. Это означает, что любые попытки вставки или обновления в таблице empl oyees записей, у которых поле id меньше либо равно 100, завершаются неудачей. Таблица employees, созданная в листинге 7.7, также содержит текстовое поле 1 astjiame, для которого установлено ограничение NOT NULL. Это более простое ограничение запрещает появление в таблице записей, у которых поле lastjiame содержит NULL. Иначе говоря, это поле обязательно для заполнения. ПРИМЕЧАНИЕ Условия в секциях CHECK должны оперировать со значениями сравнимых типов данных. Ограничения таблиц В ограничениях таблиц, в отличие от ограничений полей, могут участвовать сразу несколько полей таблицы. Синтаксис ограничения таблицы: [ 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 для полей. В листинге 7.8 в базе данных booktown создается таблица editions с тремя ограничениями. Развернутое описание приводится ниже. Листинг 7.8. Использование ограничений таблицы booktown=# CREATE TABLE editions booktown-# (isbn text, booktown(# bookjid integer, booktown(# edition integer, booktown(# publisherjd integer, booktownCl publication date. booktown(# type char, booktown(# CONSTRAINT pkey PRIMARY KEY (isbn), booktown(# CONSTRAINT integrity CHECK (bookjd IS NOT NULL booktown(# AND edition IS NOT NULL). booktown(# CONSTRAINT book_exists FOREIGN KEY (bookjd) booktown(# REFERENCES books (id) booktown(# ON DELETE CASCADE booktown(# ON UPDATE CASCADE); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pkey1 for table 'editions' NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE Первое ограничение, pkey, относится к типу PRIMARY KEY и устанавливается для таблицы по полю isbn. Оно практически не отличается от ограничения PRIMARY KEY для поля, поскольку список в данном примере состоит всего из одного поля. Ограничение i ntegri ty гарантирует, что поля book i d и edi ti on не содержат псевдозначения NULL. Наконец, ограничение book_exists при помощи конструкций FOREIGN KEY и REFERENCES гарантирует, что значение поля book_id встречается в поле id таблицы books. Более того, поскольку в секциях ON DELETE и ON ACTION встречается ключевое слово CASCADE, любые модификации поля ids таблице books приведут к каскадным изменениям записей в таблице editions, а при удалении записей из таблицы books будут удалены соответствующие записи таблицы editions. Для этих ограничений в базе данных автоматически строится индекс editions_pkey по полю isbn, а также создается триггер. Индекс обеспечивает выполнение ограничения PRIMARY KEY, а триггер относится к ограничению FOREIGN KEY. Добавление ограничений в существующую таблицу Команда ALTER TABLE позволяет включать ограничения в существующую таблицу. Впрочем, в PostgreSQL 7.1.x поддерживается только возможность добавления ограничений CHECK и FOREIGN KEY. Установка ограничений в команде ALTER TABLE имеет следующий синтаксис: ALTER TABLE таблица ADD [ CONSTRAINT ограничение ] { CHECK ( условие ) | FOREIGN KEY ( поле [. ... ] ) REFERENCES таблица [ ( поле [....])] [ MATCH FULL | MATCH PARTIAL ] [ ON DELETE операция ] [ ON UPDATE операция ] [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED INITIALLY IMMEDIATE ] } В листинге 7.9 устанавливается новое ограничение FOREIGN KEY для поля subjected, которое связывается с полем id таблицы subjects. Ограничение гарантирует, что в результате вставки или обновления данных в поле subjected таблицы books не появятся значения, отсутствующие в поле id таблицы subjects. Листинг 7.9. Добавление ограничений в существующую таблицу booktown=# ALTER TABLE books booktown-# ADD CONSTRAINT legal_subjects booktown-# FOREIGN KEY (subjectjd) booktown-# REFERENCES subjects (id); NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) CREATE Удаление ограничений В PostgreSQL 7.1.x не поддерживается прямое удаление ограничений из таблицы. Добиться нужного результата можно лишь одним способом — создать копию таблицы, практически полностью повторяющую оригинал, но не содержащую удаляемых ограничений. Данные копируются из исходной таблицы в новую, после чего таблицы переименовываются командой ALTER TABLE и копия заменяет оригинал. ВНИМАНИЕ Применяя этот прием, следует учитывать, что кто-то из пользователей может подключиться к базе данных и работать с модифицируемыми таблицами. Вставка или любые модификации данных в процессе копирования недопустимы; таким образом, если таблица активно используется, вы можете временно запретить подключения к PostgreSQL, внести необходимые изменения и перезапустить систему. В листинге 7.10 снятие ограничений продемонстрировано на примере ограничения FOREIGN KEY с именем legal_subjects, установленного для таблицы books (см. листинг 7.9). Обратите внимание на удаление индекса books_1d_pkey перед созданием новой таблицы, что позволяет создать таблицу с индексом books_id_pkey. На самом деле это не обязательно, но имя индекса первичного ключа лучше сохранить. Листинг 7.10. Удаление ограничений booktown=*# DROP INDEX books_id_pkey; DROP booktown=# CREATE TABLE new_books booktown-# (id integer CONSTRAINT books_id_pkey PRIMARY KEY. booktown(# title text NOT NULL. booktown(# author_id integer. booktown(# subjected integer): NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'books_id_pkey' for table 'new_books' CREATE booktown=# INSERT INTO new_books SELECT * FROM books: INSERT 0 15 booktown=f ALTER TABLE books RENAME TO old_books: ALTER booktown=# ALTER TABLE new_books RENAME TO books; ALTER Наследование В PostgreSQL поддерживается механизм создания объектно-реляционных связей, называемый наследованием. Таблица может наследовать некоторые атрибуты своих полей от одной или нескольких других таблиц, что приводит к созданию отношений типа «предок—потомок». В результате производные таблицы («потомки») обладают теми же полями и ограничениями, что и их базовые таблицы («предки»), а также дополняются собственными полями. При составлении запроса к базовой таблице можно потребовать, чтобы запрос произвел выборку только из самой таблицы или же просмотрел как таблицу, так и ее производные таблицы. С другой стороны, в результаты запроса к производной таблице никогда не включаются записи из базовой таблицы. Создание производной таблицы Производная таблица создается командой SQL CREATE TABLE, в которую включается секция INHERITS. Секция состоит из ключевого слова INHERITS и имени базовой таблицы (или нескольких таблиц). Часть команды CREATE TABLE, относящаяся к наследованию, выглядит так: CREATE TABLE производная_таблица определение INHERITS ( базовая_таблица [. ...] ) В этом определении производная таблица — имя создаваемой таблицы, определение — полное определение таблицы со всеми стандартными секциями команды CREATE TABLE, а базовая _таблица — таблица, структура которой наследуется новой таблицей. Дополнительные имена базовых таблиц перечисляются через запятую. В листинге 7.11 создается таблица distinguished_authors, определение которой состоит из единственного текстового поля award. Поскольку в команде создания таблицы указано ключевое слово INHERITS, таблица содержит четыре поля — одно собственное и три унаследованных. Листинг 7.11. Создание производной таблицы booktown=# CREATE TABLE distinguished_authors (award text) booktown-# INHERITS (authors): CREATE booktown=# \d distinguished_authors Table "distinguished_authors" Attribute | Type Modifier id | integer | not null lastjiame text | firstjiame | text award text Как видите, несмотря на то что в листинге 7.11 определено всего одно поле, таблица distinguished_authors унаследовала все поля исходной таблицы authors. Использование производных таблиц Связь общих полей базовой и производной таблиц не ограничивается чисто косметическими удобствами. Данные, занесенные в таблицу distinguished_authors, присутствуют и в родительской таблице authors. Впрочем, в таблице authors видны только три унаследованных поля. В запрос к базовой таблице можно включить ключевое слово ONLY, которое указывает, что данные производных таблиц исключаются из результатов запроса. ПРИМЕЧАНИЕ Данные базовых таблиц никогда не включаются в результаты запросов к производным таблицам. Таким образом, ключевое слово ONLY в запросе к производной таблице действует лишь в том случае, если у производной таблицы есть собственный потомок, из-за чего она одновременно является и производной, и базовой. В листинге 7.12 в таблицу di sti ngui shed_authors заносятся данные о новом авторе Nei I Simon с текстом Pul itzer Prize в поле award. Обратите внимание: что первые три переданных значения являются общими для обеих таблиц, базовой и производной. Листинг 7.12. Вставка данных в производную таблицу booktown=# INSERT INTO distinguished_authors booktown-# VALUES (nextvaK 'authorjds'), booktown(# 'Simon'. 'Neil', 'Pulitzer Prize'); INSERT 3629421 1 Поскольку первые три поля таблицы di sti ngui shed_authors были унаследованы от таблицы authors, данные этой записи косвенно включаются в таблицу authors (хотя непосредственная вставка в таблицу authors не выполнялась). Тем не менее поле award будет присутствовать только в таблице di sti ngui shed_authors, поскольку наследование действует только в одну сторону (от родителя к потомку). В листинге 7.13 выполняются три команды SELECT. В секциях FROM указываются разные цели, тогда как условия в секциях WHERE всех трех команд одинаковы. Листинг 7.13. Выборка с наследованием booktown=# SELECT * FROM distinguished_authors booktown-# WHERE lastjiame - 'Simon'; id | last_name firstjname award 25043 | Simon | Neil | Pulitzer Prize (1 row) booktown=# SELECT * FROM authors WHERE last_name - 'Simon'; Id last_name first_name 25043 | Simon | Neil (1 row) booktown=# SELECT * FROM ONLY authors WHERE lastjname = 'Sinon': id i last_name | first_name (0 rows) Все три запроса в листинге 7.13 производят выборку записей, у которых поле 1 astjname совпадает со строковой константой Simon. Первый запрос извлекает данные из таблицы distinguished_authors, в которую они были первоначально занесены (см. листинг 7.12). Второй запрос в листинге 7.13 производит выборку из таблицы authors, базовой по отношению к distinguished_authors. В этом случае запись также возвращается, но в нее включаются только поля, унаследованные таблицей di st i ngui shed_authors. Следует хорошо понимать, что данные в действительности не заносятся в базовую таблицу, а лишь становятся видимыми в ней благодаря отношению наследования. Это доказывает третий и последний запрос в листинге 7.13, в котором перед именем таблицы authors находится ключевое слово ONLY. Оно означает, что выборка производится только из базовой таблицы, а производные таблицы игнорируются; в результате запрос не возвращает ни одной записи. Наследование может приводить к видимому нарушению ограничений. Например, значение поля, для которого установлено ограничение уникальности, может повторяться в данных производных таблиц. Применение наследования требует осторожности, поскольку производная таблица формально не нарушает ограничений, хотя при выборке из базовой таблицы без ключевого слова ONLY может показаться обратное. Модификация производных таблиц Как упоминалось в предыдущем разделе, процесс включения данных в базовые и производные таблицы весьма прямолинеен. Вставка в производную таблицу приводит к кажущемуся появлению данных в базовой таблице, хотя сами данные по-прежнему находятся в производной таблице. Вставка данных в базовую таблицу никак не отражается на производной таблице. Процесс модификации данных в производной таблице достаточно прост и очевиден — изменяется только содержимое производной таблицы, а все данные из базовой таблицы остаются без изменений. Как говорилось выше, данные не являются общими в обычном смысле слова, а лишь видны в других таблицах. Выборка из базовой таблицы без ключевого слова ONLY выведет как записи базовой таблицы, так и модифицированные записи производной таблицы. С модификацией записей в базовых таблицах дело обстоит сложнее. Команды UPDATE и DELETE по умолчанию работают не только с записями базовой таблицы, но и с записями всех производных таблиц, подходящих по заданному критерию. В листинге 7.14 выполняется команда UPDATE для таблицы authors. Как видно из листинга, команда также изменяет записи таблицы di stinguished_authors. Листинг 7.14. Модификация базовых и производных таблиц booktown=# UPDATE authors SET firstjiame - 'Paul' booktown-# WHERE last_name = 'Simon'; UPDATE 1 booktown=# SELECT * FROM distinguished_authors; id | lastjiame first_name award ---25043 Simon | Paul Pulitzer Prize (1 row) Ключевое слово ONLY выполняет в командах UPDATE и DELETE те же функции, что и в команде SELECT — оно предотвращает каскадные модификации, продемонстрированные в листинге 7.14. Согласно правилам синтаксиса SQL ключевое слово ONLY всегда предшествует имени производной таблицы. Пример использования ключевого слова ONLY приведен в листинге 7.15. Сначала в таблице distinginshed_authors создается запись, в которой заполняется поле award. В результате в таблице authors появляются две разные записи для одного автора. Затем старая запись (физически находящаяся в таблице authors) удаляется командой SQL DELETE с ключевым словом ONLY. Листинг 7.15. Модификация базовых таблиц с ключевым словом ONLY booktown=# INSERT INTO distinguished_authors booktown-* VALUES (1809. 'Geisel'. booktown(# 'Theodor Seuss', 'Pulitzer Prize'); INSERT 3629488 1 booktown=# SELECT * FROM authors booktown-# WHERE lastjname = 'Geisel': id | lastjname | firstjiame 1809 | Geisel | Theodor Seuss 1809 | Geisel | Theodor Seuss (2 rows) booktown=# DELETE FROM ONLY authors booktown-# WHERE lastjiame = 'Geisel'; DELETE 1 В итоге после выполнения листинга 7.15 запись появляется в таблице distinguished_authors и удаляется из таблицы authors: booktown=# SELECT * FROM authors booktown-# WHERE lastjiame = 'Geisel': id | lastjiame | firstjiame 1809 Geisel | Theodor Seuss (1 row) booktown=# SELECT * FROM distinguishedjauthors booktown-# WHERE lastjiame = 'Geisel1; id | lastjiame | firstjiame | award 1809 | Geisel | Theodor Seuss | Pulitzer Prize (1 row)