Использование таблиц Таблицы являются основными блоками хранения данных в базе. Перед любыми операциями создания, выборки или модификации записей необходимо сначала создать таблицу, в которой эти записи будут храниться. В этом разделе рассматриваются процедуры создания, модификации и удаления таблиц командами CREATE TABLE, ALTER TABLE n DROP TABLE SQL (вопросам создания баз данных посвящена глава 9). Создание таблицы командой CREATE TABLE В языке SQL таблицы создаются командой CREATE TABLE. Минимальный набор параметров включает имя таблицы и описания полей, состоящие из имени поля и типа данных. Команде CREATE TABLE также могут передаваться некоторые необязательные параметры: ограничения полей (правила, которые определяют, какие данные могут или не могут присутствовать в поле) и ограничения таблицы (общие ограничения и связи, определенные для самой таблицы). Синтаксис команды CREATE TABLE Синтаксис команды CREATE TABLE выглядит так: CREATE [ TEMPORARY | TEMP ] TABLE имя_таблицы ( { имя_поля тип [ограничение_поля [... ] ] | ограничение_таблицы } [. ..Т ] ) [ INHERITS (бдзовая_тзблица [....])] Ниже поясняется смысл компонентов команды. TEMPORARY | TEMP. Таблица, созданная с ключевым словом TEMPORARY или TEMP, автоматически уничтожается в конце текущего сеанса. Имя временной таблицы может совпадать с именем существующей таблицы; в этом случае все ссылки на таблицу с этим именем будут относиться к временной таблице (до момента ее уничтожения). Все индексы таблицы также являются временными и уничтожаются в конце сеанса. Имя_таблицы. Имя для ссылок на таблицу после ее создания. Имя_поля тип [ограничение _поля ] | ограничение ^таблицы. После имени таблицы в круглых скобках перечисляются определения полец таблицы, разделенные запятыми. Определение поля состоит из имени, которое представляет собой синтаксически правильный идентификатор допустимого типа, а также необязательного ограничения. Ограничения полей описаны в подразделе «Ограничения в таблицах» раздела «Нетривиальное использование таблиц» главы 7. Ограничения полей и таблицы могут чередоваться, хотя на практике обычно сначала перечисляются ограничения полей, а затем следуют ограничения таблицы. [....]. После определения поля может следовать запятая, за которой указано новое определение. Многоточие означает произвольное количество определений полей (вплоть до максимального значения 1600). Помните, что за последним элементом списка не может следовать запятая, как разрешено в языках типа Perl; это приведет к ошибке лексического разбора. INHERITS ( базовая _таблица [. ...] ). Объектные возможности PostgreSQL позволяют задать одну или несколько таблиц (в виде списка, разделенного запятыми), базовых по отношению к создаваемой (производной) таблице. При наличии этого необязательного элемента объявления между таблицами устанавливается связь типа «предок-потомок». Механизм наследования является относительно новой возможностью РСУБД и подробно описывается в подразделе «Наследование» раздела «Нетривиальное использование таблиц» главы 7. ПРИМЕЧАНИЕ Параметры ограничение_поля и ограничение_таблицы в приведенном выше объявлении могут соответствовать достаточно сложным синтаксическим конструкциям. Синтаксис различных ограничений подробно описан в подразделе «Ограничения в таблицах» раздела «Нетривиальное использование таблиц» главы 7. Пример создания таблицы Команда SQL, приведенная в листинге 4.6, создает таблицу books в базе данных booktovvn. Листинг 4.6. Создание таблицы books booktown=# CREATE TABLE books ( booktown(# id integer UNIQUE. booktown(# title text NOT NULL, booktown(# authoMd integer, booktowntf subjected integer, booktown(# CONSTRAINT books_id_pkey PRIMARY KEY (id)); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'books_id_pkey' for table 'books' CREATE После выполнения команды CREATE выводится сообщение с информацией об успешном создании таблицы. Если вы получите сообщение об ошибке, проверьте правильность расстановки знаков препинания и написания всех ключевых слов. Если сообщения вообще нет, вероятно, в команде осталась незакрытая круглая скобка, апостроф или другой парный символ. Кроме того, в сообщении NOTICE говорится о том, что при создании описанной таблицы был построен скрытый индекс books_id_pkey. Анализ структуры таблицы Команда \d (с указанием имени таблицы) предназначена для вывода структуры таблицы и ее ограничений, если они имеются. В листинге 4.7 приведены выходные данные команды \d для таблицы books, созданной в предыдущем разделе. Обратите внимание: каждая строка результата описывает одно из полей (то есть столбцов) таблицы; таким образом, таблица фактически разворачивается па 90°. Такое представление выбрано для наглядности, поскольку многие таблицы содержат большое количество полей, не помещающихся на экране (или на печатной странице) по горизонтали. В книге этот формат будет использоваться при описании структуры таблиц. Листинг 4.7. Выходные данные команды \d booktown=# \d books Table "books" Attribute | Type | Modifier id integer | not null title text I not null authorjd j integer j subjectjd | integer | Index: books_id_pkey Ниже приведены более подробные описания полей и атрибутов, встречающихся в листинге 4.7. Id. В поле Id хранится числовой код, уникальный для каждой книги. Поле id определяется с типом Integer и для него устанавливаются следующие ограничения: UNIQUE. Ограничение гарантирует уникальность значений поля. В общем случае поле с ограничением уникальности может содержать NULL, но попытки вставки дубликатов завершаются неудачей. Поле Id также используется в качестве первичного ключа. PRIMARY KEY. Хотя в выходных данных команды \d об этом не упоминается, из исходной команды CREATE TABLE видно, что поле id также назначено первичным ключом таблицы. Установка ограничения первичного ключа для поля также неявно подразумевает установку ограничений NOT NULL и UNIQUE. NOT NULL. Автоматически устанавливается при назначении ограничения PRIMARY KEY. Ограничение гарантирует, что поле id всегда содержит значение, отличное от NULL. Поле никогда не остается пустым, и любые попытки вставки псевдозначения NULL завершаются неудачей. title. Поле title содержит символьные данные типа text. Тип text обладает большей гибкостью по сравнению с varchar и хорошо подходит для данного поля, поскольку не требует задания максимального количества символов. Для поля titl e установлено ограничение NOT NULL; это означает, что поле всегда содержит значение, отличное от NULL. authorjd. Поле author_id содержит значения типа integer и используется для связи с таблицей authors. Ограничения для этого поля не устанавливаются, поскольку в таблице могут встречаться книги, написанные неизвестным автором, что исключает ограничение NOT NULL. С другой стороны, один автор может написать несколько книг, поэтому ограничение UNIQUE также не подходит. subject_id. Поле subject_id аналогично полю author_id— оно тоже содержит значения типа integer и используется для установки связи с таблицей subjects. Ограничения для этого поля также отсутствуют, поскольку некоторые книги не принадлежат ни к одной категории, а категории, как правило, содержат более одной книги. Структура таблицы может изменяться после ее создания, но возможности мо-(ификаций ограничены. Например, к их числу относится переименование табли-(ы, переименование существующих и добавление новых полей. PostgreSQL 7.1.x ie позволяет удалять поля из таблиц, поэтому структуру таблицы следует тща-•ельно продумать перед ее созданием. Модификация таблицы командой ALTER TABLE В большинстве современных РСУБД предусмотрена возможность модификации таблиц командой ALTER TABLE. Реализация ALTER TABLE в PostgreSQL 7.1.x поддер-кивает шесть типов модификации: создание полей; назначение и отмена значений по умолчанию; переименование таблицы; переименование полей; дооавление ограничении; смена владельца. Создание полей Для создания нового поля в команду ALTER TABLE включается секция ADD COLUMN. Синтаксис команды ALTER TABLE с секцией ADD COLUMN: ALTER TABLE таблица ADD [ COLUMN ] имя_поля тип_поля таблица — имя таблицы, в которой создается новое поле; имя_поля — имя создаваемого поля; тип_поля — тип создаваемого поля. Ключевое слово COLUMN не является обязательным и включается в команду лишь для наглядности. Предположим, в таблицу books базы данных booktown потребовалось включить новое поле publication для хранения даты публикации. Листинг4.8 показывает, как это делается. Листинг 4.8. Добавление поля booktown=# ALTER TABLE books booktown-# ADD publication date; ALTER booktown=# \d books Table "books" Attribute | Type | Modifier id integer | not null title text I not null authorjd j integer | subjectjd integer | publication date j Index: books_id_pkey Листинг 4.8 показывает, что в таблице books появилось новое поле с именем pub! I cation и типом date. Кроме того, он дает типичный пример плохой координации планирования между разработчиками: в базе данных booktown из нашего примера дата публикации уже хранится в таблице editions, поэтому включать его в таблицу books не нужно. Изменение структуры таблиц после подобных ошибок рассматривается ниже в подразделе «Реструктуризация таблиц». Назначение и отмена значений по умолчанию При модификации существующих таблиц наибольшая свобода действий предоставляется при выборе значений по умолчанию. Программист может относительно легко назначать и отменять эти значения для отдельных полей, для чего используется команда ADD TABLE с секцией ALTER COLUMN. В PostgreSQL команда ALTER TABLE, назначающая или отменяющая значение по умолчанию для поля имя_поля, имеет следующий синтаксис: ALTER TABLE таблица ALTER [ COLUMN ] имя_поля ( SET DEFAULT значение ] DROP DEFAULT } Как и в предыдущем разделе, ключевое слово COLUMN является необязательным включается в команду лишь для наглядности. В листинге 4.9 приведен пример азначения и отмены простой последовательности значений по умолчанию для оля id таблицы books. Листинг 4.9. Изменение значений по умолчанию Doktown=# ALTER TABLE books 3oktOwn-# ALTER COLUMN id x>ktown-# SET DEFAULT nextvalС books.ids'): JER }oktown=# \d books TABLE "books" Attribute | Type | Modifier id integer not null default nextval('books.ids'::text) 1tle | text not null juthorjd | integer ;ubject_id | integer j idex: books_id_pkey )oktown=# ALTER TABLE books )oktown-# ALTER id )oktown-# DROP DEFAULT; JER joktown=# \d books TABLE "books" Attribute | Type | Modifier id | integer | not null ;itle | text not null iuthor_id j integer ;ubject_id j integer idex: books_id_pkey Переименование таблицы Переименование таблиц осуществляется командой ALTER TABLE с секцией RENAME, интаксис переименования таблицы: JER TABLE таблица RENAME TO новое_иня Таблицу можно переименовывать сколько угодно раз, это никак не отражается а состоянии хранящихся в ней данных (листинг 4.10). Конечно, в некоторых си-/ацпях переименования нежелательны — например, если таблица используется вешним приложением. Листинг 4.10. Переименование таблицы oktown=# ALTER TABLE books RENAME TO literature; TER oktown=# ALTER TABLE literature RENAME TO books; TER Переименование полей PostgreSQL позволяет изменять имена полей без изменения данных, хранящихся таблице. Впрочем, переименование полей — дело рискованное, поскольку существующие приложения могут содержать ссылки на имена полей. Если программа обращается к полю по имени, то переименование может нарушить ее работоспособность. Команда переименования полей имеет следующий синтаксис: ALTER TABLE таблица RENAME [ COLUMN ] имя_поля ТО новое_иня_поля Как и в других командах ALTER TABLE, ключевое слово COLUMN является необязательным. По двум идентификаторам, разделенным ключевым словом ТО, Post-greSQL может определить, что команда переименования относится к одному полю, а не таблице. Пример переименования полей приведен в листинге 4.11. Листинг 4.11. Переименование поля booktown=# \d daily_inventory Table "daily_inventory" Attribute | Type | Modifier isbn | text in_stock | boolean | booktown=# ALTER TABLE daily_inventory booktown-# RENAME COLUMN in_stock TO is_in_stock; ALTER booktown=# ALTER TABLE daily_inventory booktown-l RENAME COLUMN is_in_stock TO is_stocked; ALTER Добавление ограничений После создания таблицы сохраняются некоторые возможности добавления ограничении. В PostgreSQL 7.1.x команда ALTER TABLE с секцией ADD CONSTRAINT позволяет определять для полей существующих таблиц только ограничения внешнего ключа и проверки. Команда создания новых ограничений имеет следующий синтаксис: ALTER TABLE таблице ADD CONSTRAINT имя_ограничения определение Синтаксис определения зависит от типа ограничения. В листинге 4.12 продемонстрирован синтаксис создания ограничения внешнего ключа для таблицы editions (связанной с полем id таблицы books) и ограничения проверки для поля type. Листинг 4.12. Создание новых ограничений в существующей таблице booktown=# ALTER TABLE editions booktown-# ADD CONSTRAINT foreign_book booktown-# FOREIGN KEY (book_id) REFERENCES books (id); NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for FOREIGN KEY check(s) CREATE booktown=# ALTER TABLE editions booktown-# ADD CONSTRAINT hard_or_paper_back booktown-# CHECK (type = 'p' OR type = 'h'): ALTER Установка ограничения внешнего ключа приводит к тому, что любое значение book_i d в таблице edi ti ons также должно существовать в таблице books. Кроме того, вследствие установленного ограничения проверки поле type в таблице editions может содержать только значения р или Ь. ПРИМЕЧАНИЕ Ограничение уникальности также неявно устанавливается при создании уникального индекса командой CREATE INDEX (см. раздел «Индексы» в главе 7). Дополнительная информация об ограничениях, их назначении и синтаксисе приведена в главе 7. Смена владельца По умолчанию создатель таблицы автоматически становится ее владельцем. Владелец обладает всеми правами, связанными с таблицей, в том числе правами предоставления и отзыва прав командами GRANT и REVOKE (см. главу 10). Смена владельца производится командой ALTER TABLE с секцией OWNER. Команда имеет следующий синтаксис: ALTER TABLE таблица OWNER TO новый_владелец Пример смены владельца командой ALTER TABLE приведен в листинге 4.13. Новым владельцем таблицы employee назначается пользователь corwin. Листинг 4.13. Смена владельца таблицы booktown=# ALTER TABLE employees booktown-# OWNER TO corwin; ALTER ПРИМЕЧАНИЕ Смена владельца таблицы может осуществляться либо текущим владельцем, либо суперпользователем PostgreSQL. Реструктуризация таблиц Несмотря на возможность включения новых полей в существующую таблицу, следует помнить, что в PostgreSQL (во всяком случае, в версии 7.1.x) не поддерживается удаление полей. Существует два относительно простых способа реструктуризации существующих таблиц. Первый способ основан на использовании команды CREATE TABLE AS, а во втором способе команда CREATE TABLE объединяется с командой INSERT INTO. Фактически оба способа сводятся к созданию новой таблицы требуемой структуры, заполнению ее данными из существующей таблицы и переименованию. Таким образом, новая таблица занимает место старой. ВНИМАНИЕ При реструктуризации таблиц следует помнить, что идентификаторы объектов (OID) изменятся, а старые индексы, сгенерированные для исходной таблицы, не будут автоматически действовать в новых таблицах. Все индексы необходимо удалить и построить заново. Реструктуризация таблиц командой CREATE TABLE AS Распространенная методика реструктуризации таблиц основана на использовании команды CREATE TABLE с секцией AS в сочетании с запросом SQL. Команда создает временную таблицу на основании существующей таблицы, после чего временная таблица переименовывается. Физическое создание новой таблицы может сопровождаться удалением полей и изменением порядка их следования с одновременным заполнением данными из исходной таблицы. В приведенном ниже описании синтаксиса этой усеченной версии команды CREATE TABLE запрос представляет собой команду SELECT для выборки данных, переносимых в новую таблицу. Типы данных всех создаваемых полей определяются типами данных соответствующих полей, выбранных в результате выполнения запроса. CREATE [ TEMPORARY | TEMP ] TABLE таблица [ ( имя_поля [....])] AS запрос Преимущество такого подхода заключается в том, что создание таблицы и заполнение ее данными происходит в одной команде SQL. Самый заметный недостаток — отсутствие полноценных возможностей для установки ограничений в созданной таблице. После того как таблица создана, в нее можно добавлять только ограничения внешних ключей и проверки. После создания новой таблицы старую таблицу можно переименовать (или уничтожить) и присвоить новой таблице имя старой. Допустим, из таблицы books удаляется лишнее поле publication, созданное в пункте «Создание полей» подраздела «Модификация таблицы командой ALTER TABLE». Для этого мы создаем усеченную версию таблицы (с перечислением нужных полей) с указанием соответствующей команды SELECT в секции AS команды CREATE TABLE, после чего старая таблица удаляется командой DROP TABLE (листинг 4.14). Листинг 4.14. Реструктуризация таблицы командой CREATE TABLE AS booktown=# \d books Table "books" Attribute | Type | Modifier id | integer | not null title text I not null authorjd | integer | subjectjd integer j publication j date | Index: books_id_pkey booktown=# CREATE TABLE new_books booktown-# (id, title. authorjd, subjectjd) booktown-# AS SELECT id, title, authorjd, subjectjd booktown-f FROM books; SELECT booktown=# ALTER TABLE books RENAME TO old_books; ALTER booktown=# ALTER TABLE new_books RENAME TO books; ALTER booktown=# \d books Table "books" Attribute | Type | Modifier id | integer | title | text | authorjd j integer j subjectjd | integer | booktown=# DROP TABLE books; DROP ВНИМАНИЕ В PostgreSQL 7.1.x присутствие в команде SELECT необязательного списка полей, заключенного в круглые скобки, исключает использование специального символа *. Недостаток должен быть исправлен в PostgreSQL 7.2. Реструктуризация таблиц командами CREATE TABLE и INSERT INTO Если таблица, создаваемая командой CREATE TABLE AS, вас почему-либо не устраивает (например, если в таблице необходимо установить ограничения полей), то одну команду CREATE TABLE AS можно заменить двумя командами SQL. Сначала команда CREATE TABLE создает новую таблицу, а затем команда INSERT INTO с запросом SELECT заполняет ее данными (листинг 4.15). Листинг 4.15. Реструктуризация таблицы командами CREATE TABLE и INSERT INTO booktownHf CREATE TABLE new_books ( booktown(# id integer UNIQUE, booktown(# title text NOT NULL. booktown(# authorjd integer. booktown(# subjectjd integer. booktown(# CONSTRAINT books_id_pkey PRIMARY КЕУ (id) booktown(# ): NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index ' books Jd_pkey' for table 'new_books' CREATE booktown=# INSERT INTO new_books booktown-# SELECT id, title, author_id, subjectjd booktown-f FROM books; INSERT 0 12 booktown=# ALTER TABLE books RENAME TO old_books; ALTER booktown=# ALTER TABLE new_books RENAME TO books: ALTER booktown=# \d books Table "books" Attribute | Type | Modifier id | integer | not null title ( text I not null authorjd integer j subjectjd integer | Index: booksjd_pkey За дополнительной информацией об использовании команды INSERT INTO с командой SELECT обращайтесь к подразделу «Вставка данных из других таблиц командой SELECT» раздела «Добавление данных командами INSERT и COPY». Команда SELECT рассматривается в разделе «Выборка данных командой SELECT». Удаление таблиц командой DROP TABLE В SQL таблицы удаляются командой DROP TABLE. Команда имеет следующий синтаксис (таблица — имя удаляемой таблицы): DROP TABLE таблица Использование команды DROP TABLE требует осторожности, поскольку удаление таблицы приводит к уничтожению всех хранящихся в ней данных. ПРИМЕЧАНИЕ Уничтожение таблицы с неявно созданным индексом приводит к уничтожению всех связанных с ней индексов.