Alter table sql синтаксис. ALTER имя_столбца SET DEFAULT значение_по_умолчанию. Перестроение индексов в режиме «в сети»

ALTER TABLE tbl_name alter_spec [, alter_spec ...] alter_specification: ADD create_definition или ADD (create_definition, create_definition,...) или ADD INDEX (index_col_name,...) или ADD PRIMARY KEY (index_col_name,...) или ADD UNIQUE (index_col_name,...) или ADD FULLTEXT (index_col_name,...) или ADD FOREIGN KEY index_name (index_col_name,...) или ALTER col_name {SET DEFAULT literal | DROP DEFAULT} или CHANGE old_col_name create_definition или MODIFY create_definition или DROP col_name или DROP PRIMARY KEY или DROP INDEX index_name или DISABLE KEYS или ENABLE KEYS или RENAME new_tbl_name или ORDER BY col или table_options

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

Если оператор ALTER TABLE используется для изменения определения типа столбца, но DESCRIBE tbl_name показывает, что столбец не изменился, то, возможно, MySQL игнорирует данную модификацию по одной из причин, описанных в разделе section 6.5.3.1 Молчаливые изменения определений столбцов . Например, при попытке изменить столбец VARCHAR на CHAR MySQL будет продолжать использовать VARCHAR , если данная таблица содержит другие столбцы с переменной длиной.

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

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

Для использования оператора ALTER TABLE необходимы привилегии ALTER , INSERT и CREATE для данной таблицы.
Опция IGNORE является расширением MySQL по отношению к ANSI SQL92. Она управляет работой ALTER TABLE при наличии дубликатов уникальных ключей в новой таблице. Если опция IGNORE не задана, то для данной копии процесс прерывается и происходит откат назад. Если IGNORE указывается, тогда для строк с дубликатами уникальных ключей только первая строка используется, а остальные удаляются.
Можно запустить несколько выражений ADD , ALTER , DROP и CHANGE в одной команде ALTER TABLE . Это является расширением MySQL по отношению к ANSI SQL92, где допускается только одно выражение из упомянутых в одной команде ALTER TABLE .
Опции CHANGE col_name , DROP col_name и DROP INDEX также являются расширениями MySQL по отношению к ANSI SQL92.
Опция MODIFY представляет собой расширение Oracle для команды ALTER TABLE .
Необязательное слово COLUMN представляет собой ``белый шум"" и может быть опущено.
При использовании ALTER TABLE имя_таблицы RENAME TO новое_имя без каких-либо других опций MySQL просто переименовывает файлы, соответствующие заданной таблице. В этом случае нет необходимости создавать временную таблицу. See section 6.5.5 Синтаксис оператора RENAME TABL E .
В выражении create_definition для ADD и CHANGE используется тот же синтаксис, что и для CREATE TABLE . Следует учитывать, что этот синтаксис включает имя столбца, а не просто его тип. See section 6.5.3 Синтаксис оператора CREATE TABLE .
Столбец можно переименовывать, используя выражение CHANGE имя_столбца create_definition . Чтобы сделать это, необходимо указать старое и новое имена столбца и его тип в настоящее время. Например, чтобы переименовать столбец INTEGER из a в b , можно сделать следующее: mysql> ALTER TABLE t1 CHANGE a b INTEGER; При изменении типа столбца, но не его имени синтаксис выражения CHANGE все равно требует указания обоих имен столбца, даже если они одинаковы. Например: mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL; Однако начиная с версии MySQL 3.22.16a можно также использовать выражение MODIFY для изменения типа столбца без переименовывания его: mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;
При использовании CHANGE или MODIFY для того, чтобы уменьшить длину столбца, по части которого построен индекс (например, индекс по первым 10 символам столбца VARCHAR), нельзя сделать столбец короче, чем число проиндексированных символов.
При изменении типа столбца с использованием CHANGE или MODIFY MySQL пытается преобразовать данные в новый тип как можно корректнее.
В версии MySQL 3.22 и более поздних можно использовать FIRST или ADD ... AFTER имя_столбца для добавления столбца на заданную позицию внутри табличной строки. По умолчанию столбец добавляется в конце. Начиная с версии MySQL 4.0.1, можно также использовать ключевые слова FIRST и AFTER в опциях CHANGE или MODIFY .
Опция ALTER COLUMN задает для столбца новое значение по умолчанию или удаляет старое. Если старое значение по умолчанию удаляется и данный столбец может принимать значение NULL , то новое значение по умолчанию будет NULL . Если столбец не может быть NULL , то MySQL назначает значение по умолчанию так, как описано в разделе section 6.5.3 Синтаксис оператора CREATE TABLE .
Опция DROP INDEX удаляет индекс. Это является расширением MySQL по отношению к ANSI SQL92. See section 6.5.8 Синтаксис оператора DROP INDEX .
Если столбцы удаляются из таблицы, то эти столбцы удаляются также и из любого индекса, в который они входят как часть. Если все столбцы, составляющие индекс, удаляются, то данный индекс также удаляется.
Если таблица содержит только один столбец, то этот столбец не может быть удален. Вместо этого можно удалить данную таблицу, используя команду DROP TABLE .
Опция DROP PRIMARY KEY удаляет первичный индекс. Если такого индекса в данной таблице не существует, то удаляется первый индекс UNIQUE в этой таблице. (MySQL отмечает первый уникальный ключ UNIQUE как первичный ключ PRIMARY KEY , если никакой другой первичный ключ PRIMARY KEY не был явно указан). При добавлении UNIQUE INDEX или PRIMARY KEY в таблицу они хранятся перед остальными неуникальными ключами, чтобы можно было определить дублирующиеся ключи как можно раньше.
Опция ORDER BY позволяет создавать новую таблицу со строками, размещенными в заданном порядке. Следует учитывать, что созданная таблица не будет сохранять этот порядок строк после операций вставки и удаления. В некоторых случаях такая возможность может облегчить операцию сортировки в MySQL, если таблица имеет такое расположение столбцов, которое вы хотели бы иметь в дальнейшем. Эта опция в основном полезна, если заранее известен определенный порядок, в котором преимущественно будут запрашиваться строки. Использование данной опции после значительных преобразований таблицы дает возможность получить более высокую производительность.
При использовании команды ALTER TABLE для таблиц MyISAM все неуникальные индексы создаются в отдельном пакете (подобно REPAIR). Благодаря этому команда ALTER TABLE при наличии нескольких индексов будет работать быстрее.
Начиная с MySQL 4.0, вышеуказанная возможность может быть активизирована явным образом. Команда ALTER TABLE ... DISABLE KEYS блокирует в MySQL обновление неуникальных индексов для таблиц MyISAM . После этого можно применить команду ALTER TABLE ... ENABLE KEYS для воссоздания недостающих индексов. Так как MySQL делает это с помощью специального алгоритма, который намного быстрее в сравнении со вставкой ключей один за другим, блокировка ключей может дать существенное ускорение на больших массивах вставок.
Применяя функцию C API mysql_info() , можно определить, сколько записей было скопировано, а также (при использовании IGNORE) - сколько записей было удалено из-за дублирования значений уникальных ключей.
Выражения FOREIGN KEY , CHECK и REFERENCES фактически ничего не делают. Они введены только из соображений совместимости, чтобы облегчить перенос кода с других серверов SQL и запуск приложений, создающих таблицы со ссылками. See section 1.9.4 Отличия MySQL от ANSI SQL92 .

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

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

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

Mysql> ALTER TABLE t1 RENAME t2;

Для того чтобы изменить тип столбца с INTEGER на TINYINT NOT NULL (оставляя имя прежним) и изменить тип столбца b с CHAR(10) на CHAR(20) с переименованием его с b на c:

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

Для того чтобы добавить новый столбец TIMESTAMP с именем d:

Mysql> ALTER TABLE t2 ADD d TIMESTAMP;

Для того чтобы добавить индекс к столбцу d и сделать столбец a первичным ключом:

Mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);

Для того чтобы удалить столбец c:

Mysql> ALTER TABLE t2 DROP COLUMN c;

Для того чтобы добавить новый числовой столбец AUTO_INCREMENT с именем c:

Mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT, ADD INDEX (c);

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

При добавлении столбца AUTO_INCREMENT значения этого столбца автоматически заполняются последовательными номерами (при добавлении записей). Первый номер последовательности можно установить путем выполнения команды SET INSERT_ID=# перед ALTER TABLE или использования табличной опции AUTO_INCREMENT = # . See section 5.5.6 Синтаксис команды SET .

Если столбец AUTO_INCREMENT для таблиц MyISAM , не изменяется, то номер последовательности остается прежним. При удалении столбца AUTO_INCREMENT и последующем добавлении другого столбца AUTO_INCREMENT номера будут начинаться снова с 1 .

Манипулирование таблицами - один из самых распространенных видов деятельности, который администраторы баз данных и программисты осуществляют при работе с объектами баз. В этом разделе подробно описывается, как нужно создавать и изменять таблицы.

Стандарт ANSI является чем-то вроде наименьшего общего знаменателя для всех производителей, хотя не каждая возможность стандартной версии инструкций CREATE TABLE и ALTER TABLE реализуется каждым производителем. Тем не менее стандарт ANSI представляет собой базовую форму, которую можно использовать на всех платформах.

В свою очередь, платформы предлагают разнообразные расширения и дополнения к инструкциям CREATE TABLE и ALTER TABLE стандарта ANSI.

Как правило, нужно как следует продумать проект таблицы и способ ее создания. Этот процесс называется проектированием базы данных. Процесс анализа взаимоотношений таблицы с ее собственными данными и другими таблицами в базе данных называется нормализацией. Мы рекомендуем программистам и администраторам баз данных изучить принципы проектирования и нормализации перед тем, как использовать команды CREATE DATABASE.

Синтаксис SQL 2003

При выполнении инструкции CREATE TABLE стандарта SQL 2003 в базе данных создается постоянная или временная таблица. Синтаксис следующий.

CREATE [{LOCAL TEMPORARY) GLOBAL TEMPORARY}] TABLE имя_таблицы (имя_столбца тип_данных атрибуты [, …]) | [имя_столбца WITH OPTIONS опции] | | {SYSTEM GENERATED | USER GENERATED | DERIVED}] [, …]] [определение_таблицы]] имя_таблицы тип_данных атрибуты] | имя_столбца SET DEFAULT значение_по_умолчанию] | имя_столбца DROP DEFAULT] | имя_столбца ADD SCOPE имя_таблицы | имя_столбца DROP SCOPE {RESTRICT | CASCADE}] | имя_столбца {RESTRICT | CASCADE}] | |

Ключевые слова

TEMPORARY

Объявляется постоянная или временная (TEMPORARY) таблица с локальной (LOCAL) или глобальной (GLOBAL) областью действия. Локальные временные таблицы доступны только из создавшего их сеанса, и они автоматически удаляются, когда завершается создавший их сеанс. Глобальные временные таблицы доступны из всех активных сеансов, но они автоматически удаляются, когда завершается создавший их сеанс. Не уточняйте имена временных таблиц именем схемы.

(имя_столбца тип_данных атрибуты [,])

Определяется список, в котором через запятую перечислены один или несколько столбцов, их типы данных и дополнительные атрибуты, например допустимость значений NULL (nullability). Каждое объявление таблицы должно включать, как минимум, один столбец, для которого можно указать:

имя_столбца

Указывается имя столбца. Оно должно представлять собой идентификатор, допустимый с точки зрения правил конкретной СУРБД. Имя должно быть осмысленным!

тип_данных

Связывает со столбцом с именем имя_столбца определенный тип данных. Для тех типов данных, которые позволяют указывать их длину, существует дополнительный параметр длина, например VARCHAR(255). Тип данных должен быть допустимым в СУРБД. За полным описанием допустимых типов данных и их вариантов у конкретных производителей обращайтесь к главе 2. атрибуты

Связывает со столбцом указанные атрибуты-ограничения. Для одного столбца с именем имя_столбца можно указывать несколько атрибутов. Запятые не требуются. К типичным атрибутам ANSI относятся следующие.

NOT NULL

В столбце запрещаются значения NULL (или разрешаются, если предложение NOT NULL опущено). Любые инструкции INSERT и UPDATE, которые попытаются поместить значение NULL, в столбец с атрибутом NOT NULL не будут выполнены, и произойдет откат.

DEFAULT выражение

Столбец будет использовать значение выражения, если инструкция INSERT или UPDATE не вводит никакого значения. Выражение должно быть допустимым для типа данных столбца; например, в столбце типа INTEGER нельзя использовать никакие буквенные символы. Выражение может представлять собой строку или числовой литерал, но вы также можете указать пользовательскую или системную функцию. Стандарт SQL 2003 позволяет использовать в предложении DEFAULT следующие системные функции: NULL, USER, CURRENTJJSER, SESSION_USER, SYSTEMJJSER, CURRENT_PATH, CURRENT_D А ТЕ, CURRENTJIME, LOCALTIME, CURRENTJIMESTAMP, LOCALTJMESTAMP, ARRAY или ARRAY.

COLLATE имя_сопоставления

Определяется используемое сопоставление (collation), то есть порядок сортировки в соответствующем столбце. Имя сопоставления зависит от платформы. Если имя сопоставления не определяется, по умолчанию принимается сопоставление по набору символов, используемому в столбце. REFERENCES ARE CHECKED Параметр определяет, будут ли проверяться ссылки в столбце REF, определенном с опцией области действия (scope). Дополнительное предложение ON DELETE определяет, будут ли значения в записях, на которые ссылалась удаленная запись, устанавливаться в NULL или же на выполнение операции будет наложено ограничение.

CONSTRAINT имя ограничения [тип_ограничения [ограничение]]

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

имя_столбца

Столбец определяется со специальными опциями, такими, как опция области действия (scope), опция значения по умолчанию (default), ограничением уровня столбца или предложением COLLATE. Во многих реализациях предложение WITH OPTIONS ограничивается созданием объектных (typed) таблиц.

LIKE имя_таблицы

Новая таблица создается с теми же определениями столбцов, что и в существующей таблице имя_таблицы.

REFIS имя_столбца {SYSTEM GENERATED | USER GENERATED DERIVED]

Определяется столбец объектных идентификаторов (object identifier, OID) в объектных (typed) таблицах. Объектный идентификатор является необходимым для таблицы, являющейся корневой в иерархии таблиц. В соответствии с этим параметром столбец REF может генерироваться системой автоматически (SYSTEM GENERATED), вручную указываться пользователем при вводе строки (USER GENERATED) или создаваться на основе другого столбца REF (DERIVED). Параметр требует включать в столбец имя_стол6ца атрибут REFERENCES.

CONSTRAINT тип ограничения [имя ограничения] [, …]

Таблице присваивается одно или несколько ограничений. Этот параметр заметно отличается от ограничений уровня столбца, поскольку предполагается, что ограничения уровня столбца применяются только к столбцу, с которым они связаны. В случае ограничений уровня таблицы существует возможность связать с ограничением несколько столбцов. Например, в таблице продаж вам может понадобиться объявить уникальное ограничение на сцепленный ключ store_id, order_id и order_date. Сделать это можно только при помощи ограничения уровня таблицы. За подробным обсуждением ограничений обращайтесь к главе 2.

OF имя_типа [определение_таблицы]

Объявляется, что таблица основывается на готовом пользовательском типе. В этой ситуации таблица может иметь только один столбец для каждого атрибута структурированного типа плюс дополнительный столбец, определенный в предложении REF IS. Тип данных REF подробно описывается в разделе «Инструкция CREATE/ALTER ТУРЕ». Это предложение несовместимо с предложением LIKE имя_таблицы. Где:

UNDER супертаблица [определение/таблицы]

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

ON COMMIT {PRESERVE ROWS DELETE ROWS]

Предложение ON COMMIT PRESERVE ROWS сохраняет строки данных временной таблицы при выполнении инструкции COMMIT. Предложение ON COMMIT DELETE RO WS удаляет все строки данных во временной таблице при выполнении инструкции COMMIT.

ADD имя_столбца тип_данных атрибуты

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

ALTER имя_столбца SET DEFAULT значение_по_умолчанию

В столбец добавляется значение по умолчанию (если оно не существует) или изменяется существующее значение.

ALTER имя_столбца DROP DEFAULT

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

ALTER имя_столбца ADD SCOPE имя_таблицы

В указанный столбец добавляется область действия (scope). Область действия представляет собой ссылку на пользовательский тип данных.

ALTER имя_столбца DROP SCOPE

Область действия удаляется из указанного столбца. Предложения RESTRICT и CASCADE объясняются в конце данного списка.

DROP COLUMN имя_столбца

Указанный столбец удаляется из таблицы. Предложения RESTRICT и CASCADE объясняются в конце данного списка.

ADD табличное_ограничение

В таблицу добавляется ограничение с указанным именем и характеристиками.

DROP CONSTRAINT имя ограничения

Существующее ограничение удаляется из таблицы.

RESTRICT

При указании этого предложения СУРБД отменяет команду, если находит в базе данных объекты, зависящие от данного объекта.

При указании этого предложения СУРБД удаляет все прочие объекты, зависящие от данного объекта.

ALTER TABLE tbl_name alter_spec [, alter_spec ...]

alter_specification:

ADD create_definition

или ADD (create_definition, create_definition,...)

или ADD INDEX (index_col_name,...)

или ADD PRIMARY KEY (index_col_name,...)

или ADD UNIQUE (index_col_name,...)

или ADD FULLTEXT (index_col_name,...)

или ADD FOREIGN KEY index_name (index_col_name,...)

или ALTER col_name {SET DEFAULT literal | DROP DEFAULT}

или CHANGE old_col_name create_definition

или MODIFY create_definition

или DROP col_name

или DROP PRIMARY KEY

или DROP INDEX index_name

или DISABLE KEYS

или ENABLE KEYS

или RENAME new_tbl_name

или ORDER BY col

или table_options

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

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

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

Для использования оператора ALTER TABLEнеобходимы привилегииALTER,INSERTиCREATEдля данной таблицы.

Опция IGNOREявляется расширением MySQL по отношению к ANSI SQL92. Она управляет работойALTER TABLEпри наличии дубликатов уникальных ключей в новой таблице. Если опцияIGNOREне задана, то для данной копии процесс прерывается и происходит откат назад. ЕслиIGNOREуказывается, тогда для строк с дубликатами уникальных ключей только первая строка используется, а остальные удаляются.

Можно запустить несколько выражений ADD,ALTER,DROPиCHANGEв одной командеALTER TABLE. Это является расширением MySQL по отношению к ANSI SQL92, где допускается только одно выражение из упомянутых в одной командеALTER TABLE.

Опции CHANGE col_name,DROP col_nameиDROP INDEXтакже являются расширениями MySQL по отношению к ANSI SQL92.

Опция MODIFYпредставляет собой расширение Oracle для командыALTER TABLE.

Необязательное слово COLUMNпредставляет собой ""белый шум"" и может быть опущено.

При использовании ALTER TABLE имя_таблицы RENAME TO новое_имябез каких-либо других опций MySQL просто переименовывает файлы, соответствующие заданной таблице. В этом случае нет необходимости создавать временную таблицу. В выраженииcreate_definitionдляADDиCHANGEиспользуется тот же синтаксис, что и дляCREATE TABLE. Следует учитывать, что этот синтаксис включает имя столбца, а не просто его тип.

Столбец можно переименовывать, используя выражение CHANGE имя_столбца create_definition. Чтобы сделать это, необходимо указать старое и новое имена столбца и его тип в настоящее время. Например, чтобы переименовать столбецINTEGERизaвb, можно сделать следующее:

mysql> ALTER TABLE t1 CHANGE a b INTEGER;

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

mysql> ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;

Однако, начиная с версии MySQL 3.22.16a, можно также использовать выражение MODIFYдля изменения типа столбца без переименовывания его:

mysql> ALTER TABLE t1 MODIFY b BIGINT NOT NULL;

При использовании CHANGEилиMODIFYдля того, чтобы уменьшить длину столбца, по части которого построен индекс (например, индекс по первым 10 символам столбцаVARCHAR), нельзя сделать столбец короче, чем число проиндексированных символов.

При изменении типа столбца с использованием CHANGEилиMODIFYMySQL пытается преобразовать данные в новый тип как можно корректнее.

В версии MySQL 3.22 и более поздних можно использовать FIRSTилиADD ... AFTER имя_столбцадля добавления столбца на заданную позицию внутри табличной строки. По умолчанию столбец добавляется в конце. Начиная с версии MySQL 4.0.1, можно также использовать ключевые словаFIRSTиAFTERв опцияхCHANGEилиMODIFY.

Опция ALTER COLUMNзадает для столбца новое значение по умолчанию или удаляет старое. Если старое значение по умолчанию удаляется и данный столбец может принимать значениеNULL, то новое значение по умолчанию будетNULL. Если столбец не может бытьNULL, то MySQL назначает значение по умолчанию. ОпцияDROP INDEXудаляет индекс. Это является расширением MySQL по отношению к ANSI SQL92. Если столбцы удаляются из таблицы, то эти столбцы удаляются также и из любого индекса, в который они входят как часть. Если все столбцы, составляющие индекс, удаляются, то данный индекс также удаляется.

Если таблица содержит только один столбец, то этот столбец не может быть удален. Вместо этого можно удалить данную таблицу, используя команду DROP TABLE.

Опция DROP PRIMARY KEYудаляет первичный индекс. Если такого индекса в данной таблице не существует, то удаляется первый индексUNIQUEв этой таблице. (MySQL отмечает первый уникальный ключUNIQUEкак первичный ключPRIMARY KEY, если никакой другой первичный ключPRIMARY KEYне был явно указан). При добавленииUNIQUE INDEXилиPRIMARY KEYв таблицу они хранятся перед остальными неуникальными ключами, чтобы можно было определить дублирующиеся ключи как можно раньше.

Опция ORDER BYпозволяет создавать новую таблицу со строками, размещенными в заданном порядке. Следует учитывать, что созданная таблица не будет сохранять этот порядок строк после операций вставки и удаления. В некоторых случаях такая возможность может облегчить операцию сортировки в MySQL, если таблица имеет такое расположение столбцов, которое Вы хотели бы иметь в дальнейшем. Эта опция в основном полезна, если заранее известен определенный порядок, в котором преимущественно будут запрашиваться строки. Использование данной опции после значительных преобразований таблицы дает возможность получить более высокую производительность.

При использовании команды ALTER TABLEдля таблицMyISAMвсе неуникальные индексы создаются в отдельном пакете (подобноREPAIR). Благодаря этому командаALTER TABLEпри наличии нескольких индексов будет работать быстрее.

Начиная с MySQL 4.0, вышеуказанная возможность может быть активизирована явным образом. Команда ALTER TABLE ... DISABLE KEYSблокирует в MySQL обновление неуникальных индексов для таблицMyISAM. После этого можно применить командуALTER TABLE ... ENABLE KEYSдля воссоздания недостающих индексов. Так как MySQL делает это с помощью специального алгоритма, который намного быстрее в сравнении со вставкой ключей один за другим, блокировка ключей может дать существенное ускорение на больших массивах вставок.

Применяя функцию C API mysql_info(), можно определить, сколько записей было скопировано, а также (при использованииIGNORE) - сколько записей было удалено из-за дублирования значений уникальных ключей.

Выражения FOREIGN KEY,CHECKиREFERENCESфактически ничего не делают. Они введены только из соображений совместимости, чтобы облегчить перенос кода с других серверов SQL и запуск приложений, создающих таблицы со ссылками.

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

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

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

mysql> ALTER TABLE t1 RENAME t2;

Для того чтобы изменить тип столбца с INTEGERнаTINYINT NOT NULL(оставляя имя прежним) и изменить тип столбцаbсCHAR(10)наCHAR(20)с переименованием его сbнаc:

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

Для того чтобы добавить новый столбец TIMESTAMPс именемd:

mysql> ALTER TABLE t2 ADD d TIMESTAMP;

Для того чтобы добавить индекс к столбцу dи сделать столбецa первичным ключом:

mysql> ALTER TABLE t2 ADD INDEX (d), ADD PRIMARY KEY (a);

Для того чтобы удалить столбец c:

mysql> ALTER TABLE t2 DROP COLUMN c;

Для того чтобы добавить новый числовой столбец AUTO_INCREMENTс именемc:

mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,

Заметьте, что столбец cиндексируется, так как столбцыAUTO_INCREMENTдолжны быть индексированы; кроме того, столбецcобъявляется какNOT NULL, поскольку индексированные столбцы не могут бытьNULL.

При добавлении столбца AUTO_INCREMENTзначения этого столбца автоматически заполняются последовательными номерами (при добавлении записей). Первый номер последовательности можно установить путем выполнения командыSET INSERT_ID=#передALTER TABLEили использования табличной опцииAUTO_INCREMENT = #.

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

Синтаксис

ALTER TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name { ALTER COLUMN column_name { [ type_schema_name. ] type_name [ ({ precision [ , scale ] | max | xml_schema_collection }) ] [ COLLATE collation_name ] [ NULL | NOT NULL ] [ SPARSE ] | {ADD | DROP } { ROWGUIDCOL | PERSISTED | NOT FOR REPLICATION | SPARSE } } | [ WITH { CHECK | NOCHECK } ] | ADD { | | | } [ ,...n ] | DROP { [ CONSTRAINT ] { constraint_name [ WITH ( [ ,...n ]) ] } [ ,...n ] | COLUMN { column_name } [ ,...n ] } [ ,...n ] | [ WITH { CHECK | NOCHECK } ] { CHECK | NOCHECK } CONSTRAINT { ALL | constraint_name [ ,...n ] } | { ENABLE | DISABLE } TRIGGER { ALL | trigger_name [ ,...n ] } | { ENABLE | DISABLE } CHANGE_TRACKING [ WITH (TRACK_COLUMNS_UPDATED = { ON | OFF }) ] | SWITCH [ PARTITION source_partition_number_expression ] TO target_table [ PARTITION target_partition_number_expression ] [ WITH () ] | SET (FILESTREAM_ON = { partition_scheme_name | filegroup | "default" | "NULL" }) | REBUILD [ [ WITH ( [ ,...n ]) ] | [ PARTITION = partition_number [ WITH ( [ ,...n ]) ] ] ] | | } [ ; ] -- ALTER TABLE options ::= column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ::= { MAXDOP = max_degree_of_parallelism | ONLINE = { ON | OFF } | MOVE TO { partition_scheme_name (column_name) | filegroup | "default" } } ::= { SET (LOCK_ESCALATION = { AUTO | TABLE | DISABLE }) } ::= { [ { ENABLE | DISABLE } FILETABLE_NAMESPACE ] [ SET (FILETABLE_DIRECTORY = directory_name) ] } ::= { SORT_IN_TEMPDB = { ON | OFF } | MAXDOP = max_degree_of_parallelism | DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE} } | ONLINE = { ON [() ] | OFF } } ::= { WAIT_AT_LOW_PRIORITY (MAX_DURATION = [ MINUTES ], ABORT_AFTER_WAIT = { NONE | SELF | BLOCKERS }) }

Аргументы

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

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

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

ALTER COLUMN
Указывает, что именованный столбец подлежит изменению.

Нельзя изменять следующие столбцы.

Столбец с типом данных timestamp.

Свойство ROWGUIDCOL для таблицы.

Вычисляемый столбец или используемый в вычисляемом столбце.

Используемый в статистике, сформированной с помощью инструкции CREATE STATISTICS, если только столбец не принадлежит к типу данных varchar, nvarchar или varbinary, тип данных не изменялся и новый размер больше старого или равен ему, а значение столбца не заменялось значением NULL. Во-первых, удалите статистику, используя инструкцию DROP STATISTICS. Статистика, автоматически сформированная оптимизатором запросов, автоматически удаляется инструкцией ALTER COLUMN.

Используется в ограничении PRIMARY KEY или REFERENCES.

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

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

Тип данных столбцов text, ntext и image может быть изменен только следующими способами:
- text на varchar(max), nvarchar(max) или xml;
  
  ntext на varchar(max), nvarchar(max) или xml;
  
  image в varbinary(max)
Некоторые изменения типов данных могут повлечь за собой изменения в данных. Например, изменение столбца типа nchar или типа nvarchar на char или varchar может вызвать преобразование символов национальных алфавитов. Дополнительные сведения см. в разделе Функции CAST и CONVERT (Transact-SQL) . Снижение точности или масштаба столбца может привести к усечению данных.

Нельзя изменять тип данных столбца секционированной таблицы.

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

Столбцы, включенные в ограничение первичного ключа, нельзя изменить с NOT NULL на NULL.

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

[ type_schema_name**.** ] type_name
Новый тип данных для изменяемого столбца либо тип данных для добавляемого столбца. Значение type_name нельзя задать для существующих столбцов секционированных таблиц. type_name может быть любым из следующих значений:

Системным типом данных SQL Server.

Псевдонимом типа данных, основанным на системном типе данных SQL Server. Прежде чем псевдонимы типов данных можно будет использовать в определении таблицы, их нужно создать с помощью инструкции CREATE TYPE.

Определяемый пользователем тип.NET Framework и схема, к которой он принадлежит. Чтобы определяемые пользователем типы.NET Framework можно было использовать в определении столбца, их сначала нужно создать с помощью инструкции CREATE TYPE.

Предыдущие типы данных должны быть неявно преобразуемыми в новый тип данных.

type_name не может иметь тип timestamp.

По умолчанию для аргумента ANSI_NULL инструкции ALTER COLUMN всегда установлено значение ON; если не указано иное, столбец может содержать значения NULL.

Аргумент заполнения ANSI_PADDING для инструкции ALTER COLUMN всегда принимает значение ON.

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

Текущая установка для аргумента SET ARITHABORT пропускается. Инструкция ALTER TABLE функционирует аналогично случаю, когда для аргумента ARITHABORT установлено значение ON.

Примечание

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

precision
Точность указанного типа данных. Дополнительные сведения о допустимых значениях точности см. в разделе .

scale
Масштаб указанного типа данных. Дополнительные сведения о допустимых значениях масштаба см. в разделе Точность, масштаб и длина (Transact-SQL) .

max
Применим только к типам данных varchar, nvarchar и varbinary для хранения 2^31-1 байт символов, двоичных данных и данных Юникода.

xml_schema_collection

Применяется только к данным типа xml для связывания схемы XML с этим типом. Перед вводом столбца xml в коллекцию схемы необходимо при помощи CREATE XML SCHEMA COLLECTION создать коллекцию схемы в базе данных.

COLLATE < collation_name >
Задает новые параметры сортировки для изменяемого столбца. Если не указано, столбцу назначаются параметры сортировки, принятые в базе данных по умолчанию. Именем параметров сортировки может быть либо имя параметров сортировки Windows, либо имя параметров сортировки SQL. Список и дополнительные сведения см. в разделах Имя параметров сортировки Windows (Transact-SQL) и Имя параметров сортировки SQL Server (Transact-SQL) .

Предложение COLLATE может быть использовано для изменения параметров сортировки только для столбцов типов данных char, varchar, nchar и nvarchar. Чтобы изменить параметры сортировки столбца пользовательского псевдонима типа данных, необходимо выполнить отдельные инструкции ALTER TABLE, чтобы изменить столбец на системный тип данных SQL Server, изменить параметры сортировки, а затем снова перевести столбец в псевдоним типа данных.

Инструкция ALTER COLUMN не может изменить параметры сортировки, если выполняется одно или несколько из следующих условий:

Если на изменяемый столбец ссылается ограничение CHECK, ограничение FOREIGN KEY или вычисляемые столбцы.

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

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

Дополнительные сведения см. в разделе COLLATE (Transact-SQL) .

NULL | NOT NULL
Указывает, может ли столбец принимать значения NULL. Столбцы, не допускающие значения NULL, могут быть добавлены инструкцией ALTER TABLE только в том случае, если для них указаны значения по умолчанию или если таблица пуста. Ограничение NOT NULL может быть указано для вычисляемых столбцов только в случае, если одновременно указан параметр PERSISTED. Если новый столбец допускает значения NULL, а значение по умолчанию не задано, то новый столбец содержит значение NULL для каждой строки в таблице. Если новый столбец допускает значение NULL, а определение по умолчанию добавляется с новым столбцом, то инструкция WITH VALUES может использоваться для хранения значений по умолчанию в новом столбце для каждой существующей строки в таблице.

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

Значение NULL может указываться в инструкции ALTER COLUMN, чтобы принудить столбец NOT NULL допускать значения NULL, за исключением столбцов в ограничениях PRIMARY KEY. Значение NOT NULL может быть указано в инструкции ALTER COLUMN, только если столбец не содержит значения NULL. Значения NULL следует обновить, присвоив некоторые значения, прежде чем разрешить инструкцию ALTER COLUMN NOT NULL, например:

UPDATE MyTable SET NullCol = N"some_value" WHERE NullCol IS NULL; ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL;

При создании или изменении таблицы инструкцией CREATE TABLE или ALTER TABLE установки базы данных и сеанса изменяются и, возможно, переопределяют разрешение содержать значение NULL для типа данных, используемого в определении столбца. Рекомендуется всегда явно определять невычисляемые столбцы как NULL или NOT NULL.

Если добавляется столбец определяемого пользователем типа данных, то рекомендуется определить для этого столбца то же состояние допустимости значений NULL, что и в определяемом пользователем типе данных и задать для него значение по умолчанию. Дополнительные сведения см. в разделе CREATE TABLE (SQL Server) .

Примечание

Если в инструкции ALTER COLUMN указано значение NULL или NOT NULL, то необходимо также указать параметры new_data_type [(precision [, scale ])].Если тип данных, точность или масштаб не изменялись, укажите текущие значения столбца.

[ {ADD | DROP} ROWGUIDCOL ]

Указывает свойство ROWGUIDCOL, добавленное к указанному столбцу или удаленное из него. Свойство ROWGUIDCOL указывает, что данный столбец является столбцом идентификатора GUID строки. Только один столбец в таблице uniqueidentifier может быть выделен как столбец ROWGUIDCOL, и свойство ROWGUIDCOL может присваиваться только столбцу uniqueidentifier. Нельзя присваивать свойство ROWGUIDCOL столбцу определяемого пользователем типа данных.

Свойство ROWGUIDCOL не обеспечивает уникальности значений, хранимых в столбце, и не формирует автоматически значения для новых строк, вставляемых в таблицу. Для формирования уникальных значений для каждого столбца можно использовать функцию NEWID в инструкциях INSERT или определить функцию NEWID как значение по умолчанию для столбца.

[ {ADD | DROP} PERSISTED ]
Указывает, что свойство PERSISTED добавлено к указанному столбцу или удалено из него. Столбец должен быть вычисляемым столбцом, который задается при помощи детерминированных выражений. Для столбцов, указанных как PERSISTED, компонент Компонент Database Engine физически хранит вычисляемые значения в таблице и обновляет значения при обновлении любого столбца, от которого зависит вычисляемый столбец. Если пометить вычисляемый столбец как PERSISTED, можно создавать индексы по вычисляемым столбцам, которые заданы, являются детерминированными, но не точными выражениями.

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

DROP NOT FOR REPLICATION

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

SPARSE
Указывает, что столбец является разреженным столбцом. Хранилище разреженных столбцов оптимизируется для значений NULL. Для разреженных столбцов нельзя указать параметр NOT NULL. При преобразовании столбцов из разреженных в неразреженные или из неразреженных в разреженные таблица блокируется на протяжении выполнения команды. Возможно, потребуется использование предложения REBUILD для освобождения пространства. Дополнительные ограничения и сведения о разреженных столбцах см. в разделе .

WITH CHECK | WITH NOCHECK
Указывает, удовлетворяют ли данные в таблице недавно добавленному или повторно включенному ограничению FOREIGN KEY или CHECK. Если не указано иное, для новых ограничений предполагается использовать WITH CHECK, а для повторно включенных ограничений - WITH NOCHECK.

Если проверка новых ограничений CHECK или FOREIGN KEY относительно существующих данных не нужна, используйте WITH NOCHECK. За исключением редких случаев делать этого не рекомендуется. Новое ограничение будет проверяться во всех дальнейших обновлениях данных. Любые нарушения ограничений, подавляемые WITH NOCHECK во время добавления ограничения, могут вызвать сбой будущих обновлений, если они обновляют строки данными, не соответствующими ограничениям.

Оптимизатор запросов не рассматривает ограничения, которые определены WITH NOCHECK. Такие ограничения не учитываются до тех пор, пока они не будут повторно включены инструкцией ALTER TABLE table WITH CHECK CHECK CONSTRAINT ALL.

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

DROP { [ CONSTRAINT ] constraint_name | COLUMN column_name }
Указывает, что из таблицы удалено ограничение constraint_name или column_name. Может быть перечислено несколько столбцов и ограничений.

Пользовательское имя ограничения или имя ограничения, предоставленное системой, можно определить с помощью запросов к представлениям каталога sys.check_constraint , sys.default_constraints , sys.key_constraints и sys.foreign_keys .

Невозможно удалить ограничение PRIMARY KEY, если в таблице существует XML-индекс.

Невозможно удалить столбец, если он:

используется в индексе;

используется в ограничениях CHECK, FOREIGN KEY, UNIQUE или PRIMARY KEY;

связан со значением по умолчанию, определенным ключевым словом DEFAULT, или привязан к объекту «значение по умолчанию»;

привязан к правилу.

Примечание

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

WITH
Указывает, что установлен один или несколько параметров удаления кластеризованного ограничения.

MAXDOP = max_degree_of_parallelism

Переопределяет параметр конфигурации max degree of parallelism только на время выполнения операции. Дополнительные сведения см. в разделе .

Используйте параметр MAXDOP для ограничения числа процессоров, применяемых при выполнении параллельных планов. Максимальное число процессоров - 64.

Аргумент max_degree_of_parallelism может иметь одно из следующих значений.

1
Подавляет формирование параллельных планов.

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

0 (по умолчанию)
В зависимости от текущей рабочей нагрузки системы использует реальное или меньшее число процессоров.

Дополнительные сведения см. в разделе .

Примечание

Параллельные операции с индексами доступны не во всех выпусках SQL Server.Дополнительные сведения см. в разделе .

ONLINE = {ON | OFF } <применяемое к drop_clustered_constraint_option>
Определяет, будут ли базовые таблицы и связанные индексы доступны для запросов и изменения данных во время операций с индексами. Значение по умолчанию - OFF. REBUILD может выполняться как операция в режиме ONLINE.

ON
Долгосрочные блокировки таблицы не поддерживаются во время операций с индексами. Во время главной фазы операций с индексами только блокировка с намерением совмещаемого доступа (IS) удерживается в исходной таблице. Это обеспечивает продолжение выполнения запросов или обновлений для базовых таблиц и индексов. В начале операции совмещаемая блокировка (S) исходного объекта поддерживается в течение очень короткого времени. Если создается некластеризованный индекс, то в завершение операции на короткое время запрашивается совмещаемая блокировка (S) для источника. Блокировка типа SCH-M (изменение схемы) запрашивается, когда кластеризованный индекс создается или удаляется в режиме в сети и когда происходит перестроение кластеризованного или некластеризованного индекса. При создании индекса для временной локальной таблицы параметр ONLINE не может принимать значение ON. Допустима только однопотоковая операция перестроения кучи.

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

OFF
Блокировки таблиц применяются во время выполнения операций с индексами. Блокировку изменения схемы (Sch-M) в таблице получает операция с индексами вне сети, которая создает, перестраивает или удаляет кластеризованный индекс либо перестраивает или удаляет некластеризованный индекс. Это предотвращает доступ к базовой таблице всех пользователей во время операции. Операция с индексами вне сети, создающая некластеризованный индекс, получает совмещаемую блокировку (S) в таблице. Это запрещает проводить обновления базовой таблицы, но разрешает проводить операции чтения, например инструкции SELECT. Многопотоковые операции перестроения кучи разрешены.

Дополнительные сведения см. в разделе .

Примечание

Операции с индексами в сети доступны не во всех выпусках SQL Server.Дополнительные сведения см. в разделе .

MOVE TO { partition_scheme_name**(column_name [ 1 ,** ... n] ) | filegroup | "default " }

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

Примечание

В этом контексте default не является ключевым словом.Это идентификатор файловой группы по умолчанию, и поэтому он должен быть заключен в разделители, например: MOVE TO « default» или MOVE TO [ default] .Если указано значение " default" , то параметру QUOTED_IDENTIFIER для текущего сеанса должно быть присвоено значение ON.Это параметр по умолчанию.Дополнительные сведения см. в разделе SET QUOTED_IDENTIFIER (Transact-SQL) .

{ CHECK | NOCHECK } CONSTRAINT
Указывает, включено или отключено ограничение constraint_name. Данный параметр может использоваться только с ограничениями FOREIGN KEY и CHECK. Если указан параметр NOCHECK, то ограничение отключено и будущие вставки или обновления столбца не проверяются относительно условий ограничений. Невозможно отключить ограничения DEFAULT, PRIMARY KEY и UNIQUE.

ALL
Указывает, что все ограничения отключаются при помощи параметра NOCHECK или включаются при помощи параметра CHECK.

{ ENABLE | DISABLE } TRIGGER
Указывает, включено или отключено ограничение trigger_name. Отключенный триггер остается определенным для таблицы; однако при выполнении инструкций INSERT, UPDATE или DELETE относительно таблицы действия в триггере не выполняются до тех пор, пока он не будет включен повторно.

ALL
Указывает, что все триггеры в таблице включены или отключены.

trigger_name
Указывает имя триггера, подлежащего включению или отключению.

{ ENABLE | DISABLE } CHANGE_TRACKING

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

Этот параметр доступен только в том случае, если отслеживание изменений разрешено для базы данных. Дополнительные сведения см. в разделе Параметры ALTER DATABASE SET (Transact-SQL) .

Чтобы разрешить отслеживание изменений, в таблице должен содержаться первичный ключ.

WITH ( TRACK_COLUMNS_UPDATED = { ON | OFF } )

Указывает, производит ли компонент Компонент Database Engine отслеживание обновлений столбцов. Значение по умолчанию - OFF.

SWITCH [ PARTITION source_partition_number_expression ] TO { schema_name ] target_table [ PARTITION target_ partition_number_expression ]

Переключает блок данных одним из следующих способов.

Переназначает все табличные данные как секцию в уже существующей секционированной таблице.

Переключает секции из одной секционированной таблицы в другую.

Переназначает все данные одной секции секционированной таблицы в уже существующую несекционированную таблицу.

Если таблица table является секционированной, то необходимо указать аргумент source_partition_number_expression. Если таблица target_table секционирована, то должен быть указан аргумент target_partition_number_expression. Если происходит переназначение данных таблицы как секции в уже существующую секционированную таблицу или переключение секции с одной секционированной таблицы на другую, то конечная секция уже должна существовать и быть пустой.

Если происходит переназначение данных одной секции для формирования одиночной таблицы, то уже должна быть создана пустая целевая таблица. И исходная таблица или секция, и целевая таблица или секция должны располагаться в одной файловой группе. Соответствующие индексы или секции индексов также должны располагаться в одной и той же файловой группе. К переключаемым секциям применяются многие дополнительные ограничения. Значения table и target_table не могут быть одинаковыми. Значение target_table может быть идентификатором, состоящим из нескольких частей.

Аргументы source_partition_number_expression и target_partition_number_expression являются константными выражениями, которые могут ссылаться на переменные и функции. В их число входят переменные определяемого пользователем типа и определяемые пользователем функции. Они не могут ссылаться на выражения языка Transact-SQL.

Сведения об ограничении SWITCH при использовании репликации см. в разделе .

SET ( FILESTREAM_ON = { partition_scheme_name | filestream_filegroup_name | "default " | "NULL " })

Указывает местоположения хранения данных FILESTREAM.

Инструкция ALTER TABLE с предложением SET FILESTREAM_ON будет выполнена успешно только в том случае, если в таблице отсутствуют столбцы FILESTREAM. Добавить столбцы FILESTREAM можно с помощью второй инструкции ALTER TABLE.

Если указан partition_scheme_name, то применяются правила для CREATE TABLE . Таблица должна быть уже секционирована для строк данных, а схема разделения должна использовать те же функции секционирования и столбцы, что используются в схеме секционирования FILESTREAM.

Аргумент filestream_filegroup_name указывает имя файловой группы FILESTREAM. В файловой группе должен быть определен один файл для файловой группы с помощью инструкции CREATE DATABASE или ALTER DATABASE , иначе возникает ошибка.

"default " указывает файловую группу FILESTREAM с заданным свойством DEFAULT. При отсутствии файловой группы FILESTREAM возникает ошибка.

"NULL " указывает на удаление всех ссылок на файловые группы FILESTREAM для таблицы. Сначала должны быть удалены все столбцы FILESTREAM. Необходимо использовать инструкцию SET FILESTREAM_ON**="NULL "** для удаления всех данных FILESTREAM, связанных с таблицей.

SET ( LOCK_ESCALATION = { AUTO | TABLE | DISABLE } )

Указывает разрешенные методы укрупнения блокировки для таблицы.

AUTO
Этот параметр позволяет Компонент SQL Server Database Engine выбрать гранулярность укрупнения блокировки, подходящую для данной схемы таблицы.
- В секционированных таблицах допускается укрупнение блокировки до секций. После укрупнения блокировки до уровня секции дальнейшее укрупнение до гранулярности TABLE выполняться не будет.
  
  Если таблица не секционирована, то блокировка будет укрупняться до гранулярности TABLE.

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

DISABLE
В большинстве случаев предотвращает укрупнение блокировки. Блокировки уровня таблицы запрещены не полностью. Например, при сканировании таблицы, которая не имеет кластеризованного индекса на уровне изоляции SERIALIZABLE, компонент Компонент Database Engine должен установить блокировку таблицы для защиты целостности данных.

REBUILD
Используйте синтаксис REBUILD WITH для перестроения всей таблицы, включая все секции в секционированную таблицу. Если в таблице содержится кластеризованный индекс, то параметр REBUILD перестраивает его. REBUILD может выполняться как операция в режиме ONLINE.

Используйте синтаксис REBUILD PARTITION для перестроения одной секции в секционированной таблице.

Перестраивает все секции при изменении настройки сжатия секций.

REBUILD WITH ()
Все параметры применяются к таблице с кластеризованным индексом. Если в таблице не содержится кластеризованный индекс, то на структуру кучи влияют только определенные параметры.

Если определенный параметр сжатия не был указан с помощью операции REBUILD, то для секции будет использован текущий параметр сжатия. Для возврата текущего параметра выполните запрос к столбцу data_compression из представления каталога sys.partitions .

Полное описание этих параметров перестроения см. в разделе index_option (Transact-SQL) .

DATA_COMPRESSION

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

NONE
Таблица или указанные секции не сжимаются. Это не относится к таблицам columnstore.

ROW
Таблицы или указанные секции сжимаются, используя сжатие строк. Это не относится к таблицам columnstore.

PAGE
Таблицы или указанные секции сжимаются, используя сжатие страниц. Это не относится к таблицам columnstore.

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

COLUMNSTORE_ARCHIVE

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

Чтобы перестроить несколько секций одновременно, см. в разделе index_option (Transact-SQL) . Если в таблице отсутствует кластеризованный индекс, то при изменении сжатия данных выполняется перестроение кучи и некластеризованных индексов. Дополнительные сведения о сжатии см. в разделе .

ONLINE = { ON | OFF } <применяемое к single_partition_rebuild_option>
Определяет, будут ли отдельная секция базовой таблицы и связанные индексы доступны для запросов и изменения данных во время операций с индексами. Значение по умолчанию - OFF. REBUILD может выполняться как операция в режиме ONLINE.

ON
Долгосрочные блокировки таблицы не поддерживаются во время операций с индексами. Необходимо наличие S-блокировки таблицы в начале перестройки индекса и блокировки Sch-M на таблице в конце перестроения индекса в режиме «в сети». Обе блокировки являются короткими блокировками метаданных, но при этом блокировка изменения схемы (Sch-M) должна ожидать завершения всех блокирующих транзакций. Во время ожидания Sch-M блокирует все другие транзакции, ожидающие за этой блокировкой доступа к одной таблице.

Примечание

Перестроение индекса в режиме «в сети» может задать параметры low_priority_lock_wait, описанные ниже в этом разделе.

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

column_set_name XML COLUMN_SET FOR ALL_SPARSE_COLUMNS

Имя набора столбцов. Набор столбцов представляет собой нетипизированное XML-представление, в котором все разреженные столбцы таблицы объединены в структурированные выходные данные. Набор столбцов не может быть добавлен в таблицу, если в ней содержатся разреженные столбцы. Дополнительные сведения о наборах столбцов см. в разделе .

{ ENABLE | DISABLE } FILETABLE_NAMESPACE

Включает или выключает ограничения для таблицы FileTable, заданные системой. Может использоваться только для таблицы FileTable.

SET (FILETABLE_DIRECTORY = directory_name)

Указывает имя каталога таблицы FileTable, совместимое с Windows. Это имя должно быть уникальным среди всех имен каталогов FileTable в базе данных. Проверка уникальности не учитывает регистр символов независимо от параметров сортировки SQL. Может использоваться только для таблицы FileTable.

WAIT_AT_LOW_PRIORITY

Перестроение индекса в режиме «в сети» должно ожидать операции блокировки в этой таблице. Значение WAIT_AT_LOW_PRIORITY указывает, что операция перестроения индекса в режиме «в сети» будет ожидать блокировки с низким приоритетом, позволяя выполняться другим операциям, пока операция перестроения индекса в режиме «в сети» находится в состоянии ожидания. Пропуск параметра WAIT AT LOW PRIORITY эквивалентен WAIT_AT_LOW_PRIORITY (MAX_DURATION = 0 minutes, ABORT_AFTER_WAIT = NONE).

MAX_DURATION = time [MINUTES ]

Время ожидания (целочисленное значение, указанное в минутах) в течение которого SWITCH или блокировки для операции перестроения индекса в режиме «в сети» будут ожидать с низким приоритетом при выполнении команды DDL. Если операция будет заблокирована на время MAX_DURATION, будет выполнено одно из действий ABORT_AFTER_WAIT. Время MAX_DURATION всегда указывается в минутах, и слово MINUTES можно опустить.

ABORT_AFTER_WAIT = [NONE | SELF | BLOCKERS } ]

NONE (Нет)
Продолжить ожидание блокировки с обычным приоритетом.

SELF
Прекратить операцию SWITCH или операцию DDL по перестроению индекса в сети, выполняемую в данный момент, без предпринятия какого-либо действия.

BLOCKERS
Остановить все пользовательские транзакции, в данный момент блокирующие операцию SWITCH или операцию DDL по перестроению индекса в сети, чтобы можно было продолжить данную операцию.

Требуется разрешение ALTER ANY CONNECTION

Замечания

Чтобы добавить новые строки данных, используйте INSERT . Чтобы удалить строки данных, используйте DELETE или TRUNCATE TABLE . Чтобы изменить значения в существующих строках, используйте UPDATE .

При наличии в кэше процедур каких-либо планов выполнения, ссылающихся на таблицу, инструкция ALTER TABLE помечает их как подлежащие перекомпиляции в их следующем выполнении.

Изменение размера столбца

Длину, точность и масштаб столбца можно изменить, указав новый размер для типа данных столбца в предложении ALTER COLUMN. Если в столбце имеются данные, новый размер не может быть меньше максимального размера данных. Кроме того, столбец нельзя определять в индексе, если тип данных столбца не относится к varchar, nvarchar или varbinary, а индекс не является результатом ограничения PRIMARY KEY. См. пример Р.

Блокировки и инструкция ALTER TABLE

Изменения, указанные в инструкции ALTER TABLE, воплощаются немедленно. Если для изменений требуется модификация строк таблицы, то инструкция ALTER TABLE обновляет эти строки. Инструкция ALTER TABLE получает блокировку модификации схемы (SCH-M) для таблицы, чтобы убедиться, что в процессе изменения другие соединения не ссылаются даже на метаданные таблицы, за исключением операций с индексами в сети, требующих очень короткой блокировки SCH-M в конце. В операции ALTER TABLE...SWITCH запрашивается блокировка и исходной, и целевой таблиц. Изменения, сделанные в таблице, регистрируются в журнале и полностью обратимы. Изменения, затрагивающие все строки в очень больших таблицах, например удаление столбца или (в некоторых выпусках SQL Server) добавление столбца NOT NULL со значением по умолчанию, могут занять длительное время и привести к созданию множества записей в журнале. Данные инструкции ALTER TABLE следует выполнять с той же осторожностью, что и любые инструкции INSERT, UPDATE или DELETE, влияющие на множество строк.

Добавление столбцов NOT NULL в качестве операции в сети

Начиная с SQL Server 2012 Enterprise Edition, добавление столбца NOT NULL со значением по умолчанию является операцией в сети, если значение по умолчанию является константой времени выполнения. Это значит, что операция выполняется почти мгновенно, независимо от количества строк в таблице. Так происходит потому, что существующие строки в таблице не обновляются в ходе операции. Вместо этого значение по умолчанию сохраняется только в метаданных таблицы, и это значение подставляется по мере необходимости в запросах, обращающихся к этим строкам. Такой режим работы действует автоматически. Для реализации операции в сети достаточно синтаксиса ADD COLUMN. Константа времени выполнения - это выражение, которое имеет одно и то же значение во время выполнения для каждой строки в таблице независимо от ее детерминизма. Например, выражение константы «Временные данные» и системная функция GETUTCDATETIME() являются константами времени выполнения. Функции NEWID() и NEWSEQUENTIALID(), напротив, не являются константами времени выполнения, поскольку для каждой строки в таблице создается уникальное значение. Добавление столбца NOT NULL со значением по умолчанию, которое не является константой времени выполнения, всегда выполняется вне сети, и в течение этой операции накладывается монопольная блокировка (SCH-M).

Хотя существующие строки ссылаются на значение, хранящееся в метаданных, значение по умолчанию хранится в строке для всех новых строк, которые вставляются без указания другого значения для столбца. Значение по умолчанию, хранящееся в метаданных, перемещается в существующую строку, когда строка обновляется (даже если в инструкции UPDATE не указан фактический столбец), а также когда перестраивается таблица или кластеризованный индекс.

Столбцы типа varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, hierarchyid, geometry, geography или определяемых пользователем типов CLR нельзя добавлять в операцию в сети. Столбец нельзя добавлять в сети, если в результате такой операции максимальный размер строки превысит ограничение в 8060 байт. В этом случае столбец добавляется в рамках операции вне сети.

Выполнение параллельного плана

В выпуске Microsoft SQL Server 2012 Enterprise и более поздних версиях число процессоров, применяемых для выполнения одной инструкции ALTER TABLE ADD (на базе индекса) CONSTRAINT или DROP (кластеризованный индекс) CONSTRAINT, определяется с помощью параметра конфигурации max degree of parallelism и текущей рабочей нагрузки. Если компонент Компонент Database Engine определяет, что система занята, то перед началом выполнения инструкции степень параллелизма операции автоматически понижается. Можно вручную настроить число процессоров, применяемых для запуска инструкции, указав параметр MAXDOP. Дополнительные сведения см. в разделе .

Секционированные таблицы

Помимо выполнения операций SWITCH, затрагивающих секционированные таблицы, инструкция ALTER TABLE может использоваться для изменения состояния столбцов, ограничений и триггеров секционированной таблицы точно так же, как она используется для несекционированных таблиц. Однако данная инструкция не может использоваться для изменения способа, которым секционируется сама таблица. Чтобы заново секционировать секционированную таблицу, используйте ALTER PARTITION SCHEME и ALTER PARTITION FUNCTION . Кроме того, невозможно изменить тип данных столбца секционированной таблицы.

Ограничения в таблицах с представлениями, привязанными к схемам

К инструкциям ALTER TABLE в таблицах с представлениями, привязанными к схемам, применяются те же ограничения, которые применяются в текущем времени для изменения таблиц с простым индексом. Добавление столбца разрешено. Однако удаление или изменение столбца, участвующего в любом из представлений, привязанных к схемам, не разрешается. Если инструкция ALTER TABLE требует изменения столбца, используемого в привязанном к схеме представлении, то происходит сбой инструкции ALTER TABLE и компонент Компонент Database Engine выдает сообщение об ошибке. Дополнительные сведения о привязке к схеме и индексированных представлениях см. в разделе CREATE VIEW (Transact-SQL) .

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

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

При удалении ограничений индексы, создаваемые как часть ограничения, удаляются. Индексы, создаваемые при помощи инструкции CREATE INDEX, должны удаляться при помощи инструкции DROP INDEX. Инструкция ALTER INDEX может использоваться для перестроения индексной части определения ограничения; не следует удалять и вновь добавлять ограничение при помощи инструкции ALTER TABLE.

Перед удалением столбца необходимо удалить все индексы и ограничения, основанные на столбце.

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

Параметр MOVE TO недопустим для индексированных представлений и некластеризованных индексов.

Схема секционирования или файловая группа уже должна существовать.

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

При удалении кластеризованного индекса можно указать параметр ONLINE = ON, так что транзакция DROP INDEX не будет блокировать запросы к лежащим в основе данным и изменениям в них, а также в связанных с ними некластеризованных индексах.

Параметр ONLINE = ON имеет следующие ограничения.

= ON для кластеризованных индексов, которые также отключены. Отключенные индексы должны удаляться при помощи параметра ONLINE = OFF.

Только один индекс может удаляться единовременно.

Недопустимо использование параметра ONLINE = ON для индексированного представления, некластеризованных индексов или индексов в локальных временных таблицах.

Значение ONLINE = ON недопустимо для индексов columnstore.

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

Примечание

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

Репликация изменений схемы

По умолчанию при выполнении команды ALTER TABLE для опубликованной таблицы в издателе SQL Server Publisher это изменение распространяется для всех подписчиков SQL Server. Эта функция имеет некоторые ограничения и может быть отключена. Дополнительные сведения см. в разделе .

Сжатие данных

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

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

На секционированные таблицы налагаются следующие ограничения.

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

Синтаксис ALTER TABLE

Удаление столбцов NTEXT

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

Перестроение индексов в режиме «в сети».

Для выполнения инструкции DDL для перестроения индекса в режиме «в сети» все активные блокирующие транзакции, выполняемые для конкретной таблицы, должны быть завершены. Если выполняется перестроение индекса в режиме «в сети», то все новые транзакции, готовые к выполнению на данной таблице, блокируются. Хотя продолжительность блокировки для перестроения индекса в режиме «в сети» очень коротка, ожидание завершения всех открытых транзакций на данной таблице и блокировка новых запускаемых транзакций может значительно отразиться на пропускной способности и времени выполнения операции, а также значительно ограничить доступ к базовой таблице. Параметр WAIT_AT_LOW_PRIORITY позволяет администратору базы данных управлять S и Sch-M блокировками, необходимыми для перестроения индекса в режиме «в сети». Доступны 3 варианта. Во всех 3 случаях, если во время ожидания ((MAX_DURATION =n )) нет блокирующих действий, то перестроение индекса в режиме «в сети» выполняется немедленно и без ожидания завершения инструкции DDL.

Поддержка совместимости

В инструкции ALTER TABLE разрешается использовать только имена таблиц, составленные из двух частей (схема.объект). В SQL Server 2014 при задании имени таблицы в приведенных далее форматах во время компиляции возникает ошибка 117.

«сервер.база_данных.схема.таблица»

«.база_данных.схема.таблица»

«..схема.таблица»

В предыдущих версиях при задании формата «сервер.база_данных.схема.таблица» возникала ошибка 4902. Формат «.база_данных.схема.таблица» или «..схема.таблица» обрабатывался успешно.

Чтобы устранить эту проблему, используйте четырехкомпонентный префикс.

Разрешения

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

Разрешения ALTER TABLE применяются к обеим таблицам, затронутым инструкцией ALTER TABLE SWITCH. Любые переключенные данные наследуют защиту целевой таблицы.

Если какой-либо из столбцов в инструкции ALTER TABLE является определяемым пользователем типом для среды CLR или как псевдоним типа данных, то на этот тип требуется разрешение REFERENCES.

Для добавления столбца, который обновляет строки таблицы, требуется разрешение UPDATE для этой таблицы. Например, добавление столбца NOT NULL со значением по умолчанию или добавление столбца идентификаторов, если таблица не пуста.

Примеры

Добавление столбцов и ограничений

В примерах из этого раздела показано добавление в таблицу столбцов и ограничений.

А.Добавление нового столбца

Следующий пример показывает добавление столбца, который допускает значения NULL и не имеет значений, предоставленных через определение DEFAULT. В новом столбце в каждой строке будет значение NULL.

CREATE TABLE dbo.doc_exa (column_a INT) ; GO ALTER TABLE dbo.doc_exa ADD column_b VARCHAR(20) NULL ; GO

Б.Добавление столбца с ограничением

В следующем примере показано добавление нового столбца с ограничением UNIQUE.

CREATE TABLE dbo.doc_exc (column_a INT) ; GO ALTER TABLE dbo.doc_exc ADD column_b VARCHAR(20) NULL CONSTRAINT exb_unique UNIQUE ; GO EXEC sp_help doc_exc ; GO DROP TABLE dbo.doc_exc ; GO

В.Добавление непроверяемого ограничения CHECK к существующему столбцу

В следующем примере к существующему столбцу в таблице добавляется ограничение. Столбец имеет значение, нарушающее это ограничение. Поэтому во избежание проверки ограничения относительно существующих строк, а также для того, чтобы разрешить добавление ограничения, применяется WITH NOCHECK.

CREATE TABLE dbo.doc_exd (column_a INT) ; GO INSERT INTO dbo.doc_exd VALUES (-1) ; GO ALTER TABLE dbo.doc_exd WITH NOCHECK ADD CONSTRAINT exd_check CHECK (column_a > 1) ; GO EXEC sp_help doc_exd ; GO DROP TABLE dbo.doc_exd ; GO

Г.Добавление ограничения DEFAULT к существующему столбцу

Следующий пример показывает создание таблицы с двумя столбцами и заполнение значениями первого столбца; в другом столбце остается NULL. В таком случае во второй столбец добавляется ограничение DEFAULT. Чтобы проверить применение значения по умолчанию, в первый столбец вставляется другое значение и создается запрос к таблице.

CREATE TABLE dbo.doc_exz (column_a INT, column_b INT) ; GO INSERT INTO dbo.doc_exz (column_a)VALUES (7) ; GO ALTER TABLE dbo.doc_exz ADD CONSTRAINT col_b_def DEFAULT 50 FOR column_b ; GO INSERT INTO dbo.doc_exz (column_a) VALUES (10) ; GO SELECT * FROM dbo.doc_exz ; GO DROP TABLE dbo.doc_exz ; GO

Д.Добавление нескольких столбцов с ограничениями

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

CREATE TABLE dbo.doc_exe (column_a INT CONSTRAINT column_a_un UNIQUE) ; GO ALTER TABLE dbo.doc_exe ADD -- Add a PRIMARY KEY identity column. column_b INT IDENTITY CONSTRAINT column_b_pk PRIMARY KEY, -- Add a column that references another column in the same table. column_c INT NULL CONSTRAINT column_c_fk REFERENCES doc_exe(column_a), -- Add a column with a constraint to enforce that -- nonnull data is in a valid telephone number format. column_d VARCHAR(16) NULL CONSTRAINT column_d_chk CHECK (column_d LIKE "-" OR column_d LIKE "() -"), -- Add a nonnull column with a default. column_e DECIMAL(3,3) CONSTRAINT column_e_default DEFAULT .081 ; GO EXEC sp_help doc_exe ; GO DROP TABLE dbo.doc_exe ; GO

Е.Добавление столбца, допускающего значения NULL, со значениями по умолчанию

Следующий пример показывает добавление столбца, допускающего значения NULL, с определением DEFAULT и использование WITH VALUES для предоставления значений каждой строке таблицы. Если аргумент WITH VALUES не используется, то каждая строка в новом столбце имеет значение NULL.

CREATE TABLE dbo.doc_exf (column_a INT) ; GO INSERT INTO dbo.doc_exf VALUES (1) ; GO ALTER TABLE dbo.doc_exf ADD AddDate smalldatetime NULL CONSTRAINT AddDateDflt DEFAULT GETDATE() WITH VALUES ; GO DROP TABLE dbo.doc_exf ; GO

Ж.Создание ограничения PRIMARY KEY с параметрами индекса

Следующий пример показывает создание ограничения PRIMARY KEY PK_TransactionHistoryArchive_TransactionID и установку параметров FILLFACTOR, ONLINE и PAD_INDEX. Результирующий кластеризованный индекс будет иметь такое же имя, что и ограничение.

З.Добавление разреженного столбца

В следующих примерах показывается добавление и изменение разреженных столбцов в таблице T1. Код для создания таблицы T1:

CREATE TABLE T1 (C1 int PRIMARY KEY, C2 varchar(50) SPARSE NULL, C3 int SPARSE NULL, C4 int) ; GO

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

ALTER TABLE T1 ADD C5 char(100) SPARSE NULL ; GO

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

ALTER TABLE T1 ALTER COLUMN C4 ADD SPARSE ; GO

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

ALTER TABLE T1 ALTER COLUMN C4 DROP SPARSE; GO

И.Добавление набора столбцов

В следующих примерах показано добавление столбца к таблице T2. Набор столбцов не может быть добавлен в таблицу, если в ней уже содержатся разреженные столбцы. Код для создания таблицы T2:

CREATE TABLE T2 (C1 int PRIMARY KEY, C2 varchar(50) NULL, C3 int NULL, C4 int) ; GO

В следующих трех инструкциях добавляется набор столбцов с именем CS, после чего изменяются столбцы C2 и C3 на SPARSE.

ALTER TABLE T2 ADD CS XML COLUMN_SET FOR ALL_SPARSE_COLUMNS ; GO ALTER TABLE T2 ALTER COLUMN C2 ADD SPARSE ; GO ALTER TABLE T2 ALTER COLUMN C3 ADD SPARSE ; GO

[В начало]

Удаление столбцов и ограничений

Приведенные в этом разделе примеры демонстрируют удаление столбцов и ограничений.

А.Удаление столбца или столбцов

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

CREATE TABLE dbo.doc_exb (column_a INT ,column_b VARCHAR(20) NULL ,column_c datetime ,column_d int) ; GO -- Remove a single column. ALTER TABLE dbo.doc_exb DROP COLUMN column_b ; GO -- Remove multiple columns. ALTER TABLE dbo.doc_exb DROP COLUMN column_c, column_d;

Б.Удаление ограничений и столбцов

В первом примере из таблицы удаляется ограничение UNIQUE. Во втором примере удаляется 2 ограничения и один столбец.

CREATE TABLE dbo.doc_exc (column_a int NOT NULL CONSTRAINT my_constraint UNIQUE) ; GO -- Example 1. Remove a single constraint. ALTER TABLE dbo.doc_exc DROP my_constraint ; GO DROP TABLE dbo.doc_exc; GO CREATE TABLE dbo.doc_exc (column_a int NOT NULL CONSTRAINT my_constraint UNIQUE ,column_b int NOT NULL CONSTRAINT my_pk_constraint PRIMARY KEY) ; GO -- Example 2. Remove two constraints and one column -- The keyword CONSTRAINT is optional. The keyword COLUMN is required. ALTER TABLE dbo.doc_exc DROP CONSTRAINT CONSTRAINT my_constraint, my_pk_constraint, COLUMN column_b ; GO

В.Удаление ограничения PRIMARY KEY в режиме ONLINE

В следующем примере удаляется ограничение PRIMARY KEY с параметром ONLINE, имеющим значение ON.

ALTER TABLE Production.TransactionHistoryArchive DROP CONSTRAINT PK_TransactionHistoryArchive_TransactionID WITH (ONLINE = ON); GO

Г.Добавление и удаление ограничения FOREIGN KEY

Следующий пример показывает создание таблицы ContactBackup, а затем ее изменение сначала добавлением ограничения FOREIGN KEY, ссылающегося на таблицу Person.Person, затем удалением ограничения FOREIGN KEY.

CREATE TABLE Person.ContactBackup (ContactID int) ; GO ALTER TABLE Person.ContactBackup ADD CONSTRAINT FK_ContactBacup_Contact FOREIGN KEY (ContactID) REFERENCES Person.Person (BusinessEntityID) ; GO ALTER TABLE Person.ContactBackup DROP CONSTRAINT FK_ContactBacup_Contact ; GO DROP TABLE Person.ContactBackup ;

[В начало]

Изменение определения столбца

А.изменение типа данных столбца.

В следующем примере столбец таблицы изменяется с INT на DECIMAL.

CREATE TABLE dbo.doc_exy (column_a INT) ; GO INSERT INTO dbo.doc_exy (column_a) VALUES (10) ; GO ALTER TABLE dbo.doc_exy ALTER COLUMN column_a DECIMAL (5, 2) ; GO DROP TABLE dbo.doc_exy ; GO

Б.Изменение размера столбца

В следующем примере выполняется увеличение размера столбца varchar, а также точности и масштаба столбца decimal. Поскольку столбцы содержат данные, их размер можно только увеличить. Также обратите внимание, что столбец col_a определяется в уникальном индексе. Размер столбца col_a можно увеличивать, поскольку он имеет тип данных varchar, а индекс не является результатом ограничения PRIMARY KEY.

IF OBJECT_ID ("dbo.doc_exy", "U") IS NOT NULL DROP TABLE dbo.doc_exy; GO -- Create a two-column table with a unique index on the varchar column. CREATE TABLE dbo.doc_exy (col_a varchar(5) UNIQUE NOT NULL, col_b decimal (4,2)); GO INSERT INTO dbo.doc_exy VALUES ("Test", 99.99); GO -- Verify the current column size. SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale FROM sys.columns WHERE object_id = OBJECT_ID(N"dbo.doc_exy"); GO -- Increase the size of the varchar column. ALTER TABLE dbo.doc_exy ALTER COLUMN col_a varchar(25); GO -- Increase the scale and precision of the decimal column. ALTER TABLE dbo.doc_exy ALTER COLUMN col_b decimal (10,4); GO -- Insert a new row. INSERT INTO dbo.doc_exy VALUES ("MyNewColumnSize", 99999.9999) ; GO -- Verify the current column size. SELECT name, TYPE_NAME(system_type_id), max_length, precision, scale FROM sys.columns WHERE object_id = OBJECT_ID(N"dbo.doc_exy");

В.Изменение параметров сортировки столбца

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

CREATE TABLE T3 (C1 int PRIMARY KEY, C2 varchar(50) NULL, C3 int NULL, C4 int) ; GO

Затем параметры сортировки столбца C2 изменяются на Latin1_General_BIN. Обратите внимание, что необходимо указать тип данных, хотя он не изменяется.

ALTER TABLE T3 ALTER COLUMN C2 varchar(50) COLLATE Latin1_General_BIN; GO

[В начало]

Изменение определения таблицы

В приведенных в этом разделе примерах показано, как изменить определение таблицы.

А.Изменение таблицы для изменения режима сжатия

В следующем примере изменяется режим сжатия несекционированной таблицы. Куча или кластеризованный индекс будет перестроен. Если таблица является кучей, то все некластеризованные индексы будут перестроены.

ALTER TABLE T1 REBUILD WITH (DATA_COMPRESSION = PAGE);

В следующем примере изменяется режим сжатия секционированной таблицы. Инструкция REBUILD PARTITION = 1 вызывает перестройку только секции с номером 1.

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

Дополнительные примеры сжатия данных см. в разделе .

Б.Модификация таблицы columnstore для изменения архивного сжатия

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

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

В.Переключение секций между таблицами

В следующем примере демонстрируется создание секционированной таблицы, исходя из предположения, что схема секционирования myRangePS1 уже создана в базе данных. Затем создается несекционированная таблица с такой же структурой, что и секционированная таблица, и в той же файловой группе, что и PARTITION 2 таблицы PartitionTable. В таком случае данные PARTITION 2 таблицы PartitionTable переключаются в таблицу NonPartitionTable.

CREATE TABLE PartitionTable (col1 int, col2 char(10)) ON myRangePS1 (col1) ; GO CREATE TABLE NonPartitionTable (col1 int, col2 char(10)) ON test2fg ; GO ALTER TABLE PartitionTable SWITCH PARTITION 2 TO NonPartitionTable ; GO

Г.Разрешение укрупнения блокировки для секционированных таблиц

В следующем примере укрупнение блокировки разрешается на уровне секции в секционированной таблице. Если таблица не секционирована, то блокировка будет укрупняться до уровня TABLE.

Д.Настройка отслеживания изменений для таблицы

В следующем примере в таблице Person.Person включается отслеживание изменений.

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

В следующем примере в таблице Person.Person отключается отслеживание изменений.

[В начало]

Отключение и включение ограничений и триггеров

A.Отключение и повторное включение ограничения

В следующем примере отключается ограничение на зарплату. Параметр NOCHECK CONSTRAINT используется в инструкции ALTER TABLE для отключения ограничения и обеспечения возможности вставки, противоречащей указанному ограничению. CHECK CONSTRAINT повторно включает ограничение.

CREATE TABLE dbo.cnst_example (id INT NOT NULL, name VARCHAR(10) NOT NULL, salary MONEY NOT NULL CONSTRAINT salary_cap CHECK (salary < 100000)); -- Valid inserts INSERT INTO dbo.cnst_example VALUES (1,"Joe Brown",65000); INSERT INTO dbo.cnst_example VALUES (2,"Mary Smith",75000); -- This insert violates the constraint. INSERT INTO dbo.cnst_example VALUES (3,"Pat Jones",105000); -- Disable the constraint and try again. ALTER TABLE dbo.cnst_example NOCHECK CONSTRAINT salary_cap; INSERT INTO dbo.cnst_example VALUES (3,"Pat Jones",105000); -- Re-enable the constraint and try another insert; this will fail. ALTER TABLE dbo.cnst_example CHECK CONSTRAINT salary_cap; INSERT INTO dbo.cnst_example VALUES (4,"Eric James",110000) ;

Б.Отключение и повторное включение триггера

В следующем примере показывается использование параметра DISABLE TRIGGER инструкции ALTER TABLE для отключения триггера и обеспечения возможности вставки, которая в обычных условиях нарушает триггер. Затем инструкция ENABLE TRIGGER используется для повторного включения триггера.

CREATE TABLE dbo.trig_example (id INT, name VARCHAR(12), salary MONEY) ; GO -- Create the trigger. CREATE TRIGGER dbo.trig1 ON dbo.trig_example FOR INSERT AS IF (SELECT COUNT(*) FROM INSERTED WHERE salary > 100000) > 0 BEGIN print "TRIG1 Error: you attempted to insert a salary > $100,000" ROLLBACK TRANSACTION END ; GO -- Try an insert that violates the trigger. INSERT INTO dbo.trig_example VALUES (1,"Pat Smith",100001) ; GO -- Disable the trigger. ALTER TABLE dbo.trig_example DISABLE TRIGGER trig1 ; GO -- Try an insert that would typically violate the trigger. INSERT INTO dbo.trig_example VALUES (2,"Chuck Jones",100001) ; GO -- Re-enable the trigger. ALTER TABLE dbo.trig_example ENABLE TRIGGER trig1 ; GO -- Try an insert that violates the trigger. INSERT INTO dbo.trig_example VALUES (3,"Mary Booth",100001) ; GO

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

Синтаксис

имя

действие

имя

имя_столбца

новое_имя_столбца

имя

имя_ограничения

имя_нового_ограничения

имя

новое_имя

имя

новая_схема

имя

имя_роли

новое_табл_пространство

Где действие может быть следующим:

имя_столбца

тип_данных

правило_сортировки

ограничение_столбца

имя_столбца

тип_данных

правило_сортировки

выражение

имя_столбца

выражение

имя_столбца

integer

имя_столбца

атрибут

значение

имя_столбца

атрибут

имя_столбца

ограничение_таблицы

имя_ограничения

имя_триггера

имя_правила_перезаписи

имя_индекса

новое_табл_пространство

параметр_хранения

значение

параметр_хранения

таблица_родитель

имя_типа

новый_владелец

имя_индекса

и ограничение_таблицы_по_индексу :

имя_ограничения

имя_индекса

Описание

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. SET STATISTICS

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

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

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

Эта форма добавляет в таблицу новое ограничение, с тем же синтаксисом, что и CREATE TABLE , и дополнительным указанием NOT VALID , которое в настоящее время разрешено только для ограничений внешнего ключа и ограничений-проверок. Если ограничение помечено как NOT VALID , потенциально длительная начальная проверка того, что ему удовлетворяют все строки, пропускается. Тем не менее это ограничение будет действовать при последующих добавлениях или изменениях (то есть, эти операции не будут выполнены, если, в случае с внешним ключом, в главной таблице не найдётся соответствующая строка, либо, в случае с ограничением-проверкой, новая строка нарушит проверочное условие). Но база данных не будет считать, что ограничение выполняется для всех строк таблицы, пока оно не будет проверено с применением указания VALIDATE CONSTRAINT . 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 , а затем задействовать его как полноценное ограничение, используя эту запись. См. следующий пример.

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

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

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

При проверке запрашивается только блокировка SHARE UPDATE EXCLUSIVE целевой таблицы. Для ограничений внешнего ключа требуется также блокировка ROW SHARE в таблице, на которую ссылается ограничение. 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 { LOGGED | UNLOGGED }

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

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

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

Примечание

Хотя 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

Эта форма меняет информацию, записываемую в журнал предзаписи для идентификации изменяемых или удаляемых строк. Данный параметр действует только при использовании логической репликации. В режиме DEFAULT (по умолчанию для не системных таблиц) записывается старые значения столбцов первичного ключа, если он есть. В режиме USING INDEX записываются старые значения столбцов, составляющих заданный индекс, который должен быть уникальным, не частичным, не отложенным и включать только столбцы, помеченные NOT NULL . В режиме FULL записываются старые значения всех столбцов в строке, а в режиме NOTHING (по умолчанию для системных таблиц) никакая информация о старой строке не записывается. Во всех случаях старые значения записываются в журнал, только если как минимум в одном столбце из тех, что должны быть записаны, произошли изменения в новой строке. RENAME

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

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

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

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

Параметры

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Замечания

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

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

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

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

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

Форма 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) никогда не удаляет унаследованные столбцы, но вместо этого помечает их как независимо определённые, а не наследуемые.

Действия 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;

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

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

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