пятница, 11 января 2019 г.

SQL ALTER TABLE

Команда ALTER TABLE используется для добавленияудаления или модификацииколонки в уже существующей таблице.

Команда ALTER TABLE

Команда ALTER TABLE изменяет определение таблицы одним из следующих способов:
  • добавляет столбец
  • добавляет ограничение целостности
  • переопределяет столбец (тип данных, размер, умалчиваемое значение)
  • удаляет столбец
  • модифицирует характеристики памяти или иные параметры
  • включает, выключает или удаляет ограничение целостности или триггер.
Условие: Таблица должна быть в схеме пользователя, или пользователь должен иметь системную привилегию ALTER ANY TABLE.
Добавляя столбец с ограничением NOT NULL, разработчик или администратор БД должны учесть ряд обстоятельств. Сначала нужно создать столбец без ограничения, а затем ввести значения во все его строки. После того как все значения столбца станут не NULL-значениями, к нему можно применить ограничение NOT NULL. Если столбец с ограничением NOT NULL пытается добавить пользователь, возвращается сообщение об ошибке, говорящее о том, что либо таблица должна быть пустой, либо в столбце должны содержаться значения для каждой существующей строки (напомним, что после наложения на столбец ограничения NOT NULL в нем не могут присутствовать NULL-значения ни в одной из существующих строк). В СУБД Oracle, начиная с версии 8i, можно удалять столбцы.
Изменяя типы данных существующих столбцов или добавляя столбцы в таблицу базы данных, нужно соблюдать ряд условий. Общепринято, что увеличение – это хорошо, а уменьшение, как правило, — не очень. Допустимые увеличения:
  • Увеличение размера столбца CHAR или VARCHAR2
  • Увеличение размера столбца NUMBER
  • Добавление новых столбцов в таблицу
Уменьшение различных характеристик таблицы, в том числе некоторых типов данных столбцов и реального числа столбцов таблицы, требует особых действий. Часто перед внесением изменения нужно убедиться в том, что в соответствующем столбце или столбцах все значения являются NULL-значениями. Для выполнения подобных операций над столбцами таблицы, содержащими данные, разработчик должен найти или создать какую-то область для временного хранения этих данных. Например, создать таблицу с помощью команды CREATE TABLE AS SELECT, в которой извлекаются данные из первичного ключа и изменяемого столбца или столбцов. Допустимые изменения:
  • Уменьшение размера столбца NUMBER (только при пустом столбце для всех строк)
  • Уменьшение размера столбца CHAR или VARCHAR2 (только при пустом столбце для всех строк)
  • Изменение типа данных столбца (только при пустом столбце для всех строк)

ALTER TABLE Пример 1

Добавление столбца в таблицу:
ALTER TABLE t1(pole1 char(10));

ALTER TABLE Пример 2

Изменение размера столбца таблицы:
ALTER TABLE t1 MODIFY (pole1 char(20));

ALTER TABLE Пример 3

Удаление столбца таблицы:
ALTER TABLE t1 DROP COLUMN pole1;
С помощью команды ALTER TABLE можно изменить имя таблицы без реального переноса физической информации в БД:
ALTER TABLE t1 RENAME TO t2;
Аналогичную операцию можно выполнить с помощью команды RENAME:
RENAME t1 TO t2;
Ограничения целостности столбцов и таблиц БД можно изменять, а также запрещать, разрешать и удалять. Это дает разработчику возможность создавать, модифицировать и удалять бизнес-правила, ограничивающие данные. Рассмотрим добавление ограничений в БД. Простота или сложность этого процесса зависит от определенных обстоятельств. Если вместе с БД создать ограничение нельзя, проще всего добавить его перед вводом данных:

ALTER TABLE Пример 4

Модификация структуры таблицы
ALTER TABLE t1 MODIFY (pole1 NOT NULL);
CREATE TABLE t2
(pole1 CHAR(10) PRIMARY KEY);
ALTER TABLE t1 ADD
(CONSTRAINT fk_t1 FOREIGN KEY (pole1)
REFERENCES t2 (pole1));
ALTER TABLE t1 ADD (UNIQUE (p_name));
ALTER TABLE t1 ADD (p_size CHAR(4) CHECK
(p_size IN (‘P’,’S’,’M’,’L’,’XL’,’XXL’,’XXXL’)));
В первой из приведенных выше команд для добавления ограничения NOT NULL для столбца используется конструкция MODIFY, а для добавления всех табличных ограничений целостности других типов – конструкция ADD. Столбец, для которого добавляется ограничение, должен уже существовать в таблице БД; в противном случае создать ограничение не удастся.

ALTER TABLE Пример 5

Для добавления ограничений целостности можно не указывать имя создаваемого ограничения с помощью ключевого слова  CONSTRAINT. В этом случае команда будет выглядеть следующим образом:
ALTER TABLE t1 ADD  FOREIGN KEY (pole1) REFERENCES t2 (pole1);
Существует ряд условий создания ограничений:
  • Первичные ключи: в столбцах не могут содержаться NULL-значения, и все значения должны быть уникальны.
  • Внешние ключи: в тех столбцах других таблиц, на которые производятся ссылки, должны содержаться значения, соответствующие всем значениям ссылающихся столбцов, либо значения этих последних должны быть NULL-значениями.
  • Ограничения UNIQUE: все значения столбцов должны быть уникальными или NULL-значениями.
  • Ограничения CHECK: новое ограничение будет применяться только по отношению к данным, добавляемым или модифицируемым после его создания.
  • NOT NULL: NULL-значения в столбцах запрещены.
Ограничения можно разрешать и запрещать. Разрешенное ограничение выполняет свои функции, реализуя бизнес-правила по отношению к вводимым в таблицу данным, а запрещенное ограничение переводится в разряд недействующих, как если бы оно было удалено, и его правила не реализуются.

ALTER TABLE Пример 6

Запрещение ограничений:
ALTER TABLE t1 DISABLE PRIMARY KEY;
ALTER TABLE t1 DISABLE UNIQUE (p_name);

ALTER TABLE Пример 7

В некоторых случаях запрещение первичного ключа, от которого зависят внешние ключи, может вызвать определенные сложности, например:
ALTER TABLE t2 DISABLE PRIMARY KEY;
Error at line 1: Cannot disable constraint …. – dependencies exist (невозможно запретить ограничение – существуют зависимости)
Для удаления первичного ключа при наличии зависящих от него внешних ключей в команде ALTER TABLE DISABLE <ограничения> обязательна конструкция CASCADE:
ALTER TABLE t2 DISABLE PRIMARY KEY CASCADE;

ALTER TABLE Пример 8

Запрещенное ограничение разрешается следующим образом:
ALTER TABLE t1 ENABLE PRIMARY KEY;
ALTER TABLE t1 ENABLE UNIQUE (p_name);
Разрешить можно только те ограничения, которые были установлены ранее, а в данный момент запрещены.
Ограничение, процесс создания которого завершился неудачей, не будет существовать в запрещенном виде, ожидая своего разрешения после устранения ошибки. Как правило, владелец таблицы или тот, кому предоставлены соответствующие права, может удалить ограничение:
ALTER TABLE t1 DROP UNIQUE (p_name);