ITT1504: Базы данных

1234567

Материал предоставлен http://it.rfet.ru

Модификация таблицы командой 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 для хранения даты публикации. Листинг 3 показывает, как это делается.

Листинг 3. Добавление поля. 

booktown=# ALTER TABLE books ADD publication date;
ALTER

booktown=# \d books

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

Листинг 3 показывает, что в таблице books появилось новое поле с именем publication и типом date. Кроме того, он дает типичный пример плохой координации планирования между разработчиками: в базе данных booktown из нашего примера дата публикации уже хранится в таблице editions, поэтому включать его в таблицу books не нужно. Изменение структуры таблиц после подобных ошибок рассматривается ниже в подразделе “Реструктуризация таблиц”.

Назначение и отмена значений по умолчанию

При модификации существующих таблиц наибольшая свобода действий предоставляется при выборе значений по умолчанию. Программист может относительно легко назначать и отменять эти значения для отдельных полей, для чего используется команда ALTER TABLE с секцией ALTER COLUMN.

В PostgreSQL команда ALTER TABLE, назначающая или отменяющая значение по умолчанию для поля имя_поля, имеет следующий синтаксис:

ALTER TABLE таблица
ALTER [ COLUMN ] имя_поля
{SET DEFAULT значение ] DROP DEFAULT }

Как и в предыдущем разделе, ключевое слово COLUMN является необязательным включается в команду лишь для наглядности. В листинге 4 приведен пример назначения и отмены простой последовательности значений по умолчанию для поля id таблицы books.

Листинг 4. Изменение значений по умолчанию. 

booktown=# ALTER TABLE books ALTER COLUMN id SET DEFAULT nextval ('books.ids');
ALTER

booktown=# \d books

TABLE "books"
Attribute | Type | Modifier
id        | integer not null default nextval('books.ids'::text)
title     | text not null
author_id | integer;
subject_id| integer

index: books_id_pkey

booktown=# ALTER TABLE books ALTER id DROP DEFAULT;
ALTER

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

Переименование таблицы

Переименование таблиц осуществляется командой ALTER TABLE с секцией RENAME, синтаксис переименования таблицы:

ALTER TABLE таблица
RENAME TO новое_имя

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

Листинг 5. Переименование таблицы. 

booktown=# ALTER TABLE books RENAME TO literature;
ALTER
booktown=# ALTER TABLE literature RENAME TO books;
ALTER

Переименование полей

PostgreSQL позволяет изменять имена полей без изменения данных, хранящихся таблице. Впрочем, переименование полей – дело рискованное, поскольку существующие приложения могут содержать ссылки на имена полей. Если программа обращается к полю по имени, то переименование может нарушить ее работоспособность.

Команда переименования полей имеет следующий синтаксис:

ALTER TABLE таблица
RENAME [ COLUMN ] имя_поля ТО новое_имя_поля

Как и в других командах ALTER TABLE, ключевое слово COLUMN является необязательным. По двум идентификаторам, разделенным ключевым словом ТО, PostgreSQL может определить, что команда переименования относится к одному полю, а не таблице. Пример переименования полей приведен в листинге 6.

Листинг 6. Переименование поля. 

booktown=# \d daily_inventory

Table "daily_inventory"

Attribute | Type | Modifier
isbn      | text
in_stock  | boolean |

booktown=# ALTER TABLE daily_inventory RENAME COLUMN in_stock TO is_in_stock;
ALTER

booktown=# ALTER TABLE daily_inventor RENAME COLUMN is_in_stock TO is_stocked;
ALTER

Добавление ограничений

После создания таблицы сохраняются некоторые возможности добавления ограничении. В PostgreSQL команда ALTER TABLE с секцией ADD CONSTRAINT позволяет определять для полей существующих таблиц только ограничения внешнего ключа и проверки. Команда создания новых ограничений имеет следующий синтаксис:

ALTER TABLE таблице
ADD CONSTRAINT имя_ограничения определение

Синтаксис определения зависит от типа ограничения. В листинге 7 продемонстрирован синтаксис создания ограничения внешнего ключа для таблицы editions (связанной с полем id таблицы books) и ограничения проверки для поля type.

Листинг 7. Создание новых ограничений в существующей таблице. 

booktown=# ALTER TABLE editions ADD CONSTRAINT foreign_book 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 ADD CONSTRAINT hard_or_paper_back CHECK (type = 'p' OR type = 'h');
ALTER

Установка ограничения внешнего ключа приводит к тому, что любое значение book_id в таблице editions также должно существовать в таблице books. Кроме того, вследствие установленного ограничения проверки поле type в таблице editions может содержать только значения р или b.

Примечание. Ограничение уникальности также неявно устанавливается при создании уникального индекса командой CREATE INDEX.

Дополнительная информация об ограничениях, их назначении и синтаксисе будет приведена далее в этом курсе.

Использование таблицРеструктуризация таблиц