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

Синтаксис

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

Описание

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

ADD COLUMN [ IF NOT EXISTS ]

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

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

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

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

Эти формы определяют, будет ли столбец принимать значения NULL или нет. Задать SET NOT NULL можно, только если столбец не содержит значений NULL. 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 , а затем задействовать его как полноценное ограничение, используя эту запись. См. следующий пример.

ALTER CONSTRAINT

Эта форма меняет атрибуты созданного ранее ограничения. В настоящее время изменять можно только ограничения внешнего ключа. 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, который не допускает существование таблиц с нулём столбцов.

Заданным образом модифицирует структуру таблицы.

Синтаксис:

ALTER TABLE TableName1 ADD | ALTER FieldName1 FieldType [(nFieldWidth [, nPrecision])] ] ] - Или - ALTER TABLE TableName1 ALTER FieldName2 ] - Или - ALTER TABLE TableName1 FieldName3] ] ] TAG TagName4 REFERENCES TableName2 ] ]

Параметры:
TableName1
Задает имя таблицы, структура которой модифицируется.
ADD FieldName1
Задает имя добавляемого поля.
ALTER FieldName1
Задает имя существующего поля, подлежащего модификации.
FieldType [(nFieldWidth [, nPrecision]])
Задает тип поля, ширину поля и точность поля (число десятичных знаков) для нового или модифицируемого поля.
FieldType это одна буква, указывающая тип данных поля. Для некоторых типов полей требуется задавать nFieldWidth и/или nPrecision .
В следующей таблице перечислены значения параметра FieldType ; для каждого из них указывается, требуются ли параметры nFieldWidth и nPrecision .

FieldType nFieldWidth nPrecision Описание C n - Символьное поле ширины n D - - Дата T - - Поле типа DateTime N n d Числовое поле ширины n, имеющее d десятичных знаков F n d Поле данных с плавающей запятой ширины n, имеющее d десятичных знаков I - - Поле целых чисел B - d Поле данных с двойной точностью Y - - Поле типа Currency L - - Логическое M - - Поле типа Memo G - - Поле типа General

Параметры nFieldWidth и nPrecision игнорируются в случае типов D, T, I, Y, L, M, G и P. Если для типа N, F или B значение nPrecision не указано, оно принимается равным нулю по умолчанию (нет десятичных знаков).
NULL
Разрешает полю принимать значения NULL .
NOT NULL
Запрещает полю принимать значения NULL .
Если опущены предложения NULL и NOT NULL , то допустимость значений NULL для поля определяется текущей установкой SET NULL . Однако если опустить предложения NULL и NOT NULL и задать PRIMARY KEY или UNIQUE , текущая установка SET NULL будет игнорироваться и по умолчанию для поля будет установлено NOT NULL .
CHECK lExpression1
Задает правило проверки достоверности уровня поля. Вычисление lExpression1 должно давать логическое выражение; это может быть функция, определенная пользователем, или хранимая процедура. Учтите, что это правило проверяется при добавлении пустой записи. Если данное правило не допускает пустые значения полей в присоединяемой записи, генерируется ошибка.
ERROR cMessageText1
Задает текст сообщения об ошибке, отображаемого при обнаружении ошибки в соответствии с правилом поля. Это сообщение выдается только при изменении данных в окне просмотра или окне редактирования.
DEFAULT eExpression1
Задает для поля значение, принимаемое по умолчанию. Тип данных выражения eExpression1 должен совпадать с типом данных поля.
PRIMARY KEY
Создает тег первичного индекса. Имя тега совпадает с именем поля.
UNIQUE
Создает тег индекса-кандидата с тем же именем, которое носит поле. Подробнее об индексах-кандидатах см. главу 7 "Работа с таблицами" Руководства разработчика.
Замечание Индексы-кандидаты (созданные с помощью опции UNIQUE , включенной в команды ALTER TABLE и CREATE TABLE для совместимости с ANSI ) отличаются от индексов, созданных с помощью опции UNIQUE команды INDEX . Индекс, созданный опцией UNIQUE в команде INDEX , допускает дублирование ключей индекса; в индексах-кандидатах повторяющиеся ключи не разрешены.
В поле, используемом для первичного индекса или индекса-кандидата, не допускаются значения NULL и повторяющиеся записи.
Если вы создаете новое поле с помощью предложения ADD COLUMN, то при создании первичного индекса или индекса-кандидата для поля, поддерживающего значения NULL, Visual FoxPro не будет генерировать ошибку. Однако если вы попытаетесь ввести значение NULL или дубликат в поле, используемое для первичного индекса или индекса-кандидадата, Visual FoxPro выдаст ошибку.
Если вы модифицируете существующее поле, а выражение первичного индекса или индекса-кандидата состоит только из имени одного этого поля, Visual FoxPro проверит поле не содержит ли оно значений NULL или повторяющихся записей. Если такие значения будут обнаружены, Visual FoxPro сгенерирует ошибку и таблица остается без изменений. Если выражение индекса содержит несколько полей или выражение, включающее одиночное поле, Visual FoxPro не будет проверять наличие значений NULL или дублирующих записей.
REFERENCES TableName2 TAG TagName1
Задает родительскую таблицу, с которой устанавливается постоянное отношение. TAG TagName1 задает тег индекса родительской таблицы, на котором базируется данное отношение. Имя тега индекса может содержать не более 10 символов.
NOCPTRANS
Запрещает для символьных и memo-полей трансляцию в другую кодовую страницу. Если таблица преобразуется в другую кодовую таблицу, поля с опцией NOCPTRANS не участвуют в трансляции. NOCPTRANS можно задать только для символьных полей и memo-полей.
В следующем примере создается таблица с именем MYTABLE , которая содержит два символьных поля и два memo-поля. Второе символьное поле char2 и второе memo-поле memo2 определены с опцией NOCPTRANS для предотвращения трансляции.

CREATE TABLE mytable (char1 C(10), char2 C(10) NOCPTRANS,; memo1 M, memo2, M NOCPTRANS)

ALTER FieldName2
Задает имя существующего поля, которое предстоит модифицировать.
SET DEFAULT eExpression2
Задает новое значение, которое будет приниматься по умолчанию для существующего поля. Тип данных выражения eExpression2 должен совпадать с типом данных, определенным для поля.
SET CHECK lExpression2
Задает новое правило для существующего поля. Вычисление lExpression2 должно давать логическое значение; это может быть определенная пользователем функция или хранимая процедура.
ERROR cMessageText2
Задает текст сообщения об ошибке. Это сообщение будет отображаться при обнаружении ошибки в соответствии с правилом уровня поля. Сообщение выдается только в том случае, когда происходит изменение данных в окне просмотра или окне редактирования.
DROP DEFAULT
Отменяет значение, принимаемое по умолчанию для существующего поля.
DROP CHECK
Отменяет правило для существующего поля.
DROP FieldName3
Задает поле, которое нужно удалить из таблицы. При удалении поля из таблицы также отменяется значение, принимаемое по умолчанию для этого поля.
Если для поля указывает выражение ключа индекса или выражение триггера, то после удаления поля эти выражения становятся недействительными. В таком случае при удалении поля не будет генерироваться ошибка, однако ошибки возникнут для недействительных выражений ключа индекса или триггера на этапе выполнения.
SET CHECK lExpression3
Задает правило уровня таблицы. Вычисление lExpression3 должно давать логическое выражение, это может быть определенная пользователем функция или хранимая процедура.
ERROR cMessageText3
Задает текст сообщения об ошибке, отображаемого при обнаружении ошибки в соответствии с правилом уровня таблицы. Это сообщение выдается только при изменении данных в окне просмотра или окне редактирования.
DROP CHECK
Отменяет правило уровня таблицы.
ADD PRIMARY KEY eExpression3 TAG TagName2
Добавляет в таблицу первичный индекс. eExpression3 задает выражение ключа первичного индекса, а TagName2 имя тега первичного индекса. Имя тега индекса может содержать не более 10 символов. Если TAG TagName2 опущено, а eExpression3 определяет одно поле, тег первичного индекса получает то же имя, что и поле, заданное в выражении eExpression3 .
DROP PRIMARY KEY
Удаляет первичный индекс и его тег. Поскольку таблица может иметь только один первичный ключ, его имя можно не указывать. При удалении первичного индекса удаляются и все постоянные отношения, основанные на первичном ключе.
ADD UNIQUE eExpression4
Добавляет в таблицу индекс-кандидат. eExpression4 задает выражение ключа индекса-кандидата, а TagName3 имя тега индекса-кандидата. Имя тега индекса может содержать не более 10 символов. Если выражение TAG TagName3 опущено, а eExpression4 определяет одно поле, тег индекса-кандидата получает то же имя, что и поле, заданное в выражении eExpression4 .
DROP UNIQUE TAG TagName4
Удаляет индекс-кандидат и его тег. Поскольку таблица может иметь несколько ключей-кандидатов, необходимо задать имя удаляемого тега индекса-кандидата.
ADD FOREIGN KEY TAG TagName4
Добавляет в таблицу внешний (отличный от первичного) индекс. eExpression5 задает выражение ключа внешнего индекса, а TagName4 имя тега внешнего индекса. Имя тега индекса может содержать не более 10 символов.
REFERENCES TableName2
Задает родительскую таблицу, с которой устанавливается постоянное отношение. Чтобы установить отношение с родительской таблицей на базе тега существующего индекса, следует указать TAG TagName5 . Имя тега индекса может содержать не более 10 символов. Если TAG TagName5 опущено, отношение устанавливается с помощью тега первичного индекса родительской таблицы.
DROP FOREIGN KEY TAG TagName6
Удаляет внешний ключ, у которого тег индекса имеет имя TagName6. Если опустить SAVE , тег индекса будет удален из структурного индекса. Включив SAVE , вы предотвратите удаление тега из структурного индекса.
RENAME COLUMN FieldName4 TO FieldName5
Позволяет изменять имя поля в таблице. FieldName4 задает имя поля, которое следует изменить. FieldName5 задает новое имя поля.
Внимание Будьте осторожны при переименовании полей таблиц выражения индекса, правила проверки достоверности полей и таблиц, команды, функции и т.п. могут продолжать ссылаться на старые имена полей.
NOVALIDATE
Указывает, что Visual FoxPro допускает внесение изменений в структуру таблицы, которые могут нарушить целостность табличных данных. По умолчанию Visual FoxPro запрещает команде ALTER TABLE вносить в структуру подобные разрушающие изменения. Включение опции NOVALIDATE позволяет снять этот запрет.

Команда ALTER TABLE используется для добавления , удаления или модификации колонки в уже существующей таблице .

Команда ALTER TABLE

Команда ALTER TABLE изменяет определение таблицы одним из следующих способов:

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

Условие: Таблица должна быть в схеме пользователя, или пользователь должен иметь системную привилегию ALTER ANY TABLE .

Добавляя столбец с ограничением NOT NULL , разработчик или администратор БД должны учесть ряд обстоятельств. Сначала нужно создать столбец без ограничения, а затем ввести значения во все его строки. После того как все значения столбца станут не NULL -значениями, к нему можно применить ограничение NOT NULL . Если столбец с ограничением NOT NULL пытается добавить пользователь, возвращается сообщение об ошибке, говорящее о том, что либо таблица должна быть пустой, либо в столбце должны содержаться значения для каждой существующей строки (напомним, что после наложения на столбец ограничения NOT NULL в нем не могут присутствовать NULL -значения ни в одной из существующих строк). В СУБД Oracle , начиная с версии 8i, можно удалять столбцы.

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

• Увеличение размера столбца CHAR или VARCHAR2
• Увеличение размера столбца NUMBER
• Добавление новых столбцов в таблицу

Уменьшение различных характеристик таблицы, в том числе некоторых типов данных столбцов и реального числа столбцов таблицы, требует особых действий. Часто перед внесением изменения нужно убедиться в том, что в соответствующем столбце или столбцах все значения являются NULL -значениями. Для выполнения подобных операций над столбцами таблицы, содержащими данные, разработчик должен найти или создать какую-то область для временного хранения этих данных. Например, создать таблицу с помощью команды CREATE TABLE AS SELECT , в которой извлекаются данные из первичного ключа и изменяемого столбца или столбцов. Допустимые изменения:

• Уменьшение размера столбца NUMBER
• Уменьшение размера столбца CHAR или VARCHAR2 (только при пустом столбце для всех строк)
• Изменение типа данных столбца (только при пустом столбце для всех строк)

ALTER TABLE Пример 1

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

ALTER TABLE t1(pole1 char(10));

ALTER TABLE Пример 2

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

ALTER TABLE t1 MODIFY (pole1 char(20));

ALTER TABLE Пример 3

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

ALTER TABLE t1 DROP COLUMN pole1;

С помощью команды ALTER TABLE можно изменить имя таблицы без реального переноса физической информации в БД:

ALTER TABLE t1 RENAME TO t2;

Аналогичную операцию можно выполнить с помощью команды RENAME:

RENAME t1 TO t2;

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

ALTER TABLE Пример 4

Модификация структуры таблицы

ALTER TABLE t1 MODIFY (pole1 NOT NULL );

CREATE TABLE t2

(pole1 CHAR(10) PRIMARY KEY );

ALTER TABLE t1 ADD

(CONSTRAINT fk_t1 FOREIGN KEY (pole1)

REFERENCES t2 (pole1));

ALTER TABLE t1 ADD (UNIQUE (p_name));

ALTER TABLE t1 ADD (p_size CHAR(4) CHECK

(p_size IN (‘P’,’S’,’M’,’L’,’XL’,’XXL’,’XXXL’)));

В первой из приведенных выше команд для добавления ограничения NOT NULL для столбца используется конструкция MODIFY, а для добавления всех табличных ограничений целостности других типов – конструкция ADD. Столбец, для которого добавляется ограничение, должен уже существовать в таблице БД; в противном случае создать ограничение не удастся.

ALTER TABLE Пример 5

Для добавления ограничений целостности можно не указывать имя создаваемого ограничения с помощью ключевого слова CONSTRAINT . В этом случае команда будет выглядеть следующим образом:

ALTER TABLE t1 ADD FOREIGN KEY (pole1) REFERENCES t2 (pole1);

Существует ряд условий создания ограничений:

• Первичные ключи: в столбцах не могут содержаться NULL-значения, и все значения должны быть уникальны.
• Внешние ключи: в тех столбцах других таблиц, на которые производятся ссылки, должны содержаться значения, соответствующие всем значениям ссылающихся столбцов, либо значения этих последних должны быть NULL-значениями.
• Ограничения UNIQUE: все значения столбцов должны быть уникальными или NULL-значениями.
• Ограничения CHECK: новое ограничение будет применяться только по отношению к данным, добавляемым или модифицируемым после его создания.
• NOT NULL: NULL-значения в столбцах запрещены.

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

ALTER TABLE Пример 6

Запрещение ограничений:

ALTER TABLE t1 DISABLE PRIMARY KEY ;
ALTER TABLE t1 DISABLE UNIQUE (p_name);

ALTER TABLE Пример 7

В некоторых случаях запрещение первичного ключа, от которого зависят внешние ключи, может вызвать определенные сложности, например:

ALTER TABLE t2 DISABLE PRIMARY KEY;

Error at line 1: Cannot disable constraint …. – dependencies exist (невозможно запретить ограничение – существуют зависимости)

Для удаления первичного ключа при наличии зависящих от него внешних ключей в команде ALTER TABLE DISABLE <ограничения> обязательна конструкция CASCADE:

ALTER TABLE t2 DISABLE PRIMARY KEY CASCADE;

ALTER TABLE Пример 8

Запрещенное ограничение разрешается следующим образом:

ALTER TABLE t1 ENABLE PRIMARY KEY ;

ALTER TABLE t1 ENABLE UNIQUE (p_name);

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

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

ALTER TABLE t1 DROP UNIQUE (p_name);

Последнее обновление: 09.07.2017

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

Общий формальный синтаксис команды выглядит следующим образом:

ALTER TABLE название_таблицы { ADD название_столбца тип_данных_столбца [атрибуты_столбца] | DROP COLUMN название_столбца | ALTER COLUMN название_столбца тип_данных_столбца | ADD определение_ограничения | DROP имя_ограничения}

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

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

Добавим в таблицу Customers новый столбец Address:

ALTER TABLE Customers ADD Address NVARCHAR(50) NULL;

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

ALTER TABLE Customers ADD Address NVARCHAR(50) NOT NULL;

Поэтому в данном случае решение состоит в установке значения по умолчанию через атрибут DEFAULT:

ALTER TABLE Customers ADD Address NVARCHAR(50) NOT NULL DEFAULT "Неизвестно";

В этом случае, если в таблице уже есть данные, то для них для столбца Address будет добавлено значение "Неизвестно".

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

Удалим столбец Address из таблицы Customers:

ALTER TABLE Customers DROP COLUMN Address;

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

Изменим в таблице Customers тип данных у столбца FirstName на NVARCHAR(200) :

ALTER TABLE Customers ALTER COLUMN FirstName NVARCHAR(200);

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

При добавлении ограничений SQL Server автоматически проверяет имеющиеся данные на соответствие добавляемым ограничениям. Если данные не соответствуют ограничениям, то такие ограничения не будут добавлены. Например, установим для столбца Age в таблице Customers ограничение Age > 21.

ALTER TABLE Customers ADD CHECK (Age > 21);

Если в таблице есть строки, в которых в столбце Age есть значения, несоответствующие этому ограничению, то sql-команда завершится с ошибкой. Чтобы избежать подобной проверки на соответствие и все таки добавить ограничение, несмотря на наличие несоответствующих ему данных, используется выражение WITH NOCHECK :

ALTER TABLE Customers WITH NOCHECK ADD CHECK (Age > 21);

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

Добавление внешнего ключа

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

CREATE TABLE Customers (Id INT PRIMARY KEY IDENTITY, Age INT DEFAULT 18, FirstName NVARCHAR(20) NOT NULL, LastName NVARCHAR(20) NOT NULL, Email VARCHAR(30) UNIQUE, Phone VARCHAR(20) UNIQUE); CREATE TABLE Orders (Id INT IDENTITY, CustomerId INT, CreatedAt Date);

Добавим ограничение внешнего ключа к столбцу CustomerId таблицы Orders:

ALTER TABLE Orders ADD FOREIGN KEY(CustomerId) REFERENCES Customers(Id);

Добавление первичного ключа

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

ALTER TABLE Orders ADD PRIMARY KEY (Id);

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

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

ALTER TABLE Orders ADD CONSTRAINT PK_Orders_Id PRIMARY KEY (Id), CONSTRAINT FK_Orders_To_Customers FOREIGN KEY(CustomerId) REFERENCES Customers(Id); ALTER TABLE Customers ADD CONSTRAINT CK_Age_Greater_Than_Zero CHECK (Age > 0);

Удаление ограничений

Для удаления ограничений необходимо знать их имя. Если мы точно не знаем имя ограничения, то его можно узнать через SQL Server Management Studio:

Раскрыв узел таблиц в подузле Keys можно увидеть названия ограничений первичного и внешних ключей. Названия ограничений внешних ключей начинаются с "FK". А в подузле Constraints можно найти все ограничения CHECK и DEFAULT. Названия ограничений CHECK начинаются с "CK", а ограничений DEFAULT - с "DF".

Например, как видно на скриншоте в моем случае имя ограничения внешнего ключа в таблице Orders называется "FK_Orders_To_Customers". Поэтому для удаления внешнего ключа я могу использовать следующее выражение:

ALTER TABLE Orders DROP FK_Orders_To_Customers;

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

Дано: таблица размером в несколько десятков гигабайт данных. Задача - изменить структуру таблицы.

Сразу забегу в перед, работать метод будет только на транзакционных таблицах. Если у вас MyISAM-таблица на десятки гигабайт, то тут как в том анекдоте - «разбирайтесь сами со своими проблемами». Пример будет приведен для InnoDB таблицы.

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

CREATE TABLE `users` (`id` int(11) NOT NULL AUTO_INCREMENT, `email` varchar(40) NOT NULL DEFAULT "", `password_hash` char(32) NOT NULL DEFAULT "", `registration_date` int(11) NOT NULL DEFAULT "0", PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8
Мы хотим добавить в эту таблицу поле last_login.

Какие у нас есть варианты.

В лоб

ALTER TABLE `users` ADD COLUMN `last_login` int(11) NOT NULL DEFAULT 0;
Вариант прекрасно работет на мелких проектах где размер таблиц редко превышает 50 000 записей. Нам вариант не подходит т.к. ALTER будет выполнятся слишком долго и все это время таблица будет заблокирована как на запись так и на чтение. Соответственно сервис нужно будет останавливать на это время.

Включаем мозг

Можно вообще не трогать таблицу раз уж на то пошло, а сделать отдельную `users_lastvisits`:
CREATE TABLE `users_lastvisits` (`user_id` int(11) NOT NULL, `last_login` int(11) NOT NULL DEFAULT "0", PRIMARY KEY (`user_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Теперь можно во всех запросах где нужен last_login делать JOIN с таблицей last_login. Работать будет, конечно, медленнее, да и в запросах дописывать JOIN тоже лишнее время, но в целом этого иногда бывает достаточно и на этом пункте можно и остановится.

И все таки - нужно добавить поле

Можно поднять master-slave репликацию, сделать ALTER на slave-сервере а потом поменять их местами. Если честно я таким никогда не занимался, может это и проще следующего способа, но не всегда есть возможность поднять репликацию.

Мой способ заключается в следующем

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

Итак, подготавливаем 2 таблицы - первая с нужной структурой, вторая для логирования изменений.
CREATE TABLE `_users` (`id` int(11) NOT NULL AUTO_INCREMENT, `email` varchar(40) NOT NULL DEFAULT "", `password_hash` char(32) NOT NULL DEFAULT "", `registration_date` int(11) NOT NULL DEFAULT "0", `lastvisit` int(11) NOT NULL DEFAULT 0, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `users_updated_rows` (`id` int(11) NOT NULL AUTO_INCREMENT, `row_id` int(11) NOT NULL DEFAULT "0", `action` enum("updated","deleted") NOT NULL DEFAULT "updated", PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Ставим триггеры:
DELIMITER ;; CREATE TRIGGER users_after_delete AFTER DELETE ON users FOR EACH ROW BEGIN INSERT INTO users_updated_rows VALUES (0, OLD.id, "deleted"); END;; CREATE TRIGGER users_after_insert AFTER INSERT ON users FOR EACH ROW BEGIN INSERT INTO users_updated_rows VALUES (0, NEW.id, "updated"); END;; CREATE TRIGGER users_after_update AFTER UPDATE ON users FOR EACH ROW BEGIN IF (OLD.id != NEW.id) THEN INSERT INTO users_updated_rows VALUES (0, OLD.id, "deleted"); END IF; INSERT INTO users_updated_rows VALUES (0, NEW.id, "updated"); END;; DELIMITER ;
Теперь начинаем переливку. Для этого надо открыть 2 соединения с базой. В одном будет идти содственно переливка, в другом нужно будет ненадолго заблокировать таблицу на запись.
mysql> LOCK TABLES users WRITE; Query OK, 0 rows affected (0.00 sec) mysql> -- триггеры уже должны быть установлены mysql> TRUNCATE users_updated_rows; Query OK, 0 rows affected (0.17 sec) mysql> -- в другой консоли пускаем переливку mysql> INSERT INTO _users SELECT id, email, password_hash, registration_date, 0 FROM users; mysql> -- снова в первой консоли mysql> UNLOCK TABLES;
Все, теперь пока таблица переливается у нас есть время подумать как будем вливать изменившиеся с момента начала переливки данные. Тут вообщем то ничего сложного - скрипт приводить я не буду, нужно просто брать по одной записи из таблицы users_updated_rows в том порядке, в котором они добавлялись (сортировать по первичному ключу) и обновлять или удалять её в таблице _users;

Итак, переливка таблицы уже закончилась, нужно вливать остальные данные. Запускаем скрипт. Скрипт должен работать постоянно и обновлять все записи которые добавляются в лог, когда он перельет все данные нужно переименовать таблицы:
mysql> TRUNCATE users_updated_rows; Query OK, 0 rows affected (0.16 sec) mysql> RENAME TABLE users TO __users, _users TO users; Query OK, 0 rows affected (0.11 sec)
Стоит заметит что в этот момент возможна небольшая потеря данных т.к. запросы выполняются не атомарно. Если это критично, лучше выключить сервис на некоторое время чтобы запросов на изменение не было. Можно например забрать права на запись у пользователя а выполнять команды под другим пользователем.

Если все сделать правильно данные не будут потеряны и перерывов в работе сервиса практически не будет. Что нам и требовалось. Таким же способом можно перелить данные на другой сервер, поменяется только способ переливки. Вместо
mysql> INSERT INTO _users SELECT id, email, password_hash, registration_date, 0 FROM users;
нужно перелить через mysqldump:
$ mysqldump -h host1 db users --single-transaction -ecQ | pv | mysql -h host2
Таким способом мне удалось перелить без остановки работы сервисов на другой сервер таблицу размером в 60Gb и 400 млн строк где то за 12 часов.

Кстати, велосипед уже изобретен Facebook и называется