Урок 9.2 · Время чтения: ~9 мин

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

ALTER TABLE в SQL: изменение существующей таблицы

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

Учебные базы на SQLtest доступны только для чтения, поэтому там эти операторы вернут ошибку прав доступа. Примеры используют схему Sakila; они проверены на MySQL 8.0 и PostgreSQL 17.

Синтаксис ALTER TABLE

ALTER TABLE table_name
    action [, action ...];

Действие (action) указывает, что изменить. Один ALTER TABLE может содержать несколько действий через запятую. Самые частые:

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

ALTER TABLE customer
ADD COLUMN loyalty_points INT NOT NULL DEFAULT 0;

Результат: каждый существующий клиент получает loyalty_points = 0; новые строки получают 0, если INSERT не задаёт значение.

Новый столбец добавляется в конец таблицы. MySQL может поставить его в другое место с помощью AFTER column_name или FIRST; PostgreSQL всегда добавляет столбец в конец.

NOT NULL без значения по умолчанию

Если в таблице уже есть строки, столбцу с NOT NULL нужно значение для них:

ALTER TABLE customer
ADD COLUMN phone VARCHAR(20) NOT NULL;

Результат в PostgreSQL: ошибка, column "phone" contains null values. MySQL выполняет оператор и заполняет существующие строки неявным значением по умолчанию для типа: здесь пустой строкой, для чисел — нулём.

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

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

Чтобы удлинить столбец email:

-- MySQL
ALTER TABLE customer
MODIFY COLUMN email VARCHAR(100) NOT NULL;

-- PostgreSQL
ALTER TABLE customer
ALTER COLUMN email TYPE VARCHAR(100);

Остальную часть определения столбца две СУБД обрабатывают по-разному:

• MODIFY в MySQL заменяет всё определение столбца. То, что вы не повторили, теряется: ALTER TABLE customer MODIFY active SMALLINT; превращает столбец NOT NULL DEFAULT 1 в столбец, допускающий NULL и без значения по умолчанию. Всегда пишите полное определение: MODIFY active SMALLINT NOT NULL DEFAULT 1.
• ALTER COLUMN в PostgreSQL меняет одно свойство за раз и сохраняет остальные:

ALTER TABLE customer
    ALTER COLUMN active TYPE INT,
    ALTER COLUMN active SET DEFAULT 1,
    ALTER COLUMN email SET NOT NULL;

В MySQL есть ещё CHANGE COLUMN old_name new_name definition, который переименовывает и переопределяет столбец за один шаг.

Когда данные не подходят

СУБД проверяет существующие строки на соответствие новому определению:

• Сокращение VARCHAR(50) до VARCHAR(10) не удаётся, если какое-то значение длиннее: «Data truncated for column 'email'» в MySQL, «value too long for type character varying(10)» в PostgreSQL.
• Добавление NOT NULL не удаётся, если в столбце есть NULL: «Invalid use of NULL value» в MySQL, «column contains null values» в PostgreSQL.

Сначала исправьте данные, например UPDATE customer SET email = '' WHERE email IS NULL;, затем повторите ALTER TABLE.

Переименование и удаление

ALTER TABLE customer RENAME COLUMN email TO email_address;

ALTER TABLE customer DROP COLUMN loyalty_points;

ALTER TABLE customer RENAME TO client;

Результат: столбец получает новое имя, столбец loyalty_points удаляется вместе с данными, а таблица переименовывается.

RENAME COLUMN работает в MySQL 8.0, MariaDB 10.5 и PostgreSQL; в SQL Server используется процедура sp_rename. Переименование не обновляет ваши запросы, представления и код приложения: всё, что использует старое имя, перестанет работать.

DROP COLUMN в MySQL нельзя отменить через ROLLBACK, потому что DDL неявно фиксирует транзакцию (см. урок 8.4). Сохраните данные, которые могут понадобиться.

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

Ограничения из CREATE TABLE можно добавить и позже. Давайте каждому имя через CONSTRAINT name, чтобы его было легко найти и удалить:

ALTER TABLE customer
ADD CONSTRAINT uq_customer_email UNIQUE (email);

ALTER TABLE payment
ADD CONSTRAINT chk_payment_amount CHECK (amount >= 0);

ALTER TABLE payment
ADD CONSTRAINT fk_payment_customer
    FOREIGN KEY (customer_id) REFERENCES customer (customer_id);

Первичный ключ добавляется так же: ALTER TABLE table_name ADD PRIMARY KEY (column_name);.

Существующие данные могут помешать ограничению

Новое ограничение проверяется на строках, которые уже есть в таблице. Если хотя бы одна строка его нарушает, оператор не выполняется целиком:

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

SELECT p.payment_id, p.customer_id
FROM payment AS p
LEFT JOIN customer AS c ON c.customer_id = p.customer_id
WHERE c.customer_id IS NULL;

Результат: «осиротевшие» строки, которые помешают внешнему ключу. Исправьте или удалите их, затем добавьте ограничение.

Поиск и удаление ограничений

Чтобы удалить ограничение, нужно знать его имя. Если имя не задали, СУБД сгенерировала его сама, например payment_ibfk_1 в MySQL или payment_customer_id_fkey в PostgreSQL. Их перечисляет представление information_schema.table_constraints:

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM information_schema.table_constraints
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'payment'
ORDER BY CONSTRAINT_TYPE, CONSTRAINT_NAME;

Результат на Sakila: fk_payment_customer, fk_payment_rental и fk_payment_staff (FOREIGN KEY) и PRIMARY (PRIMARY KEY). В PostgreSQL вместо DATABASE() фильтруйте по table_schema = 'public'.

Затем удалите его:

-- PostgreSQL, SQL Server and MySQL 8.0.19+
ALTER TABLE payment DROP CONSTRAINT fk_payment_customer;

-- MySQL: type-specific forms
ALTER TABLE payment DROP FOREIGN KEY fk_payment_customer;
ALTER TABLE payment DROP CHECK chk_payment_amount;
ALTER TABLE customer DROP INDEX uq_customer_email;

В MySQL ограничение UNIQUE — это индекс, поэтому оно удаляется через DROP INDEX.

ALTER TABLE на больших таблицах

На таблице с миллионами строк некоторые изменения перезаписывают всю таблицу и блокируют запись на время работы. Добавление столбца, допускающего NULL, или столбца с постоянным значением по умолчанию в современных версиях выполняется быстро: MySQL 8.0 делает это мгновенно (ALGORITHM=INSTANT), как и PostgreSQL 11 и новее. Изменение типа столбца, добавление внешнего ключа к большой таблице или удаление столбца в старых версиях может занять много времени.

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

Главное из урока:

• ALTER TABLE изменяет существующую таблицу на месте: столбцы, ограничения и имя таблицы.
• Обязательные столбцы в заполненные таблицы добавляйте с DEFAULT или заполните их до установки NOT NULL.
• В MySQL MODIFY заменяет всё определение столбца; повторяйте NOT NULL и DEFAULT, иначе они потеряются.
• Существующие строки должны соответствовать новому типу, NOT NULL или ограничению, иначе оператор не выполнится.
• Давайте ограничениям имена; безымянные ищите в information_schema.table_constraints и удаляйте через DROP CONSTRAINT.
• В MySQL ALTER TABLE неявно фиксирует транзакцию и не откатывается.

Часто задаваемые вопросы

Как добавить столбец в существующую таблицу в SQL?

Используйте ALTER TABLE table_name ADD COLUMN column_name data_type;, например ALTER TABLE customer ADD COLUMN loyalty_points INT NOT NULL DEFAULT 0;. Существующие строки получат значение по умолчанию или NULL, если значения по умолчанию у столбца нет.

Как изменить тип данных столбца в SQL?

В MySQL — ALTER TABLE customer MODIFY COLUMN email VARCHAR(100) NOT NULL; с полным определением столбца. В PostgreSQL — ALTER TABLE customer ALTER COLUMN email TYPE VARCHAR(100);. Изменение не выполнится, если существующие значения не подходят под новый тип.

Как добавить внешний ключ в существующую таблицу?

ALTER TABLE payment ADD CONSTRAINT fk_payment_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id);. Каждый существующий customer_id в payment должен быть в customer, иначе оператор не выполнится.

Как удалить ограничение, если неизвестно его имя?

Найдите его в information_schema.table_constraints для нужной таблицы, затем выполните ALTER TABLE table_name DROP CONSTRAINT constraint_name;. В MySQL внешние ключи можно удалить и через DROP FOREIGN KEY, а ограничения уникальности — через DROP INDEX.

Вопросы для собеседования

Чем MODIFY COLUMN в MySQL отличается от ALTER COLUMN в PostgreSQL?

MODIFY COLUMN заменяет полное определение столбца, поэтому атрибуты, которые вы не повторили, например NOT NULL или DEFAULT, удаляются. ALTER COLUMN в PostgreSQL меняет одно свойство, например тип или значение по умолчанию, и сохраняет остальные.

Почему добавление NOT NULL или UNIQUE к существующей таблице может не удаться?

Ограничение проверяется на строках, которые уже есть в таблице. NOT NULL не пройдёт, если есть значение NULL, UNIQUE — при дубликатах, внешний ключ — при значениях, которых нет в связанной таблице. Сначала очистите данные.

Как добавить обязательный столбец в большую рабочую таблицу?

Добавить его с постоянным DEFAULT — в MySQL 8.0 и PostgreSQL 11+ это быстрое изменение метаданных; или добавить как допускающий NULL, заполнить порциями через UPDATE, затем установить NOT NULL. Проверить на копии и выполнять при низкой нагрузке.