Изменение определения таблицы (инструкция ALTER TABLE)

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

Как видно из рисунка, инструкция ALTER TABLE может:

- добавить в таблицу определение столбца;

- удалить столбец из таблицы;

- изменить значение по умолчанию, для какого-либо столбца;

- добавить или удалить первичный ключ таблицы;

- добавить или удалить внешний ключ таблицы;

- добавить или удалить условие уникальности;

- добавить или удалить условие на значения

- добавить или удалить ограничение.

В коммерческих СУБД некоторые из рассмотренных возможностей не используются вообще либо используются специфические предложения (так вместо регламентированного стандартом предложения ALTER на синтаксической диаграмме использовано ключевое слово MODIFY, специфичное для ORACLE) либо добавляются совершенно новые возможности изменения таблиц.

Стандарт требует, чтобы инструкция ALTER TABLE применялась для единичного изменения таблицы. Например, для добавления столбца и определения нового внешнего ключа потребуются две различные инструкции. В некоторых СУБД это ограничение ослаблено и допускается присутствие нескольких предложений в одной инструкции ALTER TABLE.

Добавление и удаление столбца. Чаще всего инструкция ALTER TABLE применяется для добавления столбца в существующую таблицу. Предложение с определением столбца в инструкции ALTER TABLE имеет точно такой же вид, как и в инструкции CREATE TABLE. Новое определение добавляется в конец определений столбцов таблицы, и в последующих запросах новый столбец будет крайним справа. СУБД обычно предполагает, что новый столбец во всех существующих строках содержит значения NULL. Если столбец объявлен как NOT NULL WITH DEFAULT, то СУБД считает, что он содержит значения по умолчанию. Нельзя объявлять столбец просто как NOT NULL, поскольку СУБД подставляла бы в существующие строки значения NULL, нарушая тем самым заданное условие. В действительности, когда вы добавляете новый столбец, СУБД не заносит во все существующие строки нового столбца значения NULL или значения по умолчанию. СУБД обнаруживает тот факт, что строка “слишком коротка” для нового определения таблицы, только при выборке этой строки пользователем, и расширяет ее значениями NULL или значениями по умолчанию непосредственно перед выводом на экран или передачей в программу пользователя.

С помощью инструкции ALTER TABLE можно удалить из существующей таблицы один или несколько столбцов, если в них больше нет необходимости. Например, следующая инструкция удаляет столбец position из таблицы staff.

alter table staff

drop position;

Стандарт требует, чтобы одна инструкция ALTER TABLE использовалась для удаления только одного столбца, но в ряде ведущих СУБД такое ограничение снято.

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

Описанные проблемы решаются так же, как и в случае инструкций DELETE и UPDATE – с помощью правила удаления. Можно выбрать одно из двух правил:

RESTRICT: если с удаляемым столбцом связан какой-либо объект в базе данных (внешний ключ, ограничение и т.п.), инструкция ALTER TABLE завершится выдачей сообщения об ошибке и столбец не будет удален;

CASCADE: любой объект базы данных (внешний ключ, ограничение и т.п.), связанный с удаляемым столбцом, также будет удален.

Правило CASCADE может вызвать целую “лавину” изменений, поэтому применять его следует с осторожностью. Лучше указывать правило RESTRICT, а связанные внешние ключи или ограничения обрабатывать с помощью дополнительных инструкций.

Изменение первичных и внешних ключей.Еще одним распространенным случаем применения инструкции ALTER TABLE является изменение или добавление определений первичных и внешних ключей таблицы.

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

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

Пример

alter table staff

add constraint works_in

foreign key (city) references cities;