Изменение таблиц в Microsoft SQL Server или как добавить, удалить, изменить столбец в таблице?

В этом материале я покажу, как вносятся изменения в таблицы в Microsoft SQL Server, под изменениями здесь подразумевается добавление новых столбцов, удаление или изменение характеристик уже существующих столбцов в таблице. По традиции я покажу, как это делается в графическом конструкторе среды SQL Server Management Studio и, конечно же, как это делается на языке T-SQL.

Напомню, в прошлых статьях я показывал, как создаются базы данных в Microsoft SQL Server, а также как создаются новые таблицы. Сегодня Вы узнаете, как изменить уже существующие таблицы в Microsoft SQL Server, при этом, как было уже отмечено, будет рассмотрено два способа изменения таблиц: первый – с помощью SQL Server Management Studio (SSMS), и второй – с помощью T-SQL.

Также я расскажу о некоторых нюансах и проблемах, которые могут возникнуть в процессе изменения таблиц, что, на самом деле, характерно для большинства случаев.

Заметка! Для комплексного изучения языка T-SQL рекомендую посмотреть мои видеокурсы по T-SQL, в которых используется последовательная методика обучения и рассматриваются все конструкции языка SQL и T-SQL.

Исходные данные для примеров

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

• ProductId – идентификатор товара, столбец не может содержать значения NULL, первичный ключ;
• Category – ссылка на категорию товара, столбец не может содержать значения NULL, но имеет значение по умолчанию, например, для случаев, когда товар еще не распределили в необходимую категорию, в этом случае товару будет присвоена категория по умолчанию;
• ProductName – наименование товара, столбец не может содержать значения NULL;
• Price – цена товара, столбец может содержать значения NULL, например, с ценой еще не определились.

Если у Вас нет такой таблицы, то создайте ее и добавьте в нее несколько строк данных, например, следующей инструкцией.

   
   --Создание таблицы с товарами
   CREATE TABLE Goods (
     ProductId    INT IDENTITY(1,1) NOT NULL CONSTRAINT PK_ProductId PRIMARY KEY,
     Category     INT NOT NULL DEFAULT (1),
     ProductName  VARCHAR(100) NOT NULL,
     Price        MONEY NULL,
   );
   GO
   --Добавление строк в таблицу
   INSERT INTO Goods(Category, ProductName, Price)
     VALUES (1, 'Системный блок', 300),
            (1, 'Монитор', 200),
            (2, 'Клавиатура', 100);
   GO
   --Выборка данных
   SELECT * FROM Goods;

Данные мы добавили инструкцией INSERT INTO языка T-SQL.

Примечание! В качестве сервера у меня выступает версия Microsoft SQL Server 2017 Express, как ее установить, можете посмотреть в моей видео-инструкции.

Итак, давайте начнем.

Изменение таблиц в конструкторе SQL Server Management Studio

Сначала я покажу, как изменяются таблицы с помощью графического интерфейса SQL Server Management Studio, а изменяются они точно так же, как и создаются, с помощью того же самого конструктора.

Чтобы открыть конструктор таблиц в среде SQL Server Management Studio, необходимо в обозревателе объектов найти нужную таблицу и щелкнуть по ней правой кнопкой мыши, и выбрать пункт «Проект». Увидеть список таблиц можно в контейнере «Базы данных -> Нужная база данных -> Таблицы».

В итоге откроется конструктор таблиц, где Вы можете добавлять, удалять или изменять столбцы таблицы.

Важно! При работе в конструкторе с таблицей, в которой есть данные, обязательно стоит учитывать один очень важный момент, большинство изменений внести не получится, например, изменить свойства столбцов. Это связано с тем, что по умолчанию в конструкторе «Запрещено сохранение изменений, требующих повторного создания таблицы», именно так и называется параметр, который по умолчанию включён, за счет чего все соответствующие изменения будут блокироваться и, при попытке сохранить такие изменения, Вы будете получать, например, ошибки следующего характера

В случае если Вы работаете исключительно в конструкторе (если делать все то же самое с помощью T-SQL, то такая ошибка возникать не будет) и четко уверены в своих действиях, то этот параметр можно отключить. Для этого зайдите в меню «Сервис -> Параметры» и в разделе «Конструкторы -> Конструкторы таблиц и баз данных» снимите соответствующую галочку.

После чего данное ограничение будет снято, и Вы сможете вносить изменения в таблицы с помощью конструктора. При сохранении таблицы ошибок возникать уже не будет.

Как работать с конструктором, я думаю, понятно, например, для добавления нового столбца просто пишем название столбца в новую строку, выбираем тип данных и указываем признак, может ли данный столбец хранить значения NULL. Для сохранения изменений нажимаем сочетание клавиш «Ctrl+S» или на панели инструментов нажимаем кнопку «Сохранить» (также кнопка «сохранить» доступна и в меню «Файл», и в контекстном меню самой вкладки конструктора).

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

Важно!

Во всех случаях, т.е. не важно с помощью конструктора или с помощью языка T-SQL, когда Вы будете вносить изменения в таблицы, в которых уже есть данные, важно понимать и знать, как эти изменения отразятся на существующих данных, и можно ли вообще применить эти изменения к данным.

Например, изменить тип данных можно, только если он явно преобразовывается без потери данных или в столбце нет данных вообще. Допустим, если в столбце с типом данных VARCHAR(100) есть данные, при этом максимальная длина фактических данных в столбце, к примеру, 80 символов, то изменить тип данных, без потери данных можно только в сторону увеличения или уменьшения до 80 символов (VARCHAR(80)).

Также если в столбце есть данные, при этом он может принимать значение NULL, а Вы хотите сделать его обязательным, т.е. задать свойство NOT NULL, Вам сначала нужно проставить всем записям, в которых есть NULL, значение, например, то, которое будет использоваться по умолчанию, или уже более детально провести анализ для корректной простановки значений.

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

Изменение таблиц в Microsoft SQL Server на языке T-SQL (ALTER TABLE)

Теперь давайте я покажу, как изменять таблицы в Microsoft SQL Server на T-SQL. Все изменения в таблицы вносятся с помощью инструкции ALTER TABLE. Для начала давайте рассмотрим упрощённый синтаксис инструкции ALTER TABLE, чтобы Вы лучше понимали структуру тех запросов, которые мы будем рассматривать далее в примерах.

Упрощенный синтаксис инструкции ALTER TABLE

   
   ALTER TABLE [Название таблицы] [Тип изменения] [Название столбца] [Тип данных] 
               [Возможность принятия значения NULL]

Добавление нового столбца в таблицу на T-SQL

Чтобы добавить новый столбец в таблицу, мы пишем инструкцию ALTER TABLE с параметром ADD, указываем название нового столбца (в нашем случае ProductDescription, т.е. описание товара), его тип данных и возможность принятия значения NULL (как было уже отмечено ранее, если в таблице есть строки, то сначала столбец должен принимать значения NULL).

   
   ALTER TABLE Goods ADD ProductDescription VARCHAR(300) NULL;

   GO

   SELECT * FROM Goods;

Удаление столбца из таблицы на T-SQL

Если Вам столбец не нужен, то его легко удалить (если он не участвует ни в каких связях) параметром DROP COLUMN, например, мы передумали добавлять новый столбец с описанием товара, и чтобы его удалить, пишем следующую инструкцию.

   
   ALTER TABLE Goods DROP COLUMN ProductDescription;

   GO

   SELECT * FROM Goods;

Задаем свойство NOT NULL для столбца на T-SQL

Если у Вас возникла необходимость сделать столбец обязательным, т.е. задать свойство NOT NULL для столбца, то для этого необходимо использовать параметр ALTER COLUMN, но обязательно помним о том, что в столбце уже должны быть заполнены все строки, т.е. отсутствовать значения NULL.

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

   
   ALTER TABLE Goods ALTER COLUMN Price MONEY NOT NULL;

Изменяем тип данных столбца на T-SQL

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

Допустим, у нас возникла необходимость увеличить длину строки для хранения наименования товара (например, до 200 символов).

   
   ALTER TABLE Goods ALTER COLUMN ProductName VARCHAR(200) NOT NULL;

Видео-инструкция по изменению таблиц в Microsoft SQL Server

У меня на этом все, надеюсь, материал был Вам полезен, пока!