Добавление столбцов в таблицу (ядро СУБД)
В этой статье описывается, как добавить новые столбцы в таблицу в SQL Server с помощью SQL Server Management Studio или Transact-SQL.
Замечания
ALTER TABLE С помощью инструкции для добавления столбцов в таблицу эти столбцы автоматически добавляются в конец таблицы.
Если вы хотите, чтобы столбцы в определенном порядке в таблице, необходимо использовать SQL Server Management Studio. Переупорядочивание таблиц не рекомендуется, но дополнительные сведения доступны в статье Изменение порядка столбцов в таблице.
Чтобы запросить существующие столбцы, используйте представление каталога объектов sys.columns.
Разрешения
Требуется разрешение ALTER на таблицу.
Использование SQL Server Management Studio
SQL Server Management Studio (SSMS) не поддерживает все параметры языка определения данных (DDL) в Azure Synapse. Вместо этого используйте скрипты T-SQL.
Вставка столбцов в таблицу с помощью конструктора таблиц
- В обозревателе объектовщелкните правой кнопкой мыши таблицу, в которую необходимо добавить столбцы, и выберите пункт Конструктор.
- Щелкните первую пустую ячейку в столбце Имя столбца.
- Введите имя столбца в ячейку. Имя столбца — значение, которое необходимо указать.
- Нажмите клавишу TAB, чтобы перейти к ячейке типа данных и выбрать тип данных из раскрывающегося списка. Тип данных является обязательным значением и присваивается значение по умолчанию, если вы не выбрали его.
Примечание. Значения по умолчанию можно установить или изменить в диалоговом окне Параметры в разделе Инструменты для баз данных.
Примечание. При создании нового столбца для свойств столбца устанавливаются значения по умолчанию, но их можно изменить на вкладке Свойства столбца .
Использование Transact-SQL
Добавление столбцов в таблицу
В следующем примере добавляются два столбца в таблицу dbo.doc_exa .
ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL, column_c INT NULL ; Связанный контент
- Инструкция ALTER TABLE (Transact-SQL)
- Свойства столбцов (страница «Общие»)
- Создание проверочных ограничений
- Указание значений по умолчанию для столбцов
- Указание вычисляемых столбцов в таблице
- Создание ограничений уникальности
- Индексы
Как добавить столбец в таблицу sql
Возможно, в какой-то момент мы захотим изменить уже имеющуюся таблицу. Например, добавить или удалить столбцы, изменить тип столбцов, добавить или удалить ограничения. То есть потребуется изменить определение таблицы. Для изменения таблиц используется выражение ALTER TABLE .
Общий формальный синтаксис команды выглядит следующим образом:
ALTER TABLE название_таблицы [WITH CHECK | WITH NOCHECK]
Таким образом, с помощью ALTER TABLE мы можем провернуть самые различные сценарии изменения таблицы. Рассмотрим некоторые из них.
Добавление нового столбца
Добавим в таблицу Customers новый столбец Address:
ALTER TABLE Customers ADD Address NVARCHAR(50) NULL;
В данном случае столбец Address имеет тип NVARCHAR и для него определен атрибут NULL. Но что если нам надо добавить столбец, который не должен принимать значения NULL? Если в таблице есть данные, то следующая команда не будет выполнена:
ALTER TABLE Customers ADD Address NVARCHAR(50) NOT NULL;
Поэтому в данном случае решение состоит в установке значения по умолчанию через атрибут DEFAULT:
ALTER TABLE Customers ADD Address NVARCHAR(50) NOT NULL DEFAULT 'Неизвестно';
В этом случае, если в таблице уже есть данные, то для них для столбца Address будет добавлено значение «Неизвестно».
Удаление столбца
Удалим столбец Address из таблицы Customers:
ALTER TABLE Customers DROP COLUMN Address;
Изменение типа столбца
Изменим в таблице Customers тип данных у столбца FirstName на NVARCHAR(200) :
ALTER TABLE Customers ALTER COLUMN FirstName NVARCHAR(200);
Добавление ограничения CHECK
При добавлении ограничений SQL Server автоматически проверяет имеющиеся данные на соответствие добавляемым ограничениям. Если данные не соответствуют ограничениям, то такие ограничения не будут добавлены. Например, установим для столбца Age в таблице Customers ограничение Age > 21.
ALTER TABLE Customers ADD CHECK (Age > 21);
Если в таблице есть строки, в которых в столбце Age есть значения, несоответствующие этому ограничению, то sql-команда завершится с ошибкой. Чтобы избежать подобной проверки на соответствие и все таки добавить ограничение, несмотря на наличие несоответствующих ему данных, используется выражение WITH NOCHECK :
ALTER TABLE Customers WITH NOCHECK ADD CHECK (Age > 21);
По умолчанию используется значение WITH CHECK , которое проверяет на соответствие ограничениям.
Добавление внешнего ключа
Пусть изначально в базе данных будут добавлены две таблицы, никак не связанные:
CREATE TABLE Customers ( Id INT PRIMARY KEY IDENTITY, Age INT DEFAULT 18, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) UNIQUE, Phone VARCHAR(20) UNIQUE ); CREATE TABLE Orders ( Id INT IDENTITY, CustomerId INT, CreatedAt Date );
Добавим ограничение внешнего ключа к столбцу CustomerId таблицы Orders:
ALTER TABLE Orders ADD FOREIGN KEY(CustomerId) REFERENCES Customers(Id);
Добавление первичного ключа
Используя выше определенную таблицу Orders, добавим к ней первичный ключ для столбца Id:
ALTER TABLE Orders ADD PRIMARY KEY (Id);
Добавление ограничений с именами
При добавлении ограничений мы можем указать для них имя, используя оператор CONSTRAINT , после которого указывается имя ограничения:
ALTER TABLE Orders ADD CONSTRAINT PK_Orders_Id PRIMARY KEY (Id), CONSTRAINT FK_Orders_To_Customers FOREIGN KEY(CustomerId) REFERENCES Customers(Id); ALTER TABLE Customers ADD CONSTRAINT CK_Age_Greater_Than_Zero CHECK (Age > 0);
Удаление ограничений
Для удаления ограничений необходимо знать их имя. Если мы точно не знаем имя ограничения, то его можно узнать через SQL Server Management Studio:
Раскрыв узел таблиц в подузле Keys можно увидеть названия ограничений первичного и внешних ключей. Названия ограничений внешних ключей начинаются с «FK». А в подузле Constraints можно найти все ограничения CHECK и DEFAULT. Названия ограничений CHECK начинаются с «CK», а ограничений DEFAULT — с «DF».
Например, как видно на скриншоте в моем случае имя ограничения внешнего ключа в таблице Orders называется «FK_Orders_To_Customers». Поэтому для удаления внешнего ключа я могу использовать следующее выражение:
ALTER TABLE Orders DROP FK_Orders_To_Customers;
Как добавить новый столбец в таблицу между существующими столбцами?
Уже не впервые на форумах встречаю вопрос о том, как добавить новый столбец в определенное место существующей таблицы, скажем, между первым и вторым столбцом. Этот наивный с точки зрения реляционной модели вопрос, тем не менее, имеет некоторый смысл с точки зрения языка SQL.
Я говорю «наивный», поскольку по определению атрибуты отношения не упорядочены, и обращение к значениям атрибута выполняется по его имени, но не по позиции. Что же касается языка SQL, то столбцы в таблице имеют порядок, который задается в операторе CREATE TABLE. Новый же столбец, который добавляется с помощью оператора ALTER TABLE, становится последним в таблице. Т.е. стандарт языка SQL не предусматривает возможности непосредственно добавить столбец в определенную позицию в списке столбцов.
Справедливости ради следует сказать, что некоторые реализации языка SQL расширяют стандарт в этом плане. Например, в MySQL в операторе ALTER TABLE вы можете указать позицию добавляемого столбца (новый столбец может стать первым или после указанного столбца).
Другой вопрос, а зачем это нужно? Мне приходит в голову такой вариант. Скажем, в клиентском приложении для генерации отчетов используется запрос типа
SELECT * FROM Employees ORDER BY last_name, first_name;
Если потребовалось добавить в таблицу Employees дополнительную информацию о сотрудниках, которая логически должна находиться в определенном месте (например, отчество непосредственно между именем и фамилией), то вместо того, чтобы вносить изменения в клиентские приложения, может оказаться проще изменить структуру таблицы Employees.
Итак, имеется таблица Employees, которая создается следующим оператором:
CREATE TABLE Employees( emp_num INT NOT NULL PRIMARY KEY, first_name CHAR(30) NOT NULL, last_name CHAR(30) NOT NULL );
Теперь нам требуется добавить столбец middle_name (отчество) между столбцами first_name и last_name.
В MySQL это можно сделать просто:
ALTER TABLE Employees ADD COLUMN middle_name CHAR(10) NULL AFTER first_name;
В SQL Server так поступить нельзя, но можно использовать следующий алгоритм:
» создание новой таблицы требуемой структуры;
» копирование данных из таблицы Employees в эту новую таблицу;
» удаление таблицы Employees;
» переименование новой таблицы в таблицу с именем Employees.
Ниже приводятся операторы T-SQL, которые реализуют этот алгоритм.
-- Создаем временную таблицу требуемой структуры CREATE TABLE Emp_temp( emp_num INT NOT NULL PRIMARY KEY, first_name CHAR(30) NOT NULL, middle_name CHAR(30) NULL, last_name CHAR(30) NOT NULL ); GO -- Копируем данные из старой таблицы в новую INSERT INTO Emp_temp(emp_num, first_name, last_name) SELECT * FROM Employees; GO -- Удаляем старую таблицу DROP TABLE Employees; GO -- Переименовываем новую EXEC sp_rename 'Emp_temp', 'Employees'; GO
Обратите внимание, что столбец middle_name допускает NULL-значения. Мы не можем добавить столбец в существующую таблицу (или, как в нашем случае, не задавая значения для этого столбца при копировании данных из таблицы Employees в таблицу Emp_temp), если он не имеет значения по умолчанию. Здесь мы принимаем по умолчанию значение NULL.
Мы можем выполнить два первых шага за одно действие с помощью оператора SELECT INTO, который «на лету» создает новую таблицу:
SELECT emp_num, first_name, CAST(NULL AS CHAR(30)), last_name INTO Emp_temp FROM Employees;
Оператор CAST позволяет нам тут же задать требуемый тип добавляемого столбца. Остальные столбцы наследуют типы из таблицы-источника.
Если вы хотите проверить работу последнего скрипта, приведите таблицу в исходное состояние, удалив добавленный ранее столбец:
ALTER TABLE Employees DROP COLUMN middle_name;
Заметим, что при использовании оператора SELECT INTO теряются ключи. Поэтому нам придется добавить ограничение PRIMARY KEY (первичный ключ) либо во временную таблицу, либо уже в переименованную, чтобы получить в точности требуемую структуру:
ALTER TABLE Emp_temp ADD CONSTRAINT emp_PK PRIMARY KEY(emp_num);
Аналогичный алгоритм можно применить и для перестановки уже существующих столбцов. Помимо указанной причины такая перестановка может повысить производительность, связанную с сокращением объема данных, записываемых в журнал транзакций в некоторых реализациях. Это связано со спецификой обработки строк фиксированной и переменной длины. Вот какие рекомендации по этому поводу дает Джо Селко * :
» помещайте первыми нечасто обновляемые столбцы постоянной длины;
» затем помещайте нечасто обновляемые столбцы переменной длины;
» последними помещайте часто обновляемые столбцы;
» ставьте рядом столбцы, которые, как правило, обновляются одновременно.
* Селко Д. Стиль программирования Джо Селко на SQL. — М.: Изд-во «Русская редакция»; СПб.: Питер, 2006
Как добавить новый столбец в таблицу на SQL?
Продолжаем изучать SQL и сегодня мы поговорим о том, как можно добавить новую колонку в уже существующую таблицу с данными. И как обычно рассмотрим примеры реализации этого в разных СУБД.
Очень часто бывает, что нужно добавлять колонки в существующие таблицы, которые уже находятся в работе и в них уже есть данные. Причины такой необходимости могут быть разные, например, добавилась еще одна характеристика и ее нужно хранить именно в этой таблице. Ну, в общем если Вы читаете эту статью, то у Вас возникла такая необходимость и сейчас мы с Вами научимся добавлять новые столбцы в таблицу.
Добавляем колонку в таблицу в PostgreSQL
Сначала начнем разбирать пример на СУБД PostgreSQL. Допустим, у нас есть таблица «users» и в ней уже есть данные:
| id | Name | Fam |
| 1 | User1 | Fam1 |
| 2 | User2 | Fam2 |
| 3 | User3 | Fam3 |
Другими словами у нас в таблице имеется:
- id – это уникальный идентификатор пользователя;
- Name – это имя пользователя;
- Fam – это фамилия пользователя.
У нас возникла необходимость добавить в нашу таблицу еще и отчество пользователя. Обращаю Ваше внимание на то, что перед добавлением новой колонке Вы должны определить, какой тип данных будет в этой колонке. В нашем примере подойдет тип varchar.
Переходим к добавлению столбца:
ALTER TABLE work.users ADD COLUMN otch VARCHAR (20)
- ALTER TABLE – инструкция изменения таблицы;
- users – название нужной таблицы;
- work – схема, в которой расположена таблица;
- add column – инструкция добавления столбца;
- otch – название нашей новой колонки;
- varchar (20) – тип данных, 20 это максимальное количество символов.
Теперь после того, как Вы добавили колонку, можно обновить данные, например, добавим в строку со значением user1 отчество Otch1:
UPDATE work.users SET Otch = 'Otch1' WHERE name = 'User1'
После этого можете проверить данные, выполнив запрос на выборку с помощью нам уже известного оператора SELECT:
SELECT * FROM work.users WHERE name = 'User1'
Вы получите следующий результат:
| id | Name | Fam | Otch |
| 1 | User1 | Fam1 | Otch1 |
Добавляем новую колонку в таблицу в MS SQL Server 2000
Думаю, что с PostgreSQL мы разобрались, а точнее научились добавлять колонки в существующие таблицы.
Теперь попробуем проделать то же самое в MS SQL Server 2000. Все исходные данные допустим, будут такими же.
Здесь запрос практически такой же, но все равно он немного отличается:
ALTER TABLE users ADD otch VARCHAR (20)
Здесь уже название схемы указывать необязательно (по умолчанию таблица будет определена на основе контекста подключения), а также ключевое слово column тоже нет необходимости писать. Давайте также обновим и проверим наши данные:
UPDATE users SET Otch = ' Otch1' WHERE name = 'User1'
SELECT * FROM users WHERE name = 'User1'
Заметка! Всем тем, кто только начинает свое знакомство с языком SQL, рекомендую прочитать книгу «SQL код» – это самоучитель по языку SQL, которую написал я, и в которой я подробно, и в то же время простым языком, рассказываю о языке SQL.
Вот и все! Надеюсь, Вам помог этот небольшой урок по добавлению колонок в существующие таблицы с использованием языка SQL. Удачи!