Sql как изменить тип данных столбца

SQL оператор ALTER TABLE

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

Описание

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

Добавить столбец в таблицу

Синтаксис

Синтаксис SQL оператора ALTER TABLE для добавления столбца в таблицу.

Пример

Давайте посмотрим на SQL ALTER TABLE пример, который добавляет столбец.
Например.

Sql как изменить тип данных столбца

Модификация типа данных столбца

MS SQL Server
В MS SQL Server для изменения типа данных столбцов используется предложение ALTER COLUMN инструкции ALTER TABLE.

ALTER TABLE <table_name> ALTER COLUMN <col_name> <new_col_type> [ NULL | NOT NULL ]

В аргументе column_name содержит имя столбца, подлежащего изменению. Аргумент new_col_type содержит описание нового типа данных для изменяемого столбца.

Ниже приведены критерии для аргумента new_col_type изменяемого столбца:

• Предыдущие типы данных должны быть неявно преобразуемыми в новый тип данных.
• Аргумент new_col_type не может принадлежать к типу timestamp.
• Если изменяемый столбец является столбцом идентификаторов, то новый тип данных должен поддерживать свойство идентификатора.

Тип данных столбцов text, ntext и image может быть изменен только следующими способами:

• text на varchar(max), nvarchar(max) или xml
• ntext на varchar(max), nvarchar(max) или xml
• image в varbinary(max)

MySQL Server
В СУБД MySQL описание столбца меняется с помощью предложений CHANGE или MODIFY в операторе ALTER TABLE.

ALTER TABLE <table_name> MODIFY <col_definition>
ALTER TABLE <table_name> CHANGE <old_col_name> <col_definition>

В выражении col_definition для MODIFY и CHANGE используется тот же синтаксис, что и для CREATE TABLE. Следует учитывать, что этот синтаксис включает имя столбца, а не просто его тип.

Операция CHANGE, в отличие от операции MODIFY, не только меняет тип данных, но и переименовывает столбец. Поэтому при ее использовании сначала указывается имя столбца, который будет меняться, а затем дается полностью новое объявление столбца, включая опять же его имя.

Увеличить размерность столбцов name и lastname в таблице tbl_clients с 45 символов до 60

Модификация таблиц. Оператор ALTER TABLE

Перед изучением данной темы рекомендуется ознакомиться со следующей темой:

Содержание

• 1. Модификация таблицы. Оператор ALTER TABLE . Особенности применения
• 2. Изменение параметров существующего поля. Комбинация ключевых слов ALTER COLUMN
• 3. Пример изменения типа поля (столбца) таблицы и задавание новых ограничений. ALTER TABLE + ALTER COLUMN
• 4. Добавление новых полей в таблицу. Ключевое слово ADD
• 5. Пример добавления полей к таблице
• 6. Удаление полей. Ключевые слова DROP COLUMN
• 7. Пример создания таблицы, добавление нового и удаление существующего поля из таблицы ( ALTER TABLE + ADD , ALTER TABLE + DROP COLUMN )
• 8. Удаление нескольких полей ( ALTER TABLE + DROP COLUMN )
• 9. Пример изменения ограничений в заданном поле таблицы. Сочетание ALTER TABLE+ADD+DROP
• 10. Пример изменения названия поля. Комбинация операторов ALTER TABLE + DROP , ALTER TABLE + ADD

Поиск на других ресурсах:

1. Модификация таблицы. Оператор ALTER TABLE . Особенности применения

Оператор ALTER TABLE предназначен для модификации ранее созданной таблицы. Оператор позволяет выполнять следующие базовые операции:

• добавлять новые поля в таблицу;
• удалять поля из таблицы;
• изменять тип данных полей;
• добавлять и удалять ограничения на поля;
• другое.

Оператор имеет широкий спектр возможностей. В данной теме рассматриваются только некоторые основные из них, а именно:

• добавление новых полей. Это обеспечивается за счет ключевого слова ADD ;
• удаление полей. Здесь используется ключевое слово DROP в сочетании со словом COLUMN ;
• редактирование полей. В этом случае используется комбинация слов ALTER COLUMN .

2. Изменение параметров существующего поля. Комбинация ключевых слов ALTER COLUMN

Для изменения типа поля используется следующий упрощенный синтаксис.

• Table_Name – имя таблицы, которая была создана ранее;
• Column_Name – название поля таблицы Table_Name , которое было создано ранее;
• New_Type – название типа, который назначается полю Column_Name ;
• New_Restrictions – ограничения, накладываемые на поле. Здесь можно задавать новые ограничения, которые раньше не были определены.

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

3. Пример изменения типа поля (столбца) таблицы и задавание новых ограничений. ALTER TABLE + ALTER COLUMN

В нашем примере создается таблица с полями A , B , C , D . Затем с помощью оператора ALTER TABLE тип поля C изменяется с типа VARCHAR(20) на тип DECIMAL . Также задается ограничение NOT NULL .

4. Добавление новых полей в таблицу. Ключевое слово ADD

Добавление нового поля с помощью оператора ALTER TABLE реализуется с использованием ключевого слова ADD . В этом случае упрощенная общая форма оператора имеет вид

• Table_Name – имя создаваемой таблицы;
• Field1 , Field2 , FieldN – имена полей, которые нужно добавить. Эти имена не должны повторять существующие имена таблицы. Другими словами, имена полей в таблице должны быть уникальными;
• Type1 , Type2 , TypeN – типы полей Field1 , Field2 , FieldN соответственно.

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

• Field – имя поля, которое нужно добавить;
• Type – тип поля, который нужно добавить.

Этот случай также предусматривает добавление ограничений на поля. В этом случае, после типа Type указывается одно или несколько ограничений ( NOT NULL , NULL и другие).

5. Пример добавления полей к таблице

В примере с помощью команды CREATE TABLE формируются поля со следующими названиями и их типами:

• поле A типа Int . Это поле имеет ограничение NOT NULL и PRIMARY KEY ;
• поле B типа Float .

Затем к этим полям добавляются следующие поля с помощью команды ALTER TABLE с ключевым словом ADD :

• поле C типа Varchar(20) ;
• поле D типа Date .

6. Удаление полей. Ключевые слова DROP COLUMN

Для удаления полей из таблицы используется оператор ALTER TABLE в сочетании с ключевыми словами DROP COLUMN . В этом случае упрощенная общая форма оператора следующая

• Table_Name – имя таблицы;
• Column1 , Column2 , ColumnN – имена полей (столбцов) таблицы, которые нужно удалить. Если задать несуществующие поля, будет отображаться сообщение об ошибке.

Если из таблицы нужно удалить только одно поле, вид оператора ALTER TABLE будет следующий

• Field – имя удаляемого поля.

7. Пример создания таблицы, добавление нового и удаление существующего поля из таблицы ( ALTER TABLE + ADD , ALTER TABLE + DROP COLUMN )

В примере демонстрируется использование следующих команд:

• CREATE TABLE – создание таблиц из 4-х полей;
• ALTER TABLE с ключом ADD – добавление нового поля к таблице;
• ALTER TABLE с ключом DROP COLUMN – удаление поля из таблицы.

8. Удаление нескольких полей ( ALTER TABLE + DROP COLUMN )

В примере сначала создается таблица, содержащая 6 полей с именами A , B , C , D , E , F . Затем с помощью команды ALTER TABLE из таблицы удаляются два поля с именами A , C .

9. Пример изменения ограничений в заданном поле таблицы. Сочетание ALTER TABLE+ADD+DROP

Если к существующему полю нужно добавить ограничение, то можно выполнить следующую последовательность действий:

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

Код на языке T-SQL, выполняющий операции по изменению ограничений следующий

На рисунке 1 показан вид окна Microsoft SQL Server Management Studio с выполняемым примером.

Рисунок 1. Оператор ALTER TABLE . Добавление ограничения NOT NULL в поле B

10. Пример изменения названия поля. Комбинация операторов ALTER TABLE + DROP , ALTER TABLE + ADD

В примере показано изменение названия заданного поля путем удаления существующего ( ALTER TABLE + DROP ) и добавления нового с новым именем ( ALTER TABLE + ADD ).

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

Результат выполнения программы в Microsoft SQL Server Management Studio изображен на рисунке 2.

Рисунок 2. Результат выполнения программы. Таблица MyTable с измененным столбцом ABC

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

Команда 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:

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

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

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

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

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

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

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

Меняем тип

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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