Использование таблиц

Таблицы являются основными блоками хранения данных в базе. Перед любыми операциями создания, выборки или модификации записей необходимо сначала создать таблицу, в которой эти записи будут храниться.

В этом разделе рассматриваются процедуры создания, модификации и удаления таблиц командами CREATE TABLE, ALTER TABLE и DROP TABLE SQL.

Создание таблицы командой CREATE TABLE

В языке SQL таблицы создаются командой CREATE TABLE.

Минимальный набор параметров включает имя таблицы и описания полей, состоящие из имени поля и типа данных. Команде CREATE TABLE также могут передаваться некоторые необязательные параметры: ограничения полей (правила, которые определяют, какие данные могут или не могут присутствовать в поле) и ограничения таблицы (общие ограничения и связи, определенные для самой таблицы).

Синтаксис команды CREATE TABLE выглядит так:

CREATE [ TEMPORARY | TEMP ] TABLE имя_таблицы (
{ имя_поля тип [ограничение_поля [… ] ] | ограничение_таблицы }
[…]) [ INHERITS (базовая_таблица […])]

Ниже поясняется смысл компонентов команды.

• TEMPORARY | TEMP. Таблица, созданная с ключевым словом TEMPORARY или TEMP, автоматически уничтожается в конце текущего сеанса. Имя временной таблицы может совпадать с именем существующей таблицы; в этом случае все ссылки на таблицу с этим именем будут относиться к временной таблице (до момента ее уничтожения). Все индексы таблицы также являются временными и уничтожаются в конце сеанса.
• Имя_таблицы. Имя для ссылок на таблицу после ее создания.
• {Имя_поля тип [ограничение _поля ] | ограничение таблицы}. После имени таблицы в круглых скобках перечисляются определения полей таблицы, разделенные запятыми. Определение поля состоит из имени, которое представляет собой синтаксически правильный идентификатор допустимого типа, а также необязательного ограничения. Ограничения полей и таблицы могут чередоваться, хотя на практике обычно сначала перечисляются ограничения полей, а затем следуют ограничения таблицы.
• [….]. После определения поля может следовать запятая, за которой указано новое определение. Многоточие означает произвольное количество определений полей (вплоть до максимального значения 1600). Помните, что за последним элементом списка не может следовать запятая, как разрешено в языках типа Perl; это приведет к ошибке лексического разбора.
• [INHERITS (базовая _таблица [….]). Объектные возможности PostgreSQL позволяют задать одну или несколько таблиц (в виде списка, разделенного запятыми), базовых по отношению к создаваемой (производной) таблице. При наличии этого необязательного элемента объявления между таблицами устанавливается связь типа “предок-потомок”.

Примечание. Параметры ограничение_поля и ограничение_таблицы в приведенном выше объявлении могут соответствовать достаточно сложным синтаксическим конструкциям.

Пример создания таблицы

Команда SQL, приведенная в листинге 1, создает таблицу books в базе данных booktown.

Листинг 1. Создание таблицы books.

booktown=# CREATE TABLE books (
 id integer UNIQUE.
 title text NOT NULL,
 author_id integer,
 subject_id integer,
 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 (с указанием имени таблицы) предназначена для вывода структуры таблицы и ее ограничений, если они имеются. В листинге 2 приведены выходные данные команды \d для таблицы books, созданной выше.

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

Листинг 2. Выходные данные команды \d.

booktown=# \d books

Table "books"
Attribute | Type   | Modifier
id        |integer | not null
title     |text    | not null
author_id | integer
subject_id| integer
Index: books_id_pkey

Ниже приведены более подробные описания полей и атрибутов, встречающихся в листинге.

• 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 и хорошо подходит для данного поля, поскольку не требует задания максимального количества символов. Для поля title установлено ограничение NOT NULL; это означает, что поле всегда содержит значение, отличное от NUL.
• author_id. Поле author_id содержит значения типа integer и используется для связи с таблицей authors. Ограничения для этого поля не устанавливаются, поскольку в таблице могут встречаться книги, написанные неизвестным автором, что исключает ограничение NOT NULL. С другой стороны, один автор может написать несколько книг, поэтому ограничение UNIQUE также не подходит.
• subject_id. Поле subject_id аналогично полю author_id оно тоже содержит значения типа integer и используется для установки связи с таблицей subjects. Ограничения для этого поля также отсутствуют, поскольку некоторые книги не принадлежат ни к одной категории, а категории, как правило, содержат более одной книги.

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