Add column sql: SQL ALTER TABLE Statement

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

Навигация по уроку

• ALTER TABLE для добавления и удаления столбца, значения по умолчанию
• ALTER TABLE для добавления ключей
• ALTER TABLE для добавления и удаления ограничений

Связанные темы

• Создание базы данных
• Основы проектирования баз данных

Команда языка SQL ALTER TABLE предназначена для изменения структуры столбца таблицы базы данных, если
таблица уже существует.

Запрос с командой ALTER TABLE на добавление нового столбца в таблицу имеет следующий синтаксис:

ALTER TABLE ИМЯ_ТАБЛИЦЫ
ADD ИМЯ_НОВОГО_СТОЛБЦА ТИП_ДАННЫХ

Пример 1. Есть база данных фирмы — Company. В ней есть таблица
Employee (Сотрудник). Требуется добавить в эту таблицу новый столбец — SEX (Пол). Соответствующий запрос
выглядит следующим образом:

ALTER TABLE EMPLOYEE
ADD SEX CHAR(1)

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

Пусть теперь требуется установить значение записей в таблице по умолчанию. Запрос с командой ALTER TABLE
для этой цели содержит ещё одну команду — ALTER COLUMN — и имеет следующий синтаксис:

ALTER TABLE ИМЯ_ТАБЛИЦЫ
ALTER COLUMN ИМЯ_СТОЛБЦА
SET DEFAULT ‘ЗНАЧЕНИЕ_ПО_УМОЛЧАНИЮ’

Пример 2. Пусть в фирме, база данных которой имеет название Company,
трудятся в основном женщины. Администратор базы данных решил сократить процесс заполнения данными столбца
SEX (Пол) и установить его значение по умолчанию — ‘F’ (что означает женский пол). Соответствующий запрос
выглядит следующим образом:

ALTER TABLE EMPLOYEE
ALTER COLUMN SEX
SET DEFAULT ‘F’

Теперь только если новый сотрудник — мужчина, потребуется вписывать в столбец SEX
значение ‘M’, а в большинстве случаев значение установится по умолчанию — ‘F’.

При помощи запроса с командой ALTER TABLE можно и удалить столбец из таблицы. Синтаксис команды будет
следующим:

ALTER TABLE ИМЯ_ТАБЛИЦЫ
DROP COLUMN ИМЯ_СТОЛБЦА

Пример 3. Пусть в той же базе данных Company из
таблицы Employee требуется удалить столбец SEX (Пол). Соответствующий запрос
выглядит следующим образом:

ALTER TABLE EMPLOYEE
DROP COLUMN SEX

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

О ключах в таблицах базы данных — на уроке Реляционная модель данных.
Если при создании таблицы базы данных не был определён первичный ключ (PRIMARY KEY), то это может быть
сделано при помощи команды ALTER TABLE. Синтаксис команды в случае простого первичного ключа следующий:

ALTER TABLE ИМЯ_ТАБЛИЦЫ
ADD PRIMARY KEY (ИМЯ_ИДЕНТИФИКАТОРА)

Синтаксис команды в случае составного первичного ключа:

ALTER TABLE ИМЯ_ТАБЛИЦЫ
ADD PRIMARY KEY (ИМЯ_ИДЕНТИФИКАТОРА_1, ИМЯ_ИДЕНТИФИКАТОРА_2)

Пример 4. Работаем всё с той же базой данных Company,
со столбцом EMPLOYEE. Мы можем добавить простой первичный ключ этого столбца в виде идентификатора
сотрудника и сделать это так:

ALTER TABLE EMPLOYEE
ADD PRIMARY KEY (EMP_ID)

Если решено использовать составной ключ, состоящий из идентификатора сотрудника и
идентификатора отдела, в котором сотрудник работает, то используем следующий запрос:

ALTER TABLE EMPLOYEE
ADD PRIMARY KEY (EMP_ID, DEPT_ID)

О том, что такое ограничения целостности, говорилось на уроке Реляционная модель данных.
Если коротко — при удалении какой-либо строки из одной таблицы (1), в другой таблице (2), строка в которой
ссылается на строку таблицы (1), не должно быть ссылок, которые не содержатся в таблице (1), то есть
ссылок, ведущих в никуда.

Команда с выражением ALTER TABLE может понадобиться для установки значения NULL для такой ссылки. Её
синтаксис следующий:

ALTER TABLE ИМЯ_ССЫЛАЮЩЕЙСЯ_ТАБЛИЦЫ
ADD CONSTRAINT ИМЯ_ИДЕНТИФИКАТОРА
FOREIGN KEY (ИМЯ_ИДЕНТИФИКАТОРА)
REFERENCES ИМЯ_ТАБЛИЦЫ_НА_КОТОРУЮ_ССЫЛАЮТСЯ
ON DELETE SET NULL

Пример 5. Пусть в той же базе данных Company
требуется установить ссылочное ограничение: при удалении какого-либо отдела из таблицы Dept
в таблице Employee, содержатся ссылки на эти отделы, значение ссылки (внешнего ключа) должно быть
уставлено в NULL (полностью неопределенное). Соответствующий запрос
выглядит следующим образом:

ALTER TABLE EMPLOYEE
ADD CONSTRAINT DEPT_ID
FOREIGN KEY (DEPT_ID)
REFERENCES DEPT
ON DELETE SET NULL

Таким образом, если в таблице Employee есть сотрудник, работающий в отделе, который
вдруг был ликвидирован и запись о нём удалена из таблицы Dept, то в записи об этом сотруднике значение
столбца DEPT_ID примет значение NULL.

Запросом с командой ALTER TABLE можно также установить запрет на удаление ссылающихся записей из таблицы (1), если
существует хотя бы одна запись в таблице (2), на которую ссылается запись в таблице (1). Синтаксис такого
запроса следующий:

ALTER TABLE ИМЯ_ССЫЛАЮЩЕЙСЯ_ТАБЛИЦЫ
ADD CONSTRAINT ИМЯ_ИДЕНТИФИКАТОРА
FOREIGN KEY (ИМЯ_ИДЕНТИФИКАТОРА)
REFERENCES ИМЯ_ТАБЛИЦЫ_НА_КОТОРУЮ_ССЫЛАЮТСЯ
ON DELETE RESTRICT

Пример 6. Далее работаем с базой данных сети аптек, которую мы создали на уроке Создание базы
данных SQL и таблиц базы данных.

Следующая команда с выражением alter table устанавливает запрет на удаление группы препаратов из таблицы Group, если существует хотя бы один препарат этой группы, что определяется записью в таблице Preparation:

alter table Preparation add constraint GR_ID foreign key(GR_ID) references Group on delete restrict

Устанавливаем запрет на удаление аптеки из таблицы Pharmacy, если существует хотя бы один сотрудник этой аптеки, что определяется записью в таблице Employee:

alter table Employee add constraint PH_ID foreign key(PH_ID) references
Pharmacy on delete restrict

Запросом с командой ALTER TABLE можно также определить, что если удалить запись в таблице (2), на которую
ссылается запись в таблице (1), то должны быть удалены все соответствующие записи в таблице (1) (каскадное
удаление).
Синтаксис такого запроса следующий:

ALTER TABLE ИМЯ_ССЫЛАЮЩЕЙСЯ_ТАБЛИЦЫ
ADD CONSTRAINT ИМЯ_ИДЕНТИФИКАТОРА
FOREIGN KEY (ИМЯ_ИДЕНТИФИКАТОРА)
REFERENCES ИМЯ_ТАБЛИЦЫ_НА_КОТОРУЮ_ССЫЛАЮТСЯ
ON DELETE CASCADE

Пример 7. Продолжаем работать с базой данных сети аптек.

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

alter table AVAILABILITY add constraint PR_ID foreign key(PR_ID)
references PREPARATION on delete cascade

Теперь определим, что если удалить аптеку из таблицы PHARMACY, то должны удалиться все записи этой аптеки в таблице AVAILABILITY:

alter table AVAILABILITY add constraint PH_ID foreign key(PH_ID)
references PHARMACY on delete cascade

Следующая команда модифицирует таблицу DEFICIT и определяет, что если удалить препарат из таблицы PREPARATION, то должны удалиться все записи этого препарата в таблице DEFICIT:

alter table DEFICIT add constraint PR_ID foreign key(PR_ID) references
PREPARATION on delete cascade

iv>

Вновь модифицируем таблицу DEFICIT и определяем, что если удалить аптеку из таблицы PHARMACY, то должны удалиться все записи этой аптеки в таблице DEFICIT:

alter table DEFICIT add constraint PH_ID foreign key(PH_ID) references
PHARMACY on delete cascade

Запросом с командой ALTER TABLE можно установить проверочное ограничение.
Синтаксис такого запроса следующий:

ALTER TABLE ИМЯ_ТАБЛИЦЫ
ADD CONSTRAINT CHECK_DATA
CHECK(ПРОВЕРЯЕМОЕ_УСЛОВИЕ)

Пример 8. Продолжаем работать с базой данных сети аптек.

Нужно модифицировать таблицу AVAILABILITY и определить, что в одной и той же записи значение атрибута DateStart таблицы не должно быть меньше значения DateEnd.

ALTER TABLE AVAILABILITY ADD CONSTRAINT CHECK_DATA CHECK(DateStart

Изменяем таблицу DEFICIT и определяем, что в одной и той же записи значение атрибута DateStart таблицы не должено быть меньше значения DateEnd:

ALTER TABLE DEFICIT ADD CONSTRAINT CHECK_DATA CHECK(DateStart

Поделиться с друзьями

Добро пожаловать в TechBrothersIT: как добавить столбец со значениями в существующую таблицу SQL Server

Нам часто приходится добавлять новый столбец в существующие таблицы. Давайте представим, что у нас есть таблица dbo.Customer, и наша компания находится в США, и мы никогда не занимались бизнесом за пределами США. поэтому нам никогда не нужен столбец страны в нашей существующей таблице для адресов, поскольку мы работаем только в США. Сейчас компания решила расширить бизнес и, возможно, заполучить клиентов из других стран. Что мы хотим сделать, мы хотим добавить столбец в существующую таблицу со значением по умолчанию. Все существующие значения также должны быть обновлены до значения по умолчанию, когда мы добавляем этот новый столбец. Двигаясь вперед, если название страны не указано, в противном случае должно быть указано значение США.

 --Создать образец таблицы без столбца CountryName
Создайте таблицу dbo.Customer (
Идентификация CustomerId Int (1,1),
ИмяКлиентаVARCHAR(100),
Адрес улицы VARCHAR(100),
Город ВАРЧАР(100),
Состояние CHAR(2))
--Вставьте пару записей в таблицу образцов
Вставить в dbo.Customer
Выберите «Аамир Шахзад», «Проверить адрес улицы», «Шарлотта», «Северная Каролина». 
Союз
Выберите «M Raza», «Test Street Address», «Charlotte», «NC» 

Добавить новый столбец без указания ограничения NOT NULL 

 Изменить таблицу dbo.Customer
ДОБАВИТЬ название страны VARCHAR(50)
По умолчанию «США»
Выберите * Из dbo.Customer 

Рис. 1. Добавление нового столбца в существующую таблицу SQL Server без указания ограничения Not Null

В приведенном выше запросе мы не упомянули ограничение Not Null для нашего столбца CountryName. SQL Server принял это по умолчанию как Null. Также он не обновлял недавно добавленный столбец значениями по умолчанию.

Добавить новый столбец с NOT NULL И ограничением по умолчанию

Если мы предоставим ограничение Not Null для CountryColumn, а также значение по умолчанию. Недавно добавленный столбец будет заполнен значениями по умолчанию, как показано ниже.

 Изменить таблицу dbo.Customer
ДОБАВИТЬ имя страны VARCHAR(50) NOT NULL
По умолчанию «США»
Выберите * из dbo. Customer 

Рис. 2. Добавление ограничения Not Null со значением по умолчанию Для заполнения существующего столбца в таблице

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

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

 Изменить таблицу dbo.Customer
ДОБАВИТЬ название страны VARCHAR(50)
По умолчанию «США»
СО значениями
Выберите * из dbo.Customer 

Рис. 3. Добавьте новый столбец с нулевым ограничением и значением по умолчанию и заполните значениями по умолчанию

Как видите, в последнем скрипте sql мы использовали предложение «Со значениями». Если вы используете это, существующие записи будут заполнены значениями по умолчанию. Кроме того, поскольку мы не предоставили ограничение NOT Null, наша таблица будет принимать значения Null, если мы хотим сохранить значение Null в столбце. Если значение не будет предоставлено, в таблицу будет вставлено значение по умолчанию.

 --Проверьте, нормально ли работает значение по умолчанию
Вставьте в dbo.Customer(CustomerName,StreetAddress,City,State)
Выберите «Джон Смит», «Проверить адрес улицы», «Шарлотта», «Северная Каролина».
--Вставить пустое имя страны
Вставьте в dbo.Customer(CustomerName,StreetAddress,City,State,CountryName)
Выберите «July River», «Test Street Address», «Jersey City», «NJ», Null 

Рис. 4. Вставка записей в таблицу SQL Server после добавления нового столбца

Видеоруководство по SQL Server. Добавить столбец в таблицу SQL и заполнить уже существующие строки значением по умолчанию.

sql-docs/add-columns-to-a-table-database-engine.

md в реальном времени · MicrosoftDocs/sql-docs · GitHub

[!INCLUDE sqlserver2016-asdb-asdbmi-asa-pdw]

В этой статье описывается, как добавить новые столбцы в таблицу в [!INCLUDEssnoversion] с помощью [!INCLUDEssManStudioFull] или [!INCLUDEtsql].

Замечания

Использование оператора ALTER TABLE для добавления столбцов в таблицу автоматически добавляет эти столбцы в конец таблицы.

Если вы хотите, чтобы столбцы располагались в таблице в определенном порядке, вы должны использовать [!INCLUDEssManStudioFull]. Хотя это и не рекомендуется, дополнительные сведения об изменении порядка таблиц см. в разделе Изменение порядка столбцов в таблице.

Чтобы запросить существующие столбцы, используйте представление каталога объектов sys.columns.

Разрешения

Требуется разрешение ALTER для таблицы.

Используйте SQL Server Management Studio

Вставка столбцов в таблицу с помощью Table Designer

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

2. Выберите первую пустую ячейку в столбце Имя столбца .

3. Введите имя столбца в ячейку. Имя столбца является обязательным значением.

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

   Это обязательное значение, и ему будет присвоено значение по умолчанию, если вы его не выберете.

   [!ПРИМЕЧАНИЕ]
   Значение по умолчанию можно изменить в диалоговом окне Параметры в разделе Инструменты базы данных .

5. Продолжайте определять любые другие свойства столбца на вкладке Свойства столбца.

   [!ПРИМЕЧАНИЕ]
   Значения по умолчанию для свойств вашего столбца добавляются при создании нового столбца, но вы можете изменить их на вкладке Свойства столбца.