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

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:

ALTER TABLE имя_таблицы [WITH CHECK | WITH NOCHECK]

Итак, используя 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! Также вас может заинтересовать общий курс по работе с реляционными и нереляционными БД:
Sql как изменить тип данных столбца
Модификация типа данных столбца
MS SQL Server 
В MS SQL Server для изменения типа данных столбцов используется предложение ALTER COLUMN инструкции ALTER TABLE.
ALTER TABLE ALTER COLUMN  [ NULL | NOT NULL ]
В аргументе column_name содержит имя столбца, подлежащего изменению. Аргумент new_col_type содержит описание нового типа данных для изменяемого столбца.
Ниже приведены критерии для аргумента new_col_type изменяемого столбца:

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

ALTER TABLE MODIFY  
ALTER TABLE CHANGE
Переименовать столбец saledate в таблице tbl_sales в d_sale и изменить его тип с date на datetime. Установить атрибут NOT NULL для данного столбца
 MS SQL: 
/*переиенование столбца тоблицы*/ 
sp_rename 'tbl_sales.saledate', 'd_sale', 'COLUMN'; 
/*измениение описание столбца*/ 
ALTER TABLE tbl_sales ALTER COLUMN d_sale datetime NOT NULL;
MySQL: 
ALTER TABLE tbl_sales CHANGE saledate d_sale datetime NOT NULL
Увеличить размерность столбцов name и lastname в таблице tbl_clients с 45 символов до 60 
Изменение столбца

Определение столбца можно изменить, используя инструкцию ALTER TABLE без предло- жения MODIFY . Изменение столбца может включать изменения типа данных, размера и стандартного значения.

Можно увеличить ширину или точность числового столбца.
Можно увеличить ширину символьных столбцов.


Можно уменьшить ширину столбца в следующих случаях:

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


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

Далее: Порядок использования представлений словаря
Post Views: 1 182
Похожие записи
Предложение WITH
Используя предложение WITH, можно определить блок запроса до его применения в запросе. Предложение WITH (формально называется subquery_factoring_clause) позволяет многократно использовать один и тот же блок запроса в инструкции SELECT, когда она встречается более одного раза в сложном запросе. Это особенно. Читать далее
Оператор WITH в sql
Оператор WITH в SQL — это чрезвычайно полезный инструмент для создания временных таблиц и использования их внутри других запросов. Это позволяет упростить код и улучшить производительность запросов. Оператор WITH (также известный как Common Table Expression) используется для создания временных таблиц. Читать далее
Выполнение запросов внешних таблиц
Внешняя таблица не описывает никаких данных, которые хранятся в базе данных. Внешняя таблица не описывает порядок хранения данных во внешнем источнике. Вместо этого она описывает, как уровень внешней таблицы должен представлять данные для сервера. За преобразования, которые требуется выполнять над. Читать далее
Инструкция FLASHBACK TABLE
Позволяет восстанавливать таблицы до состояния на заданный момент времени с помощью одной инструкции. Восстанавливает табличные данные вместе со связанными индексами и ограничениями. Позволяет возвращать таблицу и ее содержимое в состояние, существовавшее на определенный момент времени, или к изменению системы, определенному. Читать далее
Создание внешней таблицы
Внешние таблицы создаются с помощью предложения ORGANIZATION EXTERNAL инструкции CREATE TABLE. В действительности таблица не создается. Точнее, создаются метаданные в словаре данных, который можно использовать для доступа к внешним данным. Предложение ORGANIZATION применяется для указания порядка, в котором сохраняются строки. Читать далее
Инструкция DROP TABLE … PURGE
В базе данных Oracle имеется функция для удаления таблиц. При удалении таблицы база данных не сразу освобождает пространство, занимаемое таблицей. Точнее, база данных переименовывает таблицу и помещает ее в корзину, где таблица позже может быть восстановлена с помощью инструкции FLASHBACK. Читать далее
Создание внешней таблицы с помощью драйвера доступа ORACLE_LOADER
Рассмотрим, как создаются внешние таблицы посредством драйвера доступа ORACLE_LOADER. Предположим, что существует текстовый файл, в котором имеются записи в следующем формате: 10,jones,11-Dec-1934 20,smith,12-Jun-1972 Записи разделяются символом новой строки, и все поля заканчиваются запятой ( , ). Имя файла: /emp_dir/emp.dat. Читать далее
Пример создание внешней таблицы с помощью драйвера доступа ORACLE_DATAPUMP
Используя драйвер доступа ORACLE_DATAPUMP, можно выполнять с внешними таблицами операции выгрузки и повторной загрузки. Примечание. В контексте внешних таблиц загрузка данных обозначает операцию чтения данных из внешней таблицы и их загрузку в таблицу базы данных. Под выгрузкой данных понимается чтение. Читать далее
Установка Предпочтений SQL Developer
Можно настроить много аспектов интерфейса и среды SQL Developer, изменяя предпочтения SQL Developer согласно Вашим потребностям. Чтобы изменить предпочтения SQL Developer, выберите Tools, а затем Preferences. Настройте интерфейс SQL Developer и среду. В меню Tools выберите Preferences. Предпочтения группируется в. Читать далее
Резюме по SQL Developer
В этой рубрике было рассмотрено использование SQL Developer, чтобы выполнять следующие задачи: Просматривать, создавать и редактировать объекты базы данных Выполнять SQL-операторы и сценарии на Рабочем листе SQL Создавать и сохранять пользовательские отчеты SQL Developer является бесплатным графическим инструментом, позволяющим упростить. Читать далее
Sql как изменить тип данных столбца
ALTER FOREIGN TABLE — изменить определение сторонней таблицы
Синтаксис
ALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] имя [ * ] действие [, . ] ALTER FOREIGN TABLE [ IF EXISTS ] [ ONLY ] имя [ * ] RENAME [ COLUMN ] имя_столбца TO новое_имя_столбца ALTER FOREIGN TABLE [ IF EXISTS ] имя RENAME TO новое_имя ALTER FOREIGN TABLE [ IF EXISTS ] имя SET SCHEMA новая_схема Где действие может быть следующим: ADD [ COLUMN ] имя_столбца тип_данных [ COLLATE правило_сортировки ] [ ограничение_столбца [ . ] ] DROP [ COLUMN ] [ IF EXISTS ] имя_столбца [ RESTRICT | CASCADE ] ALTER [ COLUMN ] имя_столбца [ SET DATA ] TYPE тип_данных [ COLLATE правило_сортировки ] ALTER [ COLUMN ] имя_столбца SET DEFAULT выражение ALTER [ COLUMN ] имя_столбца DROP DEFAULT ALTER [ COLUMN ] имя_столбца < SET | DROP >NOT NULL ALTER [ COLUMN ] имя_столбца SET STATISTICS integer ALTER [ COLUMN ] имя_столбца SET ( атрибут = значение [, . ] ) ALTER [ COLUMN ] имя_столбца RESET ( атрибут [, . ] ) ALTER [ COLUMN ] имя_столбца SET STORAGE < PLAIN | EXTERNAL | EXTENDED | MAIN >ALTER [ COLUMN ] имя_столбца OPTIONS ( [ ADD | SET | DROP ] параметр ['значение'] [, . ]) ADD ограничение_таблицы [ NOT VALID ] VALIDATE CONSTRAINT имя_ограничения DROP CONSTRAINT [ IF EXISTS ] имя_ограничения [ RESTRICT | CASCADE ] DISABLE TRIGGER [ имя_триггера | ALL | USER ] ENABLE TRIGGER [ имя_триггера | ALL | USER ] ENABLE REPLICA TRIGGER имя_триггера ENABLE ALWAYS TRIGGER имя_триггера SET WITHOUT OIDS INHERIT таблица_родитель NO INHERIT таблица_родитель OWNER TO < новый_владелец | CURRENT_ROLE | CURRENT_USER | SESSION_USER > OPTIONS ( [ ADD | SET | DROP ] параметр ['значение'] [, . ])
Описание
ALTER FOREIGN TABLE меняет определение существующей сторонней таблицы. Эта команда имеет несколько разновидностей:
ADD COLUMN
Эта форма добавляет в стороннюю таблицу новый столбец, следуя тому же синтаксису, что и CREATE FOREIGN TABLE . В отличие от добавления столбца в обычную таблицу, при данной операции в базовом хранилище ничего не меняется; эта команда просто объявляет о доступности нового столбца через данную стороннюю таблицу. DROP COLUMN [ IF EXISTS ]
Эта форма удаляет столбец из сторонней таблицы. Если что-либо зависит от этого столбца, например, представление, для успешного результата потребуется добавить CASCADE . Если указано IF EXISTS и этот столбец не существует, ошибка не происходит, вместо этого выдаётся замечание. SET DATA TYPE
Эта форма меняет тип столбца сторонней таблицы. И это не влияет на нижележащее хранилище: данная операция просто меняет тип, который по мнению Postgres Pro будет иметь этот столбец. SET / DROP DEFAULT
Эти формы задают или удаляют значение по умолчанию для столбцов. Значения по умолчанию применяются только при последующих командах INSERT или UPDATE ; их изменения не отражаются в строках, уже существующих в таблице. SET / DROP NOT NULL
Устанавливает, будет ли столбец принимать значения NULL или нет. SET STATISTICS
Эта форма задаёт ориентир сбора статистики по столбцам для последующих операций ANALYZE . За подробностями обратитесь к описанию подобной формы ALTER TABLE . SET ( атрибут = значение [, . ] ) 
RESET ( атрибут [, . ] )
Эта форма задаёт или сбрасывает значения атрибутов. За подробностями обратитесь к описанию подобной формы ALTER TABLE . SET STORAGE
Эта форма задаёт режим хранения для столбца. За подробностями обратитесь к описанию подобной формы ALTER TABLE . Заметьте, что режим хранения не имеет значения, если обёртка сторонних данных для этой таблицы будет игнорировать его. ADD ограничение_таблицы [ NOT VALID ]
Эта форма добавляет новое ограничение в стороннюю таблицу с применением того же синтаксиса, что и CREATE FOREIGN TABLE . В настоящее время поддерживаются только ограничения CHECK .
В отличие от ограничения, добавляемого для обычной таблицы, ограничение сторонней таблицы фактически никак не проверяется; эта команда сводится просто к заявлению о том, что все строки в сторонней таблице предположительно удовлетворяют новому условию. (Подробнее это рассматривается в описании CREATE FOREIGN TABLE .) Если ограничение помечено как NOT VALID (непроверенное), сервер не будет полагать, что оно выполняется; такая запись делается только на случай использования в будущем. VALIDATE CONSTRAINT
Эта форма отмечает ограничение, которая ранее было помечено NOT VALID , как проверенное. Собственно для проверки этого ограничения ничего не делается, но последующие запросы будут полагать, что оно действует. DROP CONSTRAINT [ IF EXISTS ]
Эта форма удаляет указанное ограничение сторонней таблицы. Если указано IF EXISTS и заданное ограничение не существует, это не считается ошибкой. В этом случае выдаётся только замечание. DISABLE / ENABLE [ REPLICA | ALWAYS ] TRIGGER
Эти формы управляют триггерами, принадлежащими сторонней таблице. За подробностями обратитесь к описанию подобной формы ALTER TABLE . SET WITHOUT OIDS
Синтаксис обратной совместимости для удаления системного столбца oid . Так как добавить системные столбцы oid теперь невозможно, это указание фактически не действует. INHERIT таблица_родитель 
Эта форма делает целевую стороннюю таблицу потомком указанной родительской таблицы. За подробностями обратитесь к описанию подобной формы ALTER TABLE . NO INHERIT таблица_родитель 
Эта форма удаляет целевую стороннюю таблицу из списка потомков указанной родительской таблицы. OWNER
Эта форма меняет владельца сторонней таблицы на заданного пользователя. OPTIONS ( [ ADD | SET | DROP ] параметр [' значение '] [, . ] )
Эта форма настраивает параметры сторонней таблицы или одного из её столбцов. ADD , SET и DROP определяют, какое действие будет выполнено (добавление, установка и удаление, соответственно). Если действие не задано явно, подразумевается ADD . Имена параметров не должны повторяться (хотя параметр таблицы и параметр столбца вполне могут иметь одно имя). Имена и значения параметров также проверяются библиотекой обёртки сторонних данных. RENAME
Формы RENAME меняют имя сторонней таблицы или имя столбца в сторонней таблице. SET SCHEMA
Эта форма переносит стороннюю таблицу в другую схему.
Все действия, кроме RENAME и SET SCHEMA , можно объединить в один список изменений и выполнить одновременно. Например, можно добавить несколько столбцов и/или изменить тип столбцов одной командой.
Если команда записана в виде ALTER FOREIGN TABLE IF EXISTS . и сторонняя таблица не существует, это не считается ошибкой. В этом случае выдаётся только замечание.
Выполнить ALTER FOREIGN TABLE может только владелец соответствующей таблицы. Чтобы сменить схему сторонней таблицы, необходимо также иметь право CREATE в новой схеме. Чтобы сменить владельца, необходимо быть непосредственным или опосредованным членом новой роли-владельца, а эта роль должна иметь право CREATE в схеме таблицы. (С такими ограничениями при смене владельца не происходит ничего такого, что нельзя было бы сделать, имея право удалить и вновь создать таблицу. Однако суперпользователь может сменить владельца таблицы в любом случае.) Чтобы добавить столбец или изменить тип столбца, ещё требуется иметь право USAGE для его типа данных.
Параметры
Имя (возможно, дополненное схемой) существующей сторонней таблицы, подлежащей изменению. Если перед именем таблицы указано ONLY , изменяется только заданная таблица. Без ONLY изменяется и заданная таблица, и все её потомки (если таковые есть). После имени таблицы можно также добавить необязательное указание * , чтобы явно обозначить, что изменению подлежат все дочерние таблицы. имя_столбца 
Имя нового или существующего столбца. новое_имя_столбца 
Новое имя существующего столбца. новое_имя 
Новое имя таблицы. тип_данных 
Тип данных нового столбца или новый тип данных существующего столбца. ограничение_таблицы 
Новое ограничение уровня таблицы для сторонней таблицы. имя_ограничения 
Имя существующего ограничения, подлежащего удалению. CASCADE
Автоматически удалять объекты, зависящие от удаляемого столбца или ограничения (например, представления, содержащие этот столбец), и, в свою очередь, все зависящие от них объекты (см. Раздел 5.14). RESTRICT
Отказать в удалении столбца или ограничения, если существуют зависящие от них объекты. Это поведение по умолчанию. имя_триггера 
Имя включаемого или отключаемого триггера. ALL
Отключает или включает все триггеры, принадлежащие сторонней таблице. (Если какие-либо из триггеров являются внутрисистемными, для этого требуются права суперпользователя. Сама система не добавляет такие триггеры в сторонние таблицы, но дополнительный код может сделать это.) USER
Отключает или включает все триггеры, принадлежащие сторонней таблице, кроме сгенерированных внутрисистемных. таблица_родитель 
Родительская таблица, с которой будет установлена или разорвана связь данной сторонней таблицы. новый_владелец 
Имя пользователя, назначаемого новым владельцем таблицы. новая_схема 
Имя схемы, в которую будет перемещена таблица.
Замечания
Ключевое слово COLUMN не несёт смысловой нагрузки и может быть опущено.
При добавлении или удалении столбцов ( ADD COLUMN / DROP COLUMN ), добавлении ограничений NOT NULL или CHECK или изменении типа данных ( SET DATA TYPE ) согласованность этих определений с внешним сервером не гарантируется. Ответственность за соответствие определений таблицы удалённой стороне лежит на пользователе.
За более полным описанием параметров обратитесь к CREATE FOREIGN TABLE .
Примеры
Установление ограничения NOT NULL для столбца:
ALTER FOREIGN TABLE distributors ALTER COLUMN street SET NOT NULL;
Изменение параметров сторонней таблицы:
ALTER FOREIGN TABLE myschema.distributors OPTIONS (ADD opt1 'value', SET opt2 'value2', DROP opt3);
Совместимость
Формы ADD , DROP и SET DATA TYPE соответствуют стандарту SQL. Другие формы являются собственными расширениями Postgres Pro . Кроме того, возможность указать в одной команде ALTER FOREIGN TABLE несколько операций так же является расширением.
ALTER FOREIGN TABLE DROP COLUMN позволяет удалить единственный столбец сторонней таблицы и оставить таблицу без столбцов. Это является расширением стандарта SQL, который не допускает существование сторонних таблиц с нулём столбцов.
См. также


Пред. 
Наверх
След.


ALTER FOREIGN DATA WRAPPER
Начало
ALTER FUNCTION




Похожие публикации:

Как войти в биос на ноутбуке lenovo
Как объединить ssd в raid
Как проверить лицензионность windows 7
Как сделать ссылку в ворде