Изменение определения таблицы (инструкция ALTER TABLE)
Удаление таблицы (инструкция DROP TABLE)
Условия уникальности и ограничения на значения столбцов
Условия уникальности задаются в предложении UNIQUE инструкции CREATE TABLE. Ниже приведена модифицированная инструкция CREATE TABLE для таблицы ORDERS с включенным в нее условием уникальности для столбца ORDER_N:
create table orders (order_n integer not null,
ord_date date not null,
cust_n integer,
sale_n integer,
prod_n varchar2(10),
qty intrger,
amount integer,
unique (order_n),
primary key (order_n),
constraint placed_by foreign key (cust_n) references customers on delete cascade,
Ограничение CHECK (иногда называемое check condition) также задается в инструкции CREATE TABLE. Оно содержит условие на значения (идентично условию отбора в запросе на выборку), проверяемое всякий раз при попытке модификации содержимого таблицы (с помощью инструкций INSERT, UPDATE или DELETE). Если после модификации условие остается истинным, такое изменение допускается; в противном случае СУБД отвергает изменения и выдает сообщение об ошибке. Ниже приведена инструкция CREATE TABLE, создающая таблицу SALESREPS с простым ограничением на значения столбца AGE, позволяющим гарантировать, что возраст принимаемых на работу сотрудников не будет превышать тридцати пяти лет.
create table salesreps (sale_n integer not null,
name varchar2(15),
position varchar2(10),
sex char(1),
salary float(2),
age integer,
unique (sale_n),
primary key (sale_n),
check (age <= 35),
check (sex in (‘m’, ‘f’)),
check (salary <= 500.00));
Помимо ограничения на значения столбца AGE, приведенная выше инструкция содержит ограничения на значения также и для столбцов SEX и SALARY .
С течением времени структура базы данных изменяется. Для представления новых объектов создаются новые таблицы, а некоторые старые таблицы становятся ненужными. Эти ненужные таблицы можно удалить из базы данных посредством инструкции DROP TABLE (рисунок 18)
Рисунок 18 Синтаксическая диаграмма инструкции DROP TABLE
Инструкция содержит имя удаляемой таблицы. Обычно пользователь удаляет одну из своих собственных таблиц и указывает в инструкции простое имя таблицы. Имея соответствующее разрешение, можно также удалить таблицу другого пользователя, но в этом случае необходимо указать полное имя таблицы.
В соответствии со стандартом инструкция DROP TABLE должна включать в себя либо параметр CASCADE, либо RESTRICT, которые определяют, как влияет удаление таблицы на другие объекты базы данных (например, представления), зависящие от этой таблицы. Если задан параметр CASCADE и в базе данных имеются объекты, которые содержат ссылку на удаляемую таблицу, то выполнение инструкции закончится неуспешно. В большинстве коммерческих СУБД допускается применение инструкции DROP TABLE без каких-либо параметров.
Изменения в определении таблицы можно осуществить с помощью инструкции ALTER TABLE, синтаксическая диаграмма которой изображена на рисунке 19. Данная инструкция, как и DROP TABLE, обычно применяется пользователем по отношению к своим собственным таблицам. Однако, имея соответствующее разрешение и используя полное имя таблицы, можно изменять таблицы других пользователей.
Рисунок 19 Синтаксическая диаграмма инструкции ALTER TABLE
Как видно из рисунка, инструкция ALTER TABLE может:
• добавить в таблицу определение столбца;
• удалить столбец из таблицы;
• изменить значение по умолчанию, для какого – либо столбца;
• добавить или удалить первичный ключ таблицы;
• добавить или удалить внешний ключ таблицы;
• добавить или удалить условие уникальности;
• добавить или удалить условие на значения.
Предложения на рисунке 19 изображены в соответствии со стандартом SQL. Во многих СУБД некоторые из них не используются либо используются специфические для конкретной СУБД предложения (вместо регламентированного стандартом предложения 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 из таблицы SALESREPS.
alter table salesreps
drop position;
Стандарт требует, чтобы одна инструкция ALTER TABLE использовалась для удаления только одного столбца, но в ряде ведущих СУБД такое ограничение снято.
Следует учитывать, что операция удаления столбца вызывает те же проблемы целостности данных, которые были описаны ранее на примере обновления таблиц. В частности, при удалении столбца, являющегося первичным ключом в каком-либо отношении, связанные с ним внешние ключи становится недействительными. Похожая проблема возникает, когда удаляется столбец, участвующий в проверке ограничения на значения другого столбца.
Описанные проблемы решены так же, как и в случае инструкций DELETE и UPDATE – с помощью правила удаления. Можно выбрать одно из двух правил:
• RESTRICT: если с удаляемым столбцом связан какой-либо объект в базе данных (внешний ключ, ограничение и т.п.), инструкция ALTER TABLE завершится выдачей сообщения об ошибке и столбец не будет удален;
• CASCADE: любой объект базы данных (внешний ключ, ограничение и т.п.), связанный с удаляемым столбцом, также будет удален.
Правило CASCADE может вызвать целую “лавину” изменений, поэтому применять его следует с осторожностью. Лучше указывать правило RESTRICT, а связанные внешние ключи или ограничения обрабатывать с помощью дополнительных инструкций.
