Что такое alter table

Инструкция ALTER TABLE

Служит для изменения макета таблицы после того, как она была создана с помощью инструкции CREATE TABLE.

Примечание: Ядро СУБД Microsoft Access не поддерживает использование ALTER TABLE или любых других инструкций DDL с базами данных, которые не основаны на Microsoft Access. Вместо этого используйте методы Create DAO.

Синтаксис

Инструкция ALTER TABLE включает в себя следующие элементы:

Имя таблицы, которую требуется изменить.

Имя поля, которое нужно добавить в таблицу или удалить из нее. Или имя поля, которое нужно изменить в таблице.

Тип данных поля.

Размер поля в знаках (только для полей с типом данных TEXT и BINARY).

Индекс поля.

индекс_набора_полей

Индекс набора полей, добавляемых в таблицу.

имя_индекса

Имя удаляемого индекса набора полей.

Замечания

Изменить существующую таблицу с помощью инструкции ALTER TABLE можно несколькими способами. Вы можете:

Добавление поля в таблицу с помощью функции ADD COLUMN. Вы указываете имя поля, тип данных и (для полей с типом данных TEXT и BINARY) необязательный размер. Например, следующий текст добавляет в таблицу Employees поле «Текст» из 25 символов:

ALTER TABLE Employees ADD COLUMN Notes TEXT(25)

Для этого поля можно также указать индекс.

Если для поля определено свойство NOT NULL, то поле обязательно должно содержать допустимые данные.

Чтобы изменить тип данных существующего поля, используйте alter COLUMN. Вы указываете имя поля, новый тип данных и необязательный размер текстовых и двоичных полей. Например, следующая выписка меняет тип данных поля «Индекс» в таблице Employees (изначально он определен как «Integer») на 10-знаковое текстовое поле:

ALTER TABLE Employees ALTER COLUMN ZipCode TEXT(10)

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

Невозможно одновременно добавить или удалить несколько полей или индексов.
Чтобы добавить индекс для одного поля или для набора полей в таблице, используйте инструкцию CREATE INDEX. Чтобы удалить индекс, созданный с помощью инструкции ALTER TABLE или CREATE INDEX, можно использовать инструкцию ALTER TABLE или DROP.
Свойство NOT NULL можно задавать для одного поля или внутри именованного предложения CONSTRAINT для одного или нескольких полей. Свойство NOT NULL для поля можно задать только один раз. Попытка определить это свойство повторно приведет к ошибке выполнения.

SQL оператор ALTER TABLE

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

Описание

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

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

Синтаксис

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

ALTER TABLE table_name
ADD column_name column_definition;

Пример

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

Что такое alter table

ALTER TABLE — изменить определение таблицы

Синтаксис

ALTER TABLE [ IF EXISTS ] [ ONLY ] имя [ * ] действие [, . ] ALTER TABLE [ IF EXISTS ] [ ONLY ] имя [ * ] RENAME [ COLUMN ] имя_столбца TO новое_имя_столбца ALTER TABLE [ IF EXISTS ] [ ONLY ] имя [ * ] RENAME CONSTRAINT имя_ограничения TO имя_нового_ограничения ALTER TABLE [ IF EXISTS ] имя RENAME TO новое_имя ALTER TABLE [ IF EXISTS ] имя SET SCHEMA новая_схема ALTER TABLE ALL IN TABLESPACE имя [ OWNED BY имя_роли [, . ] ] SET TABLESPACE новое_табл_пространство [ NOWAIT ] ALTER TABLE [ IF EXISTS ] имя ATTACH PARTITION имя_секции FOR VALUES указание_границ_секции ALTER TABLE [ IF EXISTS ] имя DETACH PARTITION имя_секции Где действие может быть следующим: ADD [ COLUMN ] [ IF NOT EXISTS ] имя_столбца тип_данных [ COLLATE правило_сортировки ] [ ограничение_столбца [ . ] ] DROP [ COLUMN ] [ IF EXISTS ] имя_столбца [ RESTRICT | CASCADE ] ALTER [ COLUMN ] имя_столбца [ SET DATA ] TYPE тип_данных [ COLLATE правило_сортировки ] [ USING выражение ] ALTER [ COLUMN ] имя_столбца SET DEFAULT выражение ALTER [ COLUMN ] имя_столбца DROP DEFAULT ALTER [ COLUMN ] имя_столбца < SET | DROP >NOT NULL ALTER [ COLUMN ] имя_столбца ADD GENERATED < ALWAYS | BY DEFAULT >AS IDENTITY [ ( параметры_последовательности ) ] ALTER [ COLUMN ] имя_столбца < SET GENERATED < ALWAYS | BY DEFAULT >| SET параметр_последовательности | RESTART [ [ WITH ] перезапуск ] > [. ] ALTER [ COLUMN ] имя_столбца DROP IDENTITY [ IF EXISTS ] ALTER [ COLUMN ] имя_столбца SET STATISTICS integer ALTER [ COLUMN ] имя_столбца SET ( атрибут = значение [, . ] ) ALTER [ COLUMN ] имя_столбца RESET ( атрибут [, . ] ) ALTER [ COLUMN ] имя_столбца SET STORAGE < PLAIN | EXTERNAL | EXTENDED | MAIN >ADD ограничение_таблицы [ NOT VALID ] ADD ограничение_таблицы_по_индексу ALTER CONSTRAINT имя_ограничения [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] VALIDATE CONSTRAINT имя_ограничения DROP CONSTRAINT [ IF EXISTS ] имя_ограничения [ RESTRICT | CASCADE ] DISABLE TRIGGER [ имя_триггера | ALL | USER ] ENABLE TRIGGER [ имя_триггера | ALL | USER ] ENABLE REPLICA TRIGGER имя_триггера ENABLE ALWAYS TRIGGER имя_триггера DISABLE RULE имя_правила_перезаписи ENABLE RULE имя_правила_перезаписи ENABLE REPLICA RULE имя_правила_перезаписи ENABLE ALWAYS RULE имя_правила_перезаписи DISABLE ROW LEVEL SECURITY ENABLE ROW LEVEL SECURITY FORCE ROW LEVEL SECURITY NO FORCE ROW LEVEL SECURITY CLUSTER ON имя_индекса SET WITHOUT CLUSTER SET WITH OIDS SET WITHOUT OIDS SET TABLESPACE новое_табл_пространство SET < LOGGED | UNLOGGED >SET ( параметр_хранения [= значение] [, . ] ) RESET ( параметр_хранения [, . ] ) INHERIT таблица_родитель NO INHERIT таблица_родитель OF имя_типа NOT OF OWNER TO < новый_владелец | CURRENT_USER | SESSION_USER > REPLICA IDENTITY < DEFAULT | USING INDEX имя_индекса | FULL | NOTHING > и ограничение_таблицы_по_индексу: [ CONSTRAINT имя_ограничения ] < UNIQUE | PRIMARY KEY >USING INDEX имя_индекса [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

Описание

ALTER TABLE меняет определение существующей таблицы. Несколько её разновидностей описаны ниже. Заметьте, что для разных разновидностей могут требоваться разные уровни блокировок. Если явно не отмечено другое, запрашивается блокировка ACCESS EXCLUSIVE . При указании нескольких подкоманд будет запрашиваться самая сильная блокировка из требуемых ими.

ADD COLUMN [ IF NOT EXISTS ]

Эта форма добавляет в таблицу новый столбец, с тем же синтаксисом, что и CREATE TABLE . Если указано IF NOT EXISTS и столбец с таким именем уже существует, это не будет ошибкой. DROP COLUMN [ IF EXISTS ]

Эта форма удаляет столбец из таблицы. При этом автоматически будут удалены индексы и ограничения таблицы, связанные с этим столбцом. Также будет удалена многовариантная статистика, охватывающая удаляемый столбец, если после его удаления в статистике останутся данные только одного столбца. Если от этого столбца зависят какие либо объекты вне этой таблицы, например, внешние ключи или представления, чтобы удалить их, необходимо добавить указание CASCADE . Если в команде указано IF EXISTS и этот столбец не существует, это не считается ошибкой, вместо этого просто выдаётся замечание. SET DATA TYPE

Эта форма меняет тип столбца таблицы. Индексы и простые табличные ограничения, включающие этот столбец, будут автоматически преобразованы для использования нового типа столбца, для чего будет заново разобрано определяющее их выражение. Необязательное предложение COLLATE задаёт правило сортировки для нового столбца; если оно опущено, выбирается правило сортировки по умолчанию для нового типа. Необязательное предложение USING определяет, как новое значение столбца будет получено из старого; если оно отсутствует, выполняется приведение типа по умолчанию, как обычное присваивание значения старого типа новому. Предложение USING становится обязательным, если неявное приведение или присваивание с приведением старого типа к новому не определено. SET / DROP DEFAULT

Эти формы задают или удаляют значение по умолчанию для столбцов. Значения по умолчанию применяются только при последующих командах INSERT или UPDATE ; их изменения не отражаются в строках, уже существующих в таблице. SET / DROP NOT NULL

Эти формы определяют, будет ли столбец принимать значения NULL или нет. Задать SET NOT NULL можно, только если столбец не содержит значений NULL.

Если данная таблица является секцией, операцию DROP NOT NULL нельзя выполнить для столбца, если он определён с характеристикой NOT NULL в родительской таблице. Чтобы удалить ограничение NOT NULL из всех секций, выполните DROP NOT NULL для родительской таблицы. Даже если ограничение NOT NULL в родительской таблице отсутствует, при желании такое ограничение может быть добавлено в отдельные секции. Другими словами, потомки могут запрещать значения NULL, даже если родитель их допускает, но не наоборот. ADD GENERATED < ALWAYS | BY DEFAULT >AS IDENTITY
SET GENERATED < ALWAYS | BY DEFAULT >
DROP IDENTITY [ IF EXISTS ]

Эти формы определяют, является ли столбец столбцом идентификации, и меняют свойства генерирования значений уже существующего столбца идентификации. За подробностями обратитесь к CREATE TABLE .

Если указано DROP IDENTITY IF EXISTS и заданный столбец не является столбцом идентификации, это не считается ошибкой. В этом случае выдаётся только замечание. SET параметр_последовательности
RESTART

Эти формы меняют нижележащую последовательность ранее созданного столбца идентификации. Здесь параметр_последовательности — это параметр, поддерживаемый командой ALTER SEQUENCE , например INCREMENT BY . SET STATISTICS

Эта форма задаёт ориентир сбора статистики по столбцу для последующих операций ANALYZE . Диапазон допустимых значений ориентира: 0..10000; при -1 применяется системное значение по умолчанию (default_statistics_target). За дополнительными сведениями об использовании статистики планировщиком запросов PostgreSQL обратитесь к Разделу 14.2.

SET STATISTICS запрашивает блокировку SHARE UPDATE EXCLUSIVE . SET ( атрибут = значение [, . ] )
RESET ( атрибут [, . ] )

Эта форма устанавливает или сбрасывает параметры атрибутов. В настоящее время единственными параметрами атрибутов являются n_distinct и n_distinct_inherited , которые переопределяют оценку кол-ва_различных_значений, производимую последующими операциями ANALYZE . Атрибут n_distinct влияет на расчёт статистики по самой таблице, а n_distinct_inherited — на статистику по таблице и её потомкам. Если заданное значение положительно, ANALYZE будет считать, что столбец содержит именно это количество различных значений не NULL. Если заданное значение отрицательно (оно должно быть больше или равно -1), ANALYZE будет считать, что количество различных значений не NULL в столбце линейно зависит от размера таблицы; точное число будет получено умножением примерного размера таблицы на абсолютное значение параметра. Например, при -1 будет предполагаться, что различны все значения в столбце, а при -0,5 — что в среднем каждое значение повторяется дважды. Это может быть полезно, когда размер таблицы меняется со временем, так как умножение на число строк в таблице производится только во время планирования запроса. С 0 количество различных значений оценивается как обычно. За дополнительными сведениями об использовании статистики планировщиком запросов PostgreSQL обратитесь к Разделу 14.2.

Для изменения параметров атрибутов запрашивается блокировка SHARE UPDATE EXCLUSIVE . SET STORAGE

Эта форма устанавливает режим хранения столбца. Она определяет, хранятся ли данные внутри таблицы или в отдельной таблице TOAST , а также, сжимаются ли они. Режим PLAIN должен применяться для значений фиксированной длины, таких как integer ; это вариант хранения внутри, без сжатия. Режим MAIN применяется для хранения внутри, но сжатых данных, EXTERNAL — для внешнего хранения несжатых данных, а EXTENDED — для внешнего хранения сжатых данных. EXTENDED используется по умолчанию для большинства типов данных, поддерживающих хранилище не PLAIN . Применение EXTERNAL позволяет ускорить операции с подстроками на очень больших значениях text и bytea , за счёт проигрыша в объёме хранилища. Заметьте, что предложение SET STORAGE само по себе не меняет ничего в таблице, оно только задаёт стратегию, которая будет реализована при будущих изменениях в таблице. За дополнительными сведениями обратитесь к Разделу 67.2. ADD ограничение_таблицы [ NOT VALID ]

Эта форма добавляет в таблицу новое ограничение, принимая тот же синтаксис описания ограничения, что и CREATE TABLE , а также дополнительное указание NOT VALID , которое в настоящее время поддерживается только для ограничений внешнего ключа и ограничений-проверок.

Обычно эта форма влечёт сканирование всей таблицы для проверки, что все существующие строки в таблице удовлетворяют новому ограничению. Но если используется указание NOT VALID , эта потенциально длительная проверка пропускается. Тем не менее это ограничение будет действовать при последующих добавлениях или изменениях данных (то есть эти операции не будут выполнены, если новая строка нарушит условие ограничения-проверки, либо при наличии внешнего ключа в главной таблице не найдётся соответствующая строка). Но база данных не будет считать, что ограничение выполняется для всех строк таблицы, пока оно не будет проверено с применением указания VALIDATE CONSTRAINT . Дополнительную информацию об использовании указания NOT VALID вы найдете ниже, в Замечания.

Хотя почти все разновидности ADD ограничение_таблицы требуют блокировку ACCESS EXCLUSIVE , для указания ADD FOREIGN KEY требуется только блокировка SHARE ROW EXCLUSIVE . Заметьте, что ADD FOREIGN KEY запрашивает такую блокировку как в таблице, в которой добавляется это ограничение, так и в таблице, на которую это ограничение ссылается. ADD ограничение_таблицы_по_индексу

Эта форма добавляет в таблицу новое ограничение PRIMARY KEY или UNIQUE на базе существующего уникального индекса. В это ограничение будут включены все столбцы данного индекса.

Индекс не может быть частичным и включать столбцы-выражения. Кроме того, это должен быть индекс-B-дерево с порядком сортировки по умолчанию. С такими ограничениями добавляемые индексы не будут ничем отличаться от индексов, создаваемых обычными командами ADD PRIMARY KEY и ADD UNIQUE .

В случае с указанием PRIMARY KEY , если столбцы индекса ещё не помечены NOT NULL , данная команда попытается выполнить ALTER COLUMN SET NOT NULL для каждого столбца. При этом потребуется произвести полное сканирование таблицы, чтобы убедиться, что столбец(ы) не содержит NULL. Во всех остальных случаях это быстрая операция.

Если задано имя ограничения, индекс будет переименован и получит заданное имя. В противном случае именем ограничения станет имя индекса.

После выполнения этой команды индекс становится « принадлежащим » ограничению, так же, как если бы он был создан обычной командой ADD PRIMARY KEY или ADD UNIQUE . Это значит, в частности, что при удалении ограничения индекс будет удалён вместе с ним.

Примечание

Добавление ограничения на базе существующего индекса бывает полезно в ситуациях, когда новое ограничение требуется добавить, не блокируя изменения в таблице на долгое время. Для этого можно создать индекс командой CREATE INDEX CONCURRENTLY , а затем задействовать его как полноценное ограничение, используя эту запись. См. следующий пример.

ALTER CONSTRAINT

Эта форма меняет атрибуты созданного ранее ограничения. В настоящее время изменять можно только ограничения внешнего ключа. VALIDATE CONSTRAINT

Эта форма проверяет ограничение внешнего ключа или ограничение-проверку, созданное ранее с указанием NOT VALID , сканируя всю таблицу с целью убедиться, что ограничению удовлетворяют все строки. Если ограничение уже помечено как проверенное, ничего не происходит. (В чём польза этой команды, вы можете узнать в Замечания.)

Эта команда запрашивает блокировку SHARE UPDATE EXCLUSIVE . DROP CONSTRAINT [ IF EXISTS ]

Эта форма удаляет указанное ограничение таблицы. Если указано IF EXISTS и заданное ограничение не существует, это не считается ошибкой. В этом случае выдаётся только замечание. DISABLE / ENABLE [ REPLICA | ALWAYS ] TRIGGER

Эти формы настраивают срабатывание триггера(ов), принадлежащего таблице. Отключённый триггер сохраняется в системе, но не выполняется, когда происходит вызывающее его событие. Для отложенных триггеров состояние включения проверяется при возникновении события, а не когда фактически вызывается функция триггера. Эта команда может отключить или включить один триггер по имени, либо все триггеры таблицы, либо только пользовательские триггеры (исключая сгенерированные внутрисистемные триггеры ограничений, например, триггеры, реализующие ограничения внешнего ключа или отложенные ограничения уникальности или исключений). Для отключения или включения сгенерированных внутрисистемных триггеров ограничений требуются права суперпользователя; отключать их следует с осторожностью, так как очевидно, что невозможно гарантировать целостность ограничений, если триггеры не работают. На механизм срабатывания триггеров также влияет конфигурационная переменная session_replication_role. Включённые без дополнительных указаний триггеры будут срабатывать, когда роль репликации — « origin » (по умолчанию) или « local » . Триггеры, включённые указанием ENABLE REPLICA , будут срабатывать, только если текущий режим сеанса — « replica » , а после указания ENABLE ALWAYS триггеры срабатывают вне зависимости от текущего режима репликации.

Эта команда запрашивает блокировку SHARE ROW EXCLUSIVE . DISABLE / ENABLE [ REPLICA | ALWAYS ] RULE

Эти формы настраивают срабатывание правил перезаписи, относящихся к таблице. Отключённое правило сохраняется в системе, но не применяется во время переписывания запроса. По сути эти операции подобны операциям включения/отключения триггеров. Однако это не распространяется на правила ON SELECT — они применяются всегда, чтобы представления продолжали работать, даже в сеансах, исполняющих не основную роль репликации. DISABLE / ENABLE ROW LEVEL SECURITY

Эти формы управляют применением относящихся к таблице политик защиты строк. Если защита включается, но политики для таблицы не определены, применяется политика запрета доступа по умолчанию. Заметьте, что политики могут быть определены для таблицы, даже если защита на уровне строк отключена — в этом случае политики НЕ применяются и их ограничения игнорируются. См. также CREATE POLICY . NO FORCE / FORCE ROW LEVEL SECURITY

Эти формы управляют применением относящихся к таблице политик защиты строк, когда пользователь является её владельцем. Если это поведение включается, политики защиты на уровне строк будут действовать и на владельца таблицы. Если оно отключено (по умолчанию), защита на уровне строк не будет действовать на пользователя, являющегося владельцем таблицы. См. также CREATE POLICY . CLUSTER ON

Эта форма выбирает индекс по умолчанию для последующих операций CLUSTER . Собственно кластеризация таблицы при этом не выполняется.

Для изменения параметров кластеризации запрашивается блокировка SHARE UPDATE EXCLUSIVE . SET WITHOUT CLUSTER

Эта форма удаляет последнее заданное указание индекса для CLUSTER . Её действие отразится на будущих операциях кластеризации, для которых не будет задан индекс.

Для изменения параметров кластеризации запрашивается блокировка SHARE UPDATE EXCLUSIVE . SET WITH OIDS

Эта форма добавляет в таблицу системный столбец oid (см. Раздел 5.4). Если в таблице уже есть такой столбец, она не делает ничего.

Заметьте, что это не равнозначно команде ADD COLUMN oid oid (эта команда добавит не системный, а обычный столбец с подходящим именем oid ). SET WITHOUT OIDS

Эта форма удаляет из таблицы системный столбец oid . Это в точности равнозначно DROP COLUMN oid RESTRICT , за исключением того, что в случае отсутствия столбца oid ошибки не будет. SET TABLESPACE

Эта форма меняет табличное пространство таблицы на заданное и перемещает файлы данных, связанные с таблицей, в новое пространство. Индексы таблицы, если они имеются, не перемещаются; однако их можно переместить отдельно дополнительными командами SET TABLESPACE . Форма ALL IN TABLESPACE позволяет перенести в другое табличное пространство все таблицы текущей базы данных, при этом она сначала блокирует все таблицы, а затем переносит каждую из них. Эта форма также поддерживает указание OWNED BY , с которым перемещаются только таблицы указанного владельца. Если указан параметр NOWAIT , команда завершится ошибкой, если не сможет получить все требуемые блокировки немедленно. Заметьте, что системные каталоги эта форма не перемещает; если требуется переместить их, следует использовать ALTER DATABASE или явные вызовы ALTER TABLE . Отношения information_schema не считаются частью системных каталогов и подлежат перемещению. См. также CREATE TABLESPACE . SET

Эта форма меняет характеристику журналирования таблицы, делает таблицу журналируемой/нежурналируемой, соответственно (см. UNLOGGED ). К временной таблице она неприменима. SET ( параметр_хранения [= значение ] [, . ] )

Эта форма меняет один или несколько параметров хранения таблицы. Подробнее допустимые параметры описаны в Параметры хранения. Заметьте, что эта команда не меняет содержимое таблицы немедленно; в зависимости от параметра может потребоваться перезаписать таблицы, чтобы получить желаемый эффект. Это можно сделать с помощью команд VACUUM FULL, CLUSTER или одной из форм ALTER TABLE , принудительно перезаписывающих таблицу. Изменения параметров, связанных с планировщиком, вступают в силу при следующей блокировке таблицы, так что в текущих запросах они не проявляются.

Данная форма затребует блокировку SHARE UPDATE EXCLUSIVE для изменения параметров хранения, связанных с фактором заполнения и автоочисткой, а также параметра планировщика parallel_workers .

Примечание

Хотя CREATE TABLE позволяет указать OIDS в синтаксисе WITH ( параметр_хранения ) , ALTER TABLE не воспринимает OIDS как параметр хранения. Поэтому для изменения характеристики OID следует применять формы SET WITH OIDS и SET WITHOUT OIDS .

RESET ( параметр_хранения [, . ] )

Эта форма сбрасывает один или несколько параметров хранения к значениям по умолчанию. Как и с SET , для полного обновления таблицы может потребоваться перезаписать таблицу. INHERIT таблица_родитель

Эта форма назначает целевую таблицу потомком заданной родительской таблицы. Впоследствии запросы к родительской таблице будут включать записи и целевой таблицы. Чтобы таблица могла стать потомком, она должна содержать те же столбцы, что и родительская (хотя она может включать и дополнительные столбцы). Столбцы должны иметь одинаковые типы данных и, если в родительской таблице какие-то из них имеют ограничение NOT NULL , они должны иметь ограничение NOT NULL и в таблице-потомке.

Также в таблице-потомке должны присутствовать все ограничения CHECK родительской таблицы, за исключением ненаследуемых (то есть созданных командой ALTER TABLE . ADD CONSTRAINT . NO INHERIT ), которые игнорируются; при этом все соответствующие ограничения в таблице-потомке не должны быть ненаследуемыми. В настоящее время ограничения UNIQUE , PRIMARY KEY и FOREIGN KEY не учитываются, но в будущем это может измениться. NO INHERIT таблица_родитель

Эта форма удаляет целевую таблицу из списка потомков указанной родительской таблицы. Результаты запросов к родительской таблице после этого не будут включать записи, взятые из целевой таблицы. OF имя_типа

Эта форма связывает таблицу с составным типом, как если бы она была сформирована командой CREATE TABLE OF . При этом список имён и типов столбцов должен точно соответствовать тому, что образует составной тип; отличие возможно в системном столбце oid . Кроме того, таблица не должна быть потомком какой-либо другой таблицы. Эти ограничения гарантируют, что команда CREATE TABLE OF позволит создать таблицу с таким же определением. NOT OF

Эта форма разрывает связь типизированной таблицы с её типом. OWNER

Эта форма меняет владельца таблицы, последовательности, представления, материализованного представления или сторонней таблицы на заданного пользователя. REPLICA IDENTITY

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

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

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

Записываются старые значения всех столбцов в строке. NOTHING

Информация о старой строке не записывается. Это режим по умолчанию для системных таблиц.

Формы RENAME меняют имя таблицы (или индекса, последовательности, представления, материализованного представления или сторонней таблицы), имя отдельного столбца таблицы или имя ограничения таблицы. На хранимые данные это не влияет. SET SCHEMA

Эта форма перемещает таблицу в другую схему. Вместе с таблицей перемещаются связанные с ней индексы и ограничения, а также последовательности, принадлежащие столбцам таблицы. ATTACH PARTITION имя_секции FOR VALUES указание_границ_секции

Эта форма присоединяет существующую таблицу (которая сама по себе может быть секционированной) в качестве секции к целевой таблице, с применением того же синтаксиса указание_границ_секции , что и в CREATE TABLE . Указание границ секции должно соответствовать стратегии секционирования и ключу разбиения целевой таблицы. Присоединяемая таблица должна иметь те же столбцы, что и целевая, и никаких других; более того, должны совпадать и типы столбцов. Кроме того, в ней должны быть те же ограничения NOT NULL и CHECK , что и в целевой таблице. В настоящее время ограничения UNIQUE , PRIMARY KEY и FOREIGN KEY не рассматриваются. Если какое-либо из ограничений CHECK присоединяемой таблицы помечено как NO INHERIT , команда выдаст ошибку; такие ограничения нужно будет пересоздать без предложения NO INHERIT .

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

Если новая секция является сторонней таблицей, никакая проверка, удовлетворяют ли все строки сторонней таблицы ограничению секции, не выполняется. (Обсуждение ограничений сторонней таблицы вы можете найти в CREATE FOREIGN TABLE .) DETACH PARTITION имя_секции

Эта форма отсоединяет заданную секцию от целевой таблицы. Отсоединяемая секция продолжит существовать как отдельная таблица, и более не будет иметь никаких связей с таблицей, от которой была отсоединена.

Все виды ALTER TABLE, действующие на одну таблицу, кроме RENAME , SET SCHEMA , ATTACH PARTITION и DETACH PARTITION можно объединить в список множественных изменений и применить вместе. Например, можно добавить несколько столбцов и/или изменить тип столбцов в одной команде. Это особенно полезно для больших таблиц, так как вся таблица обрабатывается за один проход.

Выполнить ALTER TABLE может только владелец соответствующей таблицы. Чтобы сменить схему или табличное пространство таблицы, необходимо также иметь право CREATE в новой схеме или табличном пространстве. Чтобы сделать таблицу потомком другой таблицы, нужно быть владельцем и родительской таблицы. Также, чтобы подсоединить таблицу к другой в качестве секции, необходимо быть владельцем подсоединяемой таблицы. Чтобы сменить владельца, необходимо быть непосредственным или опосредованным членом новой роли-владельца, а эта роль должна иметь право CREATE в схеме таблицы. (С такими ограничениями при смене владельца не происходит ничего такого, что нельзя было бы сделать, имея право удалить и вновь создать таблицу. Однако суперпользователь может сменить владельца таблицы в любом случае.) Чтобы добавить столбец, сменить тип столбца или применить предложение OF , необходимо также иметь право USAGE для соответствующего типа данных.

Параметры

Не считать ошибкой, если таблица не существует. В этом случае будет выдано замечание. имя

Имя (возможно, дополненное схемой) существующей таблицы, подлежащей изменению. Если перед именем таблицы указано ONLY , изменяется только заданная таблица. Без ONLY изменяется и заданная таблица, и все её потомки (если таковые есть). После имени таблицы можно также добавить необязательное указание * , чтобы явно обозначить, что изменению подлежат все дочерние таблицы. имя_столбца

Имя нового или существующего столбца. новое_имя_столбца

Новое имя существующего столбца. новое_имя

Новое имя таблицы. тип_данных

Тип данных нового столбца или новый тип данных существующего столбца. ограничение_таблицы

Новое ограничение таблицы. имя_ограничения

Имя нового или существующего ограничения. CASCADE

Автоматически удалять объекты, зависящие от удаляемого столбца или ограничения (например, представления, содержащие этот столбец), и, в свою очередь, все зависящие от них объекты (см. Раздел 5.13). RESTRICT

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

Имя включаемого или отключаемого триггера. ALL

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

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

Имя существующего индекса. параметр_хранения

Имя параметра хранения таблицы значение

Новое значение параметра хранения таблицы. Это может быть число или строка, в зависимости от параметра. таблица_родитель

Родительская таблица, с которой будет установлена или разорвана связь данной таблицы. новый_владелец

Имя пользователя, назначаемого новым владельцем таблицы. новое_табл_пространство

Имя табличного пространства, в которое будет перемещена таблица. новая_схема

Имя схемы, в которую будет перемещена таблица. имя_секции

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

Указание границ для новой секции. Подробнее синтаксис этого указания рассматривается в описании CREATE TABLE .

Замечания

Ключевое слово COLUMN не несёт смысловой нагрузки и может быть опущено.

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

Добавление столбца с предложением DEFAULT или изменение типа существующего столбца влечёт за собой перезапись всей таблицы и её индексов. Но возможно исключение при смене типа существующего столбца: если предложение USING не меняет содержимое столбца и старый тип двоично приводится к новому или является неограниченным доменом поверх нового типа, перезапись таблицы не требуется; хотя все индексы с затронутыми столбцами всё же требуется перестроить. При добавлении или удалении системного столбца oid также необходима перезапись всей таблицы. Перестроение больших таблиц и/или их индексов может быть весьма длительной процедурой, которая при этом временно требует вдвое больше места на диске.

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

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

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

Сканирование большой таблицы для проверки нового внешнего ключа или ограничения-проверки может занять длительное время и будет препятствовать внесению других изменений до фиксирования команды ALTER TABLE ADD CONSTRAINT . Основное предназначение указания NOT VALID при добавлении ограничения состоит в уменьшении влияния этой операции на параллельные изменения данных. С указанием NOT VALID команда ADD CONSTRAINT не сканирует таблицу и может быть зафиксирована немедленно. После этого можно выполнить команду VALIDATE CONSTRAINT , которая проверит все существующие строки на соответствие ограничению. Эта команда не будет препятствовать параллельным изменениям, так как ей известно, что в других транзакциях для добавляемых или изменяемых строк ограничение уже будет действовать; проверить нужно только уже существующие строки. Таким образом, для этой проверки в таблице затребуется только блокировка SHARE UPDATE EXCLUSIVE . (Если ограничение является внешним ключом, то в целевой таблице этого ключа также затребуется блокировка ROW SHARE .) Помимо оптимизации параллельной работы, указание NOT VALID и предложение VALIDATE CONSTRAINT полезно в случаях, когда заведомо известно, что в таблице есть строки, нарушающие ограничения. После создания ограничения добавить новые недопустимые строки будет невозможно, а все существующие проблемы могут разрешаться в удобное время, пока VALIDATE CONSTRAINT не выполнится успешно.

Форма DROP COLUMN не удаляет столбец физически, а просто делает его невидимым для операций SQL. При последующих операциях добавления или изменения в этот столбец будет записываться значение NULL. Таким образом, удаление столбца выполняется быстро, но при этом размер таблицы на диске не уменьшается, так как пространство, занимаемое удалённым столбцом, не высвобождается. Это пространство будет освобождено со временем, по мере изменения существующих строк. (При удалении системного столбца oid это поведение не наблюдается, так как немедленно выполняется перезапись таблицы.)

Чтобы принудительно высвободить пространство, занимаемое столбцом, который был удалён, можно выполнить одну из форм ALTER TABLE , производящих перезапись всей таблицы. В результате все строки будут воссозданы так, что в удалённом столбце будет содержаться NULL.

Перезаписывающие формы ALTER TABLE небезопасны с точки зрения MVCC. После перезаписи таблица будет выглядеть пустой для параллельных транзакций, если они работают со снимком, полученным до момента перезаписи. За подробностями обратитесь к Разделу 13.5.

В указании USING предложения SET DATA TYPE на самом деле можно записать выражение со старыми значениями строки; то есть, оно может ссылаться как на преобразуемые столбцы, так и на другие. Это позволяет записывать в SET DATA TYPE очень общие преобразования данных. Ввиду такой гибкости, выражение USING не применяется к значению по умолчанию данного столбца (если таковое есть); результат может быть не константным выражением, что требуется для значения по умолчанию. Это означает, что в случае отсутствия явного приведения или присваивания старого типа новому, SET DATA TYPE может не справиться с преобразованием значения по умолчанию, несмотря на то, что применяется предложение USING . В этих случаях нужно удалить значение по умолчанию с помощью DROP DEFAULT , выполнить ALTER TYPE , а затем с помощью SET DEFAULT задать новое подходящее значение по умолчанию. Подобные соображения применимы и в отношении индексов и ограничений с этим столбцом.

Если у таблицы имеются дочерние таблицы, то добавлять, переименовывать столбцы или менять их тип в родительской таблице, не повторяя ту же операцию в дочерних таблицах, нельзя. Это правило гарантирует, что столбцы в дочерних таблицах всегда соответствуют родительской. Подобным образом, нельзя переименовывать ограничение в родительской таблице, не переименовывая его во всех дочерних таблицах, что тоже гарантирует соответствие всех ограничений. И так как выборка из родительской таблицы влечёт за собой выборку из всех потомков, ограничение родителя не может быть помечено как действующее, если оно также не является действующим в этих потомках. Во всех этих случаях команда ALTER TABLE ONLY не будет выполнена.

Рекурсивная операция DROP COLUMN удалит столбец из дочерней таблицы, только если этот столбец не наследуется от каких-то других родителей и никогда не был определён в дочерней таблице независимо. Нерекурсивная операция DROP COLUMN (т. е., ALTER TABLE ONLY . DROP COLUMN ) никогда не удаляет унаследованные столбцы; вместо этого она помечает их как независимо определённые, а не наследуемые. С секционированной таблицей нерекурсивная команда DROP COLUMN выдаст ошибку, так как все секции таблицы должны содержать те же столбцы, что и главная таблица.

Действия для столбцов идентификации ( ADD GENERATED , SET и т. д., DROP IDENTITY ), а также действия TRIGGER , CLUSTER , OWNER и TABLESPACE никогда не распространяются рекурсивно на дочерние таблицы; то есть они всегда выполняются так, как будто указано ONLY . Операция добавления ограничения выполняется рекурсивно только для ограничений CHECK , не помеченных как NO INHERIT .

Какие-либо изменения таблиц системного каталога не допускаются.

За более подробным описанием допустимых параметров обратитесь к CREATE TABLE . Дополнительно о наследовании можно узнать в Главе 5.

Примеры

Добавление в таблицу столбца типа varchar :

ALTER TABLE distributors ADD COLUMN address varchar(30);

Удаление столбца из таблицы:

ALTER TABLE distributors DROP COLUMN address RESTRICT;

Изменение типов двух существующих столбцов в одной операции:

ALTER TABLE distributors ALTER COLUMN address TYPE varchar(80), ALTER COLUMN name TYPE varchar(100);

Смена типа целочисленного столбца, содержащего время в стиле Unix, на тип timestamp with time zone с применением предложения USING :

ALTER TABLE foo ALTER COLUMN foo_timestamp SET DATA TYPE timestamp with time zone USING timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';

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

ALTER TABLE foo ALTER COLUMN foo_timestamp DROP DEFAULT, ALTER COLUMN foo_timestamp TYPE timestamp with time zone USING timestamp with time zone 'epoch' + foo_timestamp * interval '1 second', ALTER COLUMN foo_timestamp SET DEFAULT now();

Переименование существующего столбца:

ALTER TABLE distributors RENAME COLUMN address TO city;

Переименование существующей таблицы:

ALTER TABLE distributors RENAME TO suppliers;

Переименование существующего ограничения:

ALTER TABLE distributors RENAME CONSTRAINT zipchk TO zip_check;

Добавление в столбец ограничения NOT NULL:

ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;

Удаление ограничения NOT NULL из столбца:

ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;

Добавление ограничения-проверки в таблицу и все её потомки:

ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);

Добавление ограничения-проверки только в таблицу, но не в её потомки:

ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5) NO INHERIT;

(Данное ограничение-проверка не будет наследоваться и будущими потомками тоже.)

Удаление ограничения-проверки из таблицы и из всех её потомков:

ALTER TABLE distributors DROP CONSTRAINT zipchk;

Удаление ограничения-проверки только из самой таблицы:

ALTER TABLE ONLY distributors DROP CONSTRAINT zipchk;

(Ограничение-проверка остаётся во всех дочерних таблицах.)

Добавление в таблицу ограничения внешнего ключа:

ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address);

Добавление в таблицу ограничения внешнего ключа с наименьшим влиянием на работу других:

ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses (address) NOT VALID; ALTER TABLE distributors VALIDATE CONSTRAINT distfk;

Добавление в таблицу ограничения уникальности (по нескольким столбцам):

ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);

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

ALTER TABLE distributors ADD PRIMARY KEY (dist_id);

Перемещение таблицы в другое табличное пространство:

ALTER TABLE distributors SET TABLESPACE fasttablespace;

Перемещение таблицы в другую схему:

ALTER TABLE myschema.distributors SET SCHEMA yourschema;

Пересоздание ограничения первичного ключа без блокировки изменений в процессе перестроения индекса:

CREATE UNIQUE INDEX CONCURRENTLY dist_id_temp_idx ON distributors (dist_id); ALTER TABLE distributors DROP CONSTRAINT distributors_pkey, ADD CONSTRAINT distributors_pkey PRIMARY KEY USING INDEX dist_id_temp_idx;

Присоединение секции к таблице, разбиваемой по диапазонам:

ALTER TABLE measurement ATTACH PARTITION measurement_y2016m07 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');

Присоединение секции к таблице, разбиваемой по списку:

ALTER TABLE cities ATTACH PARTITION cities_ab FOR VALUES IN ('a', 'b');

Удаление секции из секционированной таблицы:

ALTER TABLE measurement DETACH PARTITION measurement_y2015m12;

Совместимость

Формы ADD (без USING INDEX ), DROP [COLUMN] , DROP IDENTITY , RESTART , SET DEFAULT , SET DATA TYPE (без USING ), SET GENERATED и SET параметр_последовательности соответствуют стандарту SQL. Другие формы являются расширениями стандарта SQL, реализованными в PostgreSQL . Кроме того, расширением является возможность указать в одной команде ALTER TABLE несколько операций изменения.

ALTER TABLE DROP COLUMN позволяет удалить единственный столбец таблицы и оставить таблицу без столбцов. Это является расширением стандарта SQL, который не допускает существование таблиц с нулём столбцов.

См. также

Пред.	Наверх	След.
ALTER SYSTEM	Начало	ALTER TABLESPACE

Синтаксис оператора ALTER TABLE

ALTER [ IGNORE ] TABLE tbl_name
alter_specification [ , alter_specification ] .

index_col_name:
col_name [ ( length ) ] [ ASC | DESC ]

index_type:
USING HASH

Оператор ALTER TABLE позволяет изменять структуру существующей таблицы. Например, вы можете добавлять или удалять колонки, изменять тип существующих колонок, переименовывать колонки, или саму таблицу. Также можно изменить комментарий к таблице и тип таблицы.

Синтаксис оператора ALTER TABLE во многих случаях подобен синтаксису CREATE TABLE . Для получения подробной информации см. раздел 11.1.5, «CREATE TABLE Syntax».

Некоторые операции могут вызвать предупреждения, если совершать их над таблицей, механизм хранения которой не поддерживает такие операции. Эти предупреждения можно просмотреть с помощью оператора SHOW WARNINGS . См. раздел 11.5.4.28, «SHOW WARNINGS Syntax»

Если вы используете оператор ALTER TABLE для изменения колонки, но оператор DESCRIBE tbl_name показывает, что ваша колонка осталась неизменной, то возможно, что сервер MySQL проигнорировал ваши изменения по одной из причин, описанных в разделе 11.1.5.1, «Silent Column Specification Changes»

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

Если вы используете оператор ALTER TABLE tbl_name RENAME TO new_tbl_name без каких-либо других опций, MySQL просто переименует файлы, относящиеся к таблице tbl_name . (Для переименования таблиц вы также можете использовать оператор RENAME TABLE . См. раздел 11.1.9, «RENAME TABLE Syntax».) Права доступа пользователей, относящиеся именно к переименованной таблице, не перенесутся на новое имя. Они должны быть изменены вручную с помощью операторов GRANT и REVOKE .

Если вы используете оператор ALTER TABLE с любыми отличными от RENAME опциями, MySQL всегда создает временную таблицу даже если данные заведомо не требуется копировать (например, когда меняется название колонки). Для таблиц типа MyISAM вы можете ускорить пересоздание индексов (это самая медленная часть в процессе изменения) установкой для системной переменной myisam_sort_buffer_size большей величины.

Информацию о возможных проблемах при использовании оператора ALTER TABLE см. в разделе B.1.7.1. «Problems with ALTER TABLE».

Для выполнения оператора ALTER TABLE необходимо иметь привелегии ALTER , INSERT и CREATE на изменяемую таблицу.
Ключевое слово IGNORE — это расширение MySQL к стандарту SQL. Оно определяет как будет работать оператор ALTER TABLE , если в результате изменения таблицы произойдет дублирование уникального ключа или возникнет предупреджение при работе в SQL-режиме strict. Если ключевое слово IGNORE не указано, то при возникновении ошибки копирование прерывается и откатывается назад. Если указано ключевое слово IGNORE , то используется только первая строка среди строк с дублирующимся уникальным ключом, остальные конфликтующие строки удаляются. Некорректные значения приводятся к ближайшему допустимому значению.
table_option обозначает опции таблицы, которые могут быть использованы в операторе CREATE TABLE , такие как ENGINE , AUTO_INCREMENT или AVG_ROW_LENGTH . (В разделе 12.1.10, «CREATE TABLE Syntax» перечисленны все табличные опции). Однако, оператор ALTER TABLE игнорирует опции DATA DIRECTORY и INDEX DIRECTORY .
Например, для преобразования механизма хранения таблицы к InnoDB используется следующее выражение:

ALTER TABLE t1 ENGINE = InnoDB ;

Успешность попытки изменить механизм хранения таблицы будет зависеть от того, доступен ли назначаемый механизм хранения и устанавлен ли режим SQL NO_ENGINE_SUBSTITUTION , как описано в разделе 5.1.7, «Server SQL Modes».
Начиная с версии MySQL 5.0.23, для предотвращения непреднамеренной потери данных оператор ALTER TABLE не может быть использован для изменения механизма хранения таблицы на MERGE или BLACKHOLE .
Для изменения значения счетчика AUTO_INCREMENT , используемого для нумерации новых строк, выполните следующее выражение:

ALTER TABLE t2 AUTO_INCREMENT = value;
ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;
ALTER TABLE t1 CHANGE a b INTEGER ;

Если требуется изменить только тип колонки, синтаксис конструкции CHANGE все равно требует старого и нового имен колонки, несмотря на то, что они совпадают. Например:

ALTER TABLE t1 CHANGE b b BIGINT NOT NULL ;
Для изменения только типа колонки без переименования можно использовать конструкцию MODIFY :
ALTER TABLE t1 MODIFY b BIGINT NOT NULL ;

Обратите внимание
Такое преобразование может привести к искажению данных. Например, при уменьшении длины для строковой колонки данные могут быть обрезаны. Для предотвращения операций по преобразованию данных, результатом которых станет их потеря, необходимо включить SQL-режим «strict» до использования оператора ALTER TABLE (см. раздел 5.1.6, «SQL Modes»).

Обратите внимание
Механизм хранения InnoDB игнорирует использование конструкции REFERENCES в части определения колонки. Допускается использовать конструкцию REFERENCES только при определении внешнего ключа.

ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;
ALTER TABLE tbl_name DISCARD TABLESPACE ;

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

ALTER TABLE tbl_name IMPORT TABLESPACE ;
ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name;

Для колонок с типом данных VARCHAR или одним из типов семейства TEXT операция CONVERT TO CHARACTER SET изменит тип данных таким образом, чтобы колонка могла содержать столь же много символов как и до смены кодировки. Например, в колонке типа TEXT два байта отводятся на хранение информации о длине данных в байтах, которая в этом случае не может быть больше 65535. В случае кодировки latin1 для хранения каждого символа требуется один байт, таким образом колонка типа TEXT с кодировкой latin1 может содержать до 65535 символов. Если колонку перекодировать в utf8 , то для хранения одного символа потребуется три байта, а для хранения максимально возможного для данного типа количества символов потребуется 3 × 65,535 = 196,605 байт. Данное количество превышает длину данных в байтах, которую может содержать тип данных TEXT , поэтому MySQL преобразует тип данных в MEDIUMTEXT , который является наименьшим из строковых типов, для которого длина данных в байтах может равняться 196,605. Подобным образом колонка типа VARCHAR может быть преобразована в MEDIUMTEXT .
Для предотвращения смены типов данных как описано выше не используйте операцию CONVERT TO CHARACTER SET . Вместо этого используйте MODIFY для изменения отдельных колонок. Например:

ALTER TABLE t MODIFY latin1_text_col TEXT CHARACTER SET utf8;
ALTER TABLE t MODIFY latin1_varchar_col VARCHAR ( M ) CHARACTER SET utf8;

Если вы указывате CONVERT TO CHARACTER SET binary , колонки типов CHAR , VARCHAR и TEXT преобразуются в соответствующие им бинарные строковые типы ( BINARY , VARBINARY , BLOB ). Это означает. что колонки больше не имеют кодировки и последующие операции CONVERT TO не применяются к ним.
Если в качестве charset_name указано значение DEFAULT , будет использована кодировка базы данных.

Обратите внимание
Операция CONVERT TO преобразует данные из одной кодировки в другую. Это может привести к нежелательным последствиям, если колонка имеет одну кодировку (например, latin1 ), а данные реально имеют некоторую другую кодировку, несовместимую с первой (например, utf8 ). В этом случае для каждой подобной колонки необходимо выполнить следующие команды:

ALTER TABLE t1 CHANGE c1 c1 BLOB ;
ALTER TABLE t1 CHANGE c1 c1 TEXT CHARACTER SET utf8;

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

Для изменения только кодировки по умолчанию таблицы используйте следующее выражение:
ALTER TABLE tbl_name DEFAULT CHARACTER SET charset_name;

Используя mysql_info ( ) C API функции, вы можете определить сколько строк было скопировано и (в случае использования ключевого слова IGNORE ) сколько строк было удалено вследствии дублирования уникального ключа. См. раздел 20.9.3.35, “mysql_info()”.

Ниже приведены примеры, иллюстрирующие использование оператора ALTER TABLE . Сначала создадим таблицу t1 как показано ниже:

CREATE TABLE t1 ( a INTEGER ,b CHAR ( 10 ) ) ;

Для переименования таблицы из t1 в t2 :

ALTER TABLE t1 RENAME t2;

Для изменения колонки a с INTEGER на TINYINT NOT NULL (без переименования) и изменения колонки b с CHAR ( 10 ) на CHAR ( 20 ) , одновременно переименуя её из b в c :

ALTER TABLE t2 MODIFY a TINYINT NOT NULL , CHANGE b c CHAR ( 20 ) ;

Для добавления новой колонки d типа TIMESTAMP :

ALTER TABLE t2 ADD d TIMESTAMP ;

Для добавления индекса на колонку d и уникального индекса на колонку a :

ALTER TABLE t2 ADD INDEX ( d ) , ADD UNIQUE ( a ) ;

Для удаления колонки c :

ALTER TABLE t2 DROP COLUMN c;

Для добавления новой целочисленной AUTO_INCREMENT колонки c :

ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT ,
ADD PRIMARY KEY ( c ) ;

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

При добавлении AUTO_INCREMENT колонки она автоматически заполняется последовательными значениями целых чисел. Для MyISAM -таблиц можно указать величину первого значения, выполнив команду SET INSERT_ID=value перед оператором ALTER TABLE или используя табличную опцию AUTO_INCREMENT =value . См. раздел 5.1.4, “Session System Variables”.

В случае MyISAM -таблиц если не происходит смены AUTO_INCREMENT колонки нумерация чисел не меняется. Если сначала удалить AUTO_INCREMENT колонку, а затем создать новую AUTO_INCREMENT колонку, то её заполнение начнется с единицы.

При использовании репликации добавление AUTO_INCREMENT колонки не гарантирует одинаковый порядок строк на мастере и slave. Это поисходит вследствии того, что порядок нумерации строк зависит от особенностей механизма хранения используемого для таблицы и того в каком порядке были добавлены строки в таблицу. Если важно иметь одинаковый порядок строк на мастере и slave, строки должны быть упорядочены до назначения AUTO_INCREMENT номера. Предположим, что вы хотите добавить AUTO_INCREMENT колонку к таблице t1 , следующий оператор новую таблицу t2 идентичную t1 , но имеющую AUTO_INCREMENT колонку:

CREATE TABLE t2 ( id INT AUTO_INCREMENT PRIMARY KEY )
SELECT * FROM t1 ORDER BY col1, col2;

Здесь предполагается, что таблица t1 имеет колонки col1 и col2 .

Следующие операторы также создают новую таблицу t2 идентичную t1 , но имеющую AUTO_INCREMENT колонку:

CREATE TABLE t2 LIKE t1;
ALTER TABLE T2 ADD id INT AUTO_INCREMENT PRIMARY KEY ;
INSERT INTO t2 SELECT * FROM t1 ORDER BY col1, col2;

Обратите внимание
Для гарантии одинакового порядка строк на мастере и slave все колонки таблицы t1 должны быть перечислены в части ORDER BY .

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

DROP t1;
ALTER TABLE t2 RENAME t1;
Дата публикации: 26.10.2009

Что такое alter table

Инструкция ALTER TABLE

Синтаксис

Замечания

SQL оператор ALTER TABLE

Описание

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

Синтаксис

Пример

Что такое alter table

Синтаксис

Описание

Примечание

Примечание

Параметры

Замечания

Примеры

Совместимость

См. также

Синтаксис оператора ALTER TABLE

Добавить комментарий Отменить ответ