02.10.20 в 16:18

ALTER TABLE — изменение таблицы в SQL

Базы данных → Полезные материалы по базам данных

Теги: ms sql server, alter table

Команда ALTER TABLE применяется в SQL при добавлении, удалении либо модификации колонки в существующей таблице. В этой статье будет рассмотрен синтаксис и примеры использования ALTER TABLE на примере MS SQL Server.

SQL-оператор ALTER TABLE способен менять определение таблицы несколькими способами: • добавлением/переопределением/удалением столбца (column); • модифицированием характеристик памяти; • включением, выключением либо удалением ограничения целостности.

При этом пользователю нужно обладать системной привилегией ALTER ANY TABLE либо таблица должна находиться в схеме пользователя.

Меняя типы данных существующих columns либо добавляя их в БД-таблицу, следует соблюдать некоторые условия. Принято, что увеличение есть хорошо, а уменьшение — не очень. Существует ряд допустимых увеличений: • добавляем новые столбцы в таблицу; • увеличиваем размер столбца CHAR либо VARCHAR2; • увеличиваем размер столбца NUMBER.

Нередко перед внесением изменений следует удостовериться, что в соответствующих columns все значения — это NULL-значения. Если выполняется операция над столбцами, которые содержат данные, следует найти либо создать область временного хранения данных. Можно создать таблицу посредством CREATE TABLE AS SELECT, где извлекаются данные из первичного ключа и изменяемых columns. Существует ряд допустимых изменений: • уменьшаем размер столбца NUMBER (лишь при наличии пустого column для всех строк); • уменьшаем размер столбца CHAR либо VARCHAR2 (лишь при наличии пустого column для всех строк); • меняем тип данных столбца (аналогично, что и в первых двух пунктах).

При добавлении column с ограничением NOT NULL, администратор баз данных либо разработчик обязан учесть некоторые обстоятельства. Вначале следует создать столбец без ограничения, потом ввести значения во все строки. Далее, когда значения column будут уже не NULL, к нему можно будет применить ограничение NOT NULL. Но если column с ограничением NOT NULL хочет добавить юзер, то вернётся сообщение об ошибке, судя по которому таблица должна быть либо пустой, либо содержать в столбце значения для каждой имеющейся строки (после наложения на column NOT NULL-ограничения, в нём не смогут присутствовать значения NULL ни в одной из имеющихся строк).

Синтаксис ALTER TABLE на примере MS SQL Server

Рассмотрим общий формальный синтаксис на примере SQL Server от Microsoft:

    ALTER TABLE имя_таблицы [WITH CHECK | WITH NOCHECK]
{ ADD имя_столбца тип_данных_столбца [атрибуты_столбца] | 
  DROP COLUMN имя_столбца |
  ALTER COLUMN имя_столбца тип_данных_столбца [NULL|NOT NULL] |
  ADD [CONSTRAINT] определение_ограничения |
  DROP [CONSTRAINT] имя_ограничения}

Итак, используя SQL-оператор ALTER TABLE, мы сможем выполнить разные сценарии изменения таблицы. Далее будут рассмотрены некоторые из этих сценариев.

Добавляем новый столбец

Для примера добавим новый column Address в таблицу Customers:

    ALTER TABLE Customers
ADD Address NVARCHAR(50) NULL;

В примере выше столбец Address имеет тип NVARCHAR, плюс для него определён NULL-атрибут. Если же в таблице уже существуют данные, команда ALTER TABLE не выполнится. Однако если надо добавить столбец, который не должен принимать NULL-значения, можно установить значение по умолчанию, используя атрибут DEFAULT:

    ALTER TABLE Customers
ADD Address NVARCHAR(50) NOT NULL DEFAULT 'Неизвестно';

Тогда, если в таблице существуют данные, для них для column Address добавится значение "Неизвестно".

Удаляем столбец

Теперь можно удалить column Address:

    ALTER TABLE Customers
DROP COLUMN Address;

Меняем тип

Продолжим манипуляции с таблицей Customers: теперь давайте поменяем тип данных столбца FirstName на NVARCHAR(200).

    ALTER TABLE Customers
ALTER COLUMN FirstName NVARCHAR(200);

Добавляем ограничения CHECK

Если добавлять ограничения, SQL Server автоматически проверит существующие данные на предмет их соответствия добавляемым ограничениям. В случае несоответствия, они не добавятся. Давайте ограничим Age по возрасту.

    ALTER TABLE Customers
ADD CHECK (Age > 21);

При наличии в таблице строк со значениями, которые не соответствуют ограничению, sql-команда не выполнится. Если надо избежать проверки и добавить ограничение всё равно, используют выражение WITH NOCHECK:

    ALTER TABLE Customers WITH NOCHECK
ADD CHECK (Age > 21);

По дефолту применяется значение WITH CHECK, проверяющее на соответствие ограничениям.

Добавляем внешний ключ

Представим, что изначально в базу данных будут добавлены 2 таблицы, которые между собой не связаны:

Теперь добавим к столбцу CustomerId ограничение внешнего ключа (таблица Orders):

    ALTER TABLE Orders
ADD FOREIGN KEY(CustomerId) REFERENCES Customers(Id);

Добавляем первичный ключ

Применяя определенную выше таблицу Orders, можно добавить к ней для столбца Id первичный ключ:

    ALTER TABLE Orders
ADD PRIMARY KEY (Id);

Добавляем ограничения с именами

Добавляя ограничения, можно указать имя для них — для этого пригодится оператор CONSTRAINT (имя прописывается после него):

Удаляем ограничения

Чтобы удалить ограничения, следует знать их имя. Если с этим проблема, имя всегда можно определить с помощью SQL Server Management Studio:

Следует раскрыть в подузле Keys узел таблиц, где находятся названия ограничений для внешних ключей (названия начинаются с «FK»). Обнаружить все ограничения DEFAULT (названия начинаются с «DF») и CHECK («СК») можно в подузле Constraints.

Из скриншота видно, что в данной ситуации имя ограничения внешнего ключа (таблица Orders) имеет название "FK_Orders_To_Customers". Здесь для удаления внешнего подойдёт такое выражение:

    ALTER TABLE Orders
DROP FK_Orders_To_Customers;

Хотите знать про SQL Server больше? Добро пожаловать на курс "MS SQL Server Developer" в OTUS! Также вас может заинтересовать общий курс по работе с реляционными и нереляционными БД:

Источники: • https://metanit.com/sql/sqlserver/3.6.php; • https://sql-language.ru/alter-table.html.

MLOps