ALTER TABLE SQL Server
В этом учебном пособии вы узнаете, как использовать оператор ALTER TABLE в SQL Server (Transact-SQL) для добавления столбца, изменения столбца, удаления столбца, переименования столбца или переименования таблицы с синтаксисом и примерами.
Описание
Оператор ALTER TABLE SQL Server (Transact-SQL) используется для добавления, изменения или удаления столбцов в таблице.
Добавить столбец в таблицу.
Вы можете использовать оператор ALTER TABLE в SQL Server, чтобы добавить столбец в таблицу.
Синтаксис
Синтаксис добавления столбца в таблицу в SQL Server (Transact-SQL):
ALTER TABLE table_name
ADD column_name column-definition;
Пример
Рассмотрим пример, который показывает, как добавить столбец в таблицу SQL Server с помощью оператора ALTER TABLE.
Например:
Transact-SQL
ALTER TABLE employees
ADD last_name VARCHAR ( 50 );
Этот пример SQL Server ALTER TABLE добавит столбец в таблицу employees , с наименованием last_name .
Добавить несколько столбцов в таблицу
Вы можете использовать оператор ALTER TABLE в SQL Server для добавления нескольких столбцов в таблицу.
Синтаксис
Синтаксис добавления нескольких столбцов в существующую таблицу в SQL Server (Transact-SQL):
ALTER TABLE table_name
ADD column_1 column-definition,
column_2 column-definition,
.
column_n column_definition;
Пример
Рассмотрим пример, который показывает, как добавить несколько столбцов в таблицу в SQL Server с помощью оператора ALTER TABLE.
Например:
Transact-SQL
ALTER TABLE employees
ADD last_name VARCHAR ( 50 ),
first_name VARCHAR ( 40 );
Этот пример SQL Server ALTER TABLE добавит в таблицу employees два столбца, поле last_name как VARCHAR (50) и поле first_name как VARCHAR (40).
Изменить столбец в таблице
Вы можете использовать оператор ALTER TABLE в SQL Server для изменения столбца в таблице.
Синтаксис
Синтаксис изменения столбца в существующей таблице в SQL Server (Transact-SQL):
ALTER TABLE table_name
ALTER COLUMN column_name column_type;
Пример
Рассмотрим пример, который показывает, как изменить столбец в таблице SQL Server с помощью оператора ALTER TABLE.
Например:
Transact-SQL
ALTER TABLE employees
ALTER COLUMN last_name VARCHAR ( 75 ) NOT NULL ;
Этот пример SQL Server ALTER TABLE изменит столбец с именем last_name как тип данных VARCHAR (75) и принудит столбец не допускать нулевые значения.
Удалить столбец из таблицы
Вы можете использовать оператор ALTER TABLE в SQL Server для удаления столбца из таблицы.
Синтаксис
Синтаксис удаления столбца в существующей таблице в SQL Server (Transact-SQL):
ALTER TABLE table_name
DROP COLUMN column_name;
Пример
Рассмотрим пример, показывающий, как удалить столбец из таблицы на SQL Server с помощью оператора ALTER TABLE.
Например:
Transact-SQL
ALTER TABLE employees
DROP COLUMN last_name ;
Этот пример SQL Server ALTER TABLE удалит столбец с именем last_name из таблицы, называемой employee .
Переименовать столбец в таблице
Вы не можете использовать оператор ALTER TABLE в SQL Server для переименования столбца в таблице. Тем не менее, вы можете использовать sp_rename , хотя Microsoft рекомендует удалять и воссоздавать таблицу, чтобы скрипты и хранимые процедуры не были нарушены.
Синтаксис
Синтаксис переименования столбца в существующей таблице в SQL Server (Transact-SQL):
sp_rename ‘table_name.old_column_name’, ‘new_column_name’, ‘COLUMN’;
Пример
Рассмотрим пример, который показывает, как переименовать столбец в таблице на SQL Server, используя sp_rename .
Например:
Добавление столбцов в набор данных
Добавляя столбцы, можно разработать собственные наборы данных. Например, можно создать новый столбец, объединяющий столбцы ADDRESS_LINE_1, ADDRESS_LINE_2 и ADDRESS_LINE_3.
- На главной странице выберите набор данных и в меню Действия выберите Открыть . На диаграмме данных или диаграмме объединения правой кнопкой мыши нажмите имя источника данных и нажмите Открыть , чтобы отобразить редактор преобразований.
- В редакторе преобразований нажмите Добавить этап подготовки .
- В разделе Создание запросов введите имя столбца и в поле «Выражение» определите выражение для добавления значений в столбец. Например, чтобы объединить значения в столбцах Категория и Подкатегория , можно указать выражение: CONCAT(Category, Sub Category) .
Используйте средство выбора функций f(x) для построения выражения с использованием операторов, математических, строковых функций и функций преобразования.
Oracle Analytics добавляет шаг на панель Сценарий подготовки.
Если вы работаете с одним набором данных таблицы или набором данных на основе файлов, нажмите Применить сценарий .
Изменение порядка столбцов в таблице Oracle
Как правило это не является проблемой. С помощью SQL можно извлечь столбцы в любом порядке.
Если возникла потребность добавить столбец в таблицу в определенном порядке или поменять местами столбцы, то есть несколько способов:
1. Через создание промежуточной таблицы с переименование. После пересоздать все индексы и констрейны.
3. Через пакет DBMS_REDEFINITION
Нужны следующие привилегии:
# execute access on the dbms_redefinition package
# create any table # alter any table
# drop any table
# lock any table
# select any table
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);
Разрешить можно только те ограничения, которые были установлены ранее, а в данный момент запрещены. Ограничение, процесс создания которого завершился неудачей, не будет существовать в запрещенном виде, ожидая своего разрешения после устранения ошибки. Как правило, владелец таблицы или тот, кому предоставлены соответствующие права, может удалить ограничение: