7.3.2. Изменение структуры таблицы – команда ALTERTABLE

 

Как бы тщательно ни планировалась структура таблицы, иногда возникает необходимость внести в нее некоторые изменения. Предположим, что в уже сформированную таблицу «Преподаватели» необходимо добавить номер домашнего телефона и домашний адрес. Эту операцию можно выполнять различными путями. Например, можно удалить таблицу со старой структурой и создать вместо нее новую таблицу с нужной структурой. Недостатком этого метода является то, что необходимо будет куда-то скопировать имеющиеся в таблице данные и переписать их в новую таблицу после ее создания.

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

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

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

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

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

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

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

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

 

Рассмотрим обобщенный синтаксис команды ALTERTABLE:

 

ALTERTABLE <имя_таблицы>

[ALTERCOLUMN <имя_столбца> [SETDEFAULT <выражение>]|

[DROPDEFAULT]]

|[ADD <определение_столбца>]

|[DROP COLUMN <имя_столбца> [CASCADE]|[RESTRICT]]

|[ADD[<определние_первичного_ключа>]|[<определение_внешнего_ключа>]|

[<условие_уникальности>]|[<условие_на_значение>]]

|[DROP CONSTRAINT <имя_ограничения> [CASCADE]|[RESTRICT]]

 

Команда ALTERTABLE берет на себя все действия по копированию данных во временную таблицу, удалению старой таблицы, созданию вместо нее новой таблицы с нужной структурой и последующим переписыванием в нее данных.

Назначение многих параметров и ключевых слов команды ALTERTABLE аналогично назначению соответствующих параметров и ключевых слов команды CREATETABLE (например, синтаксис конструкции <определение_столбца> совпадает с синтаксисом аналогичной конструкции команды CREATETABLE).

Основные режимы использования команды ALTERTABLE следующие:

добавление столбца;

удаление столбца;

модификация столбца;

изменение, добавление и удаление ограничений (первичных и внешних ключей, значений по умолчанию).

 

Добавление столбца

 

Для добавления нового столбца следует использовать ключевое слово ADD, после которого должно стоять определение столбца.

Добавим, например, в таблицу «Студенты» столбец «Год_поступления» следующим образом:

 

ALTER TABLEСтуденты

ADDГод_поступленияINTEGER NOT NULL DEFAULT YEAR(GETDATE())

 

После выполнения этой команды в структуру таблицы «Студент» будет добавлен еще один столбец со значением по умолчанию, равным текущему году (значение по умолчанию вычисляется с помощью двух встроенных функций - YEAR() и GETDATE()).

 

Модификация столбца

 

Для модификации существующего столбца таблицы служит ключевое слово ALTERCOLUMN. Изменение свойств столбца невозможно, если:

столбец участвует в ограничениях PRIMARYKEY или FOREIGNKEY;

на столбец наложены ограничения целостности CHECK или UNIQUE (исключение составляют столбцы, имеющие тип данных переменной длины, т.е. типы данных, начинающиеся на var);

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

Определяя для столбца новый тип данных, следует помнить о том, что старый тип данных должен конвертироваться в новый.

Пример модификации столбца «Номер_группы» таблицы «Студенты» (тип данных INTEGER заменяется на CHAR):

 

ALTER TABLE Студенты

ALTER COLUMN Номер_группы CHAR(6) NOT NULL

Удаление столбца

 

Для удаления столбца из таблицы используется предложение DROPCOLUMN <имя_столбца>. При удалении столбцов следует учитывать, что нельзя удалять столбцы с  ограниче­ниями целостности CHECK, FOREIGNKEY, UNIQUE или PRIMARYKEY, а также столбцы, для которых определены значения по умолчанию (в виде ограничения целостно­сти на уровне столбца или на уровне таблицы).

Рассмотрим, например, команду удаления из таблицы «Студент» столбца «Год_поступления»:

ALTER TABLE Студенты

DROP COLUMN Год_поступления

 

Эта команда выполнена не будет, т.т. при добавлении столбца было определено значение по умолчанию.

 

Добавление ограничений на уровне таблицы

 

Для добавления ограничений на уровне таблицы используется предложение ADDCONSTRAINT <имя_ограничения>.

В качестве примера рассмотрим команды добавления внешних ключей в таблицы базы данных «Сессия».

Добавление внешних ключей в таблицу «Учебный_план» (создание связи с именем FK_Дисциплина и связи с именем FK_ Кадровый_состав):

 

ALTER TABLE Учебный_план

ADD CONSTRAINT FK_Дисциплина

FOREIGN KEY (ID_Дисциплина)

REFERENCES Дисциплины

 

ALTER TABLE Учебный_план

ADD CONSTRAINT FK_Кадровый_состав

FOREIGN KEY (ID_Преподаватель)

REFERENCES Кадровый_состав

 

Добавление внешних ключей в таблицу «Сводная_ведомость» (создание связи с именем FK_Студент и связи с именем FK_План):

 

ALTER TABLE Сводная_ведомость

ADD CONSTRAINT FK_Студент

FOREIGN KEY (ID_Студент)

REFERENCES Студенты

 

ALTER TABLE Сводная_ведомость

ADD CONSTRAINT FK_План

FOREIGN KEY (ID_План)

REFERENCES Учебный_план

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

Рассмотрим еще один пример – добавление значения по умолчанию для столбца Номер_группы:

 

ALTER TABLE Студент

ADD CONSTRAINT DEF_Номер_группы DEFAULT 1 FOR Номер_группы

 

В результате выполнения этой команды на уровне таблицы будет создано ограничение целостности с именем DEF_Номер_группы.

 

Удаление ограничений

 

Для удаления из таблицы ограничения целостности используется предложение DROPCONSTRAINT <имя_ограничения>.

Удаление ограничения целостности возможно только в том случае, когда оно поименовано (т.е. предложение <определение_ограничения> содержит именование ограничения CONSTRAINT).

Команда удаления построенного внешнего ключа FK_Дисциплина из таблицы «Учебный_план» выглядит следующим образом:

 

ALTER TABLE Учебный_план

DROP CONSTRAINT FK_Дисциплина

 

Удалить же построенное ограничение DEF_Номер_группы можно с помощью следующей команды:

ALTER TABLE Студент

DROP CONSTRAINT DEF_Номер_группы

 

 

К оглавлению

Назад к разделу "7.3. Управление таблицами"

Вперед к разделу "7.3.3. Удаление таблиц – команда DROP TABLE"