Что делает команда alter table. Справочное руководство по MySQL. Примеров команды ALTER в SQL

Последнее обновление: 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 и называется

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, который не допускает существование таблиц с нулём столбцов.

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

ALTER TABLE для добавления и удаления столбца, значения по умолчанию

Запрос с командой ALTER TABLE на добавление нового столбца в таблицу имеет следующий синтаксис:

ALTER TABLE ИМЯ_ТАБЛИЦЫ ADD ИМЯ_НОВОГО_СТОЛБЦА ТИП_ДАННЫХ

Пример 1. Есть база данных фирмы - Company. В ней есть таблица Employee (Сотрудник). Требуется добавить в эту таблицу новый столбец - SEX (Пол). Соответствующий запрос выглядит следующим образом:

ALTER TABLE EMPLOYEE ADD SEX CHAR(1)

Пусть теперь требуется установить значение записей в таблице по умолчанию. Запрос с командой ALTER TABLE для этой цели содержит ещё одну команду - ALTER COLUMN - и имеет следующий синтаксис:

ALTER TABLE ИМЯ_ТАБЛИЦЫ ALTER COLUMN ИМЯ_СТОЛБЦА SET DEFAULT "ЗНАЧЕНИЕ_ПО_УМОЛЧАНИЮ"

Пример 2. Пусть в фирме, база данных которой имеет название Company, трудятся в основном женщины. Администратор базы данных решил сократить процесс заполнения данными столбца SEX (Пол) и установить его значение по умолчанию - "F" (что означает женский пол). Соответствующий запрос выглядит следующим образом:

ALTER TABLE EMPLOYEE ALTER COLUMN SEX SET DEFAULT "F"

Теперь только если новый сотрудник - мужчина, потребуется вписывать в столбец SEX значение "M", а в большинстве случаев значение установится по умолчанию - "F".

При помощи запроса с командой ALTER TABLE можно и удалить столбец из таблицы. Синтаксис команды будет следующим:

ALTER TABLE ИМЯ_ТАБЛИЦЫ DROP COLUMN ИМЯ_СТОЛБЦА

Пример 3. Пусть в той же базе данных Company из таблицы Employee требуется удалить столбец SEX (Пол). Соответствующий запрос выглядит следующим образом:

ALTER TABLE EMPLOYEE DROP COLUMN SEX

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

ALTER TABLE для добавления ключей

О ключах в таблицах базы данных - на уроке Реляционная модель данных . Если при создании таблицы базы данных не был определён первичный ключ (PRIMARY KEY), то это может быть сделано при помощи команды ALTER TABLE. Синтаксис команды в случае простого первичного ключа следующий:

ALTER TABLE ИМЯ_ТАБЛИЦЫ ADD PRIMARY KEY (ИМЯ_ИДЕНТИФИКАТОРА)

Синтаксис команды в случае составного первичного ключа:

ALTER TABLE ИМЯ_ТАБЛИЦЫ ADD PRIMARY KEY (ИМЯ_ИДЕНТИФИКАТОРА_1, ИМЯ_ИДЕНТИФИКАТОРА_2)

Пример 4. Работаем всё с той же базой данных Company, со столбцом EMPLOYEE. Мы можем добавить простой первичный ключ этого столбца в виде идентификатора сотрудника и сделать это так:

ALTER TABLE EMPLOYEE ADD PRIMARY KEY (EMP_ID)

Если решено использовать составной ключ, состоящий из идентификатора сотрудника и идентификатора отдела, в котором сотрудник работает, то используем следующий запрос:

ALTER TABLE EMPLOYEE ADD PRIMARY KEY (EMP_ID, DEPT_ID)

ALTER TABLE для добавления и удаления ограничений

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

Команда с выражением ALTER TABLE может понадобиться для установки значения NULL для такой ссылки. Её синтаксис следующий:

ALTER TABLE ИМЯ_ССЫЛАЮЩЕЙСЯ_ТАБЛИЦЫ ADD CONSTRAINT ИМЯ_ИДЕНТИФИКАТОРА FOREIGN KEY (ИМЯ_ИДЕНТИФИКАТОРА) REFERENCES ИМЯ_ТАБЛИЦЫ_НА_КОТОРУЮ_ССЫЛАЮТСЯ ON DELETE SET NULL

Пример 5. Пусть в той же базе данных Company требуется установить ссылочное ограничение: при удалении какого-либо отдела из таблицы Dept в таблице Employee, содержатся ссылки на эти отделы, значение ссылки (внешнего ключа) должно быть уставлено в NULL (полностью неопределенное). Соответствующий запрос выглядит следующим образом:

ALTER TABLE EMPLOYEE ADD CONSTRAINT DEPT_ID FOREIGN KEY (DEPT_ID) REFERENCES DEPT ON DELETE SET NULL

Таким образом, если в таблице Employee есть сотрудник, работающий в отделе, который вдруг был ликвидирован и запись о нём удалена из таблицы Dept, то в записи об этом сотруднике значение столбца DEPT_ID примет значение NULL.

Запросом с командой ALTER TABLE можно также установить запрет на удаление ссылающихся записей из таблицы (1), если существует хотя бы одна запись в таблице (2), на которую ссылается запись в таблице (1). Синтаксис такого запроса следующий:

ALTER TABLE ИМЯ_ССЫЛАЮЩЕЙСЯ_ТАБЛИЦЫ ADD CONSTRAINT ИМЯ_ИДЕНТИФИКАТОРА FOREIGN KEY (ИМЯ_ИДЕНТИФИКАТОРА) REFERENCES ИМЯ_ТАБЛИЦЫ_НА_КОТОРУЮ_ССЫЛАЮТСЯ ON DELETE RESTRICT

Следующая команда с выражением alter table устанавливает запрет на удаление группы препаратов из таблицы Group, если существует хотя бы один препарат этой группы, что определяется записью в таблице Preparation:

alter table Preparation add constraint GR_ID foreign key(GR_ID) references Group on delete restrict

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

alter table Employee add constraint PH_ID foreign key(PH_ID) references Pharmacy on delete restrict

Запросом с командой ALTER TABLE можно также определить, что если удалить запись в таблице (2), на которую ссылается запись в таблице (1), то должны быть удалены все соответствующие записи в таблице (1) (каскадное удаление). Синтаксис такого запроса следующий:

ALTER TABLE ИМЯ_ССЫЛАЮЩЕЙСЯ_ТАБЛИЦЫ ADD CONSTRAINT ИМЯ_ИДЕНТИФИКАТОРА FOREIGN KEY (ИМЯ_ИДЕНТИФИКАТОРА) REFERENCES ИМЯ_ТАБЛИЦЫ_НА_КОТОРУЮ_ССЫЛАЮТСЯ ON DELETE CASCADE

Пример 7.

Вновь изменяем таблицу AVAILABILITY и определяем, что если удалить препарат из таблицы PREPARATION, то должны удалиться все записи этого препарата в таблице AVAILABILITY.

alter table AVAILABILITY add constraint PR_ID foreign key(PR_ID) references PREPARATION on delete cascade

Теперь определим, что если удалить аптеку из таблицы PHARMACY, то должны удалиться все записи этой аптеки в таблице AVAILABILITY:

alter table AVAILABILITY add constraint PH_ID foreign key(PH_ID) references PHARMACY on delete cascade

Следующая команда модифицирует таблицу DEFICIT и определяет, что если удалить препарат из таблицы PREPARATION, то должны удалиться все записи этого препарата в таблице DEFICIT:

alter table DEFICIT add constraint PR_ID foreign key(PR_ID) references PREPARATION on delete cascade

Iv class="even">

Вновь модифицируем таблицу DEFICIT и определяем, что если удалить аптеку из таблицы PHARMACY, то должны удалиться все записи этой аптеки в таблице DEFICIT:

alter table DEFICIT add constraint PH_ID foreign key(PH_ID) references PHARMACY on delete cascade

Запросом с командой ALTER TABLE можно установить проверочное ограничение. Синтаксис такого запроса следующий:

ALTER TABLE ИМЯ_ТАБЛИЦЫ ADD CONSTRAINT CHECK_DATA CHECK(ПРОВЕРЯЕМОЕ_УСЛОВИЕ)

Пример 8. Продолжаем работать с базой данных сети аптек.

Нужно модифицировать таблицу AVAILABILITY и определить, что в одной и той же записи значение атрибута DateStart таблицы не должно быть меньше значения DateEnd.

ALTER TABLE AVAILABILITY ADD CONSTRAINT CHECK_DATA CHECK(DateStart

Изменяем таблицу DEFICIT и определяем, что в одной и той же записи значение атрибута DateStart таблицы не должено быть меньше значения DateEnd:

ALTER TABLE DEFICIT ADD CONSTRAINT CHECK_DATA CHECK(DateStart

Реляционные базы данных и язык SQL

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

Стандарт 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

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

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