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

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

Разберем синтаксис ALTER TABLE и выясним, чем он отличается в MySQL, PostgreSQL и MS SQL Server.

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

ALTER TABLE относится к DDL — группе SQL-операторов, которые меняют структуру базы данных: таблицы, индексы, связи, ограничения. 

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

ALTER TABLE table_name
<действие, которое нужно выполнить>;

Здесь:

• ALTER TABLE указывает, что нужно изменить структуру существующей таблицы;
• table_name — название таблицы, которую нужно изменить.

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

Команды с оператором ALTER TABLE

ALTER TABLE позволяет выполнять несколько типов действий:

При написании команд с ALTER TABLE их синтаксис в PostgreSQL, MySQL и SQL Server может отличаться.

Разберем команды подробнее.

Как добавить столбец в таблицу

Например, у нас есть таблица users:

CREATE TABLE users (
    id INT,
    name VARCHAR(100),
    email VARCHAR(255)
);

Мы заметили, что в ней не хватает поля с датой регистрации пользователя (создания записи). Добавим его с помощью ALTER TABLE:

ALTER TABLE users
ADD created_at TIMESTAMP;

В итоге структура таблицы будет такой:

Для просмотра таблиц можно использовать бесплатный инструмент для работы с базами данных DBeaver:

Команда выше актуальна для PostgreSQL и MySQL, но в SQL Server вместо TIMESTAMP используют DATETIME2. 

ALTER TABLE users
ADD created_at DATETIME2;

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

Выясним, какое значение там появится.

Если новый столбец nullable

Если новый столбец допускает NULL-значения, поведение зависит от того, указано ли значение по умолчанию (DEFAULT):

• В PostgreSQL и MySQL в новых и старых строках в новом столбце значение будет либо NULL, либо DEFAULT. 

Чтобы указать DEFAULT, нужно дополнить команду так:

ALTER TABLE users
ADD created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

В SQL Server старые строки останутся NULL, даже если указать DEFAULT:

ALTER TABLE users
ADD created_at DATETIME2
CONSTRAINT DF_users_created_at DEFAULT SYSUTCDATETIME();

Чтобы они были заполнены, нужно дополнительно указать WITH VALUES:

ALTER TABLE users
ADD created_at DATETIME2
CONSTRAINT DF_users_created_at DEFAULT SYSUTCDATETIME() WITH VALUES;

Если новый столбец не допускает NULL

Если новый столбец не допускает NULL-значения и таблица уже содержит строки, то DEFAULT обязателен. 

В SQL Server тогда не обязательно указывать WITH VALUES — старые строки заполнятся DEFAULT значениями.

Небольшая памятка по добавлению столбцов

Как добавить несколько столбцов

Через ALTER TABLE можно добавить сразу несколько столбцов.

Например, в таблице users не хватает информации о статусе учетной записи пользователя и верификации его аккаунта. Добавим столбцы status и is_verified.

В PostgreSQL и MySQL команда будет такой:

ALTER TABLE users
ADD COLUMN status VARCHAR(20),
ADD COLUMN is_verified BOOLEAN;

В SQL Server COLUMN не используют, вместо BOOLEAN используют BIT, а вместо VARCHAR могут использовать NVARCHAR для хранения Unicode-строк. Команда будет выглядеть так:

ALTER TABLE users
ADD status NVARCHAR(20),
    is_verified BIT;

Обе команды добавляют в таблицу users два новых столбца:

Итоговая таблица будет такой:

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

Но если важно, чтобы созданное поле было NOT NULL, нужно указать DEFAULT-значения. Иначе команда может завершиться ошибкой, так как поле не должно быть NULL, но SQL не знает, что указывать по умолчанию. 

• PostgreSQL и MySQL

ALTER TABLE users
ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'active',
ADD COLUMN is_verified BOOLEAN NOT NULL DEFAULT FALSE;

Статус пользователя по умолчанию будет active, а признак подтверждения аккаунта — FALSE.

• SQL Server

ALTER TABLE users
ADD status NVARCHAR(20) NOT NULL
        CONSTRAINT DF_users_status DEFAULT 'active',
    is_verified BIT NOT NULL
        CONSTRAINT DF_users_is_verified DEFAULT 0; 

Как обсуждали ранее, если добавленный столбец NOT NULL, то WITH VALUES указывать не обязательно, так как DEFAULT заполнит значения и у новых, и у старых строк.

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

С ALTER TABLE можно менять тип данных столбца. Например, в таблице users поле age изначально было INT, но мы хотим заменить его на BIGINT, чтобы хранить значения большего диапазона.

В PostgreSQL команда будет такой:

ALTER TABLE users
ALTER COLUMN age TYPE BIGINT;

В PostgreSQL можно указывать правила преобразования через USING прямо в конструкции ALTER TABLE:

ALTER TABLE users
ALTER COLUMN age TYPE BIGINT
USING replace(age, ' years', '')::BIGINT;

Команда с таким правилом перед изменением типа берет старое значение age, убирает из него текст years, а оставшуюся часть приводит к BIGINT.

Изменение типа и установка NOT NULL в PostgreSQL обычно записывают как отдельные действия в одной команде или отдельными командами:

ALTER TABLE users
ALTER COLUMN age TYPE BIGINT,
ALTER COLUMN age SET NOT NULL;

В MySQL используют MODIFY COLUMN:

ALTER TABLE users
MODIFY COLUMN age BIGINT;

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

ALTER TABLE users
MODIFY COLUMN age BIGINT NOT NULL;

В SQL Server используют ALTER COLUMN, но без слова TYPE:

ALTER TABLE users
ALTER COLUMN age BIGINT;

Тут также лучше явно указывать, допускает ли столбец NULL:

ALTER TABLE users
ALTER COLUMN age BIGINT NOT NULL;

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

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

Как переименовать столбец 

Представим, что в таблице users есть столбец name, но в нем хранится полное имя пользователя. Тогда его можно переименовать в full_name.

В PostgreSQL и MySQL 8.0+ команда будет такой:

ALTER TABLE users
RENAME COLUMN name TO full_name;

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

ALTER TABLE users
CHANGE name full_name VARCHAR(100) NOT NULL DEFAULT '';

CHANGE не только переименовывает столбец, но и заново описывает его определение: тип, NULL или NOT NULL, DEFAULT, комментарий и другие свойства, если они были. Поэтому, чтобы случайно не изменить свойства столбца, нужно их повторно указать в команде.

В SQL Server обычно используют системную процедуру sp_rename:

EXEC sp_rename 'users.name', 'full_name', 'COLUMN';

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

Как удалить столбец из таблицы

Чтобы удалить столбец из таблицы, используют DROP COLUMN.

В PostgreSQL, MySQL и SQL Server базовый синтаксис одинаковый:

ALTER TABLE users
DROP COLUMN age;

Эта команда удаляет столбец age из таблицы users. Вместе со столбцом удаляются все данные, которые в нем хранились.

Важно не путать удаление столбца и удаление строк. Команда DROP COLUMN меняет структуру таблицы. Она удаляет колонку целиком. 

А команда DELETE удаляет строки из таблицы, но не меняет ее структуру, например:

DELETE FROM users
WHERE age < 18;

Запрет на удаление

Если от столбца зависят ограничения, индексы, внешние ключи или вычисляемые столбцы, СУБД может не разрешить удаление сразу. Например, если столбец участвует в первичном или внешнем ключе, сначала нужно удалить или изменить связанное ограничение.

Поэтому перед удалением столбца нужно:

1. Проверить, использует ли его приложение.
2. Проверить SQL-запросы, отчеты и представления.
3. Убедиться, что данные больше не нужны.
4. Сделать резервную копию или убедиться, что есть возможность отката.
5. Удалить столбец отдельной миграцией.

Как добавить ограничения PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK

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

PRIMARY KEY

PRIMARY KEY делает столбец первичным ключом таблицы. Значения в таком столбце должны быть уникальными и не должны быть NULL.

Например, добавим первичный ключ по столбцу id:

ALTER TABLE users
ADD CONSTRAINT pk_users PRIMARY KEY (id);

Команда создает ограничение pk_users и делает столбец id первичным ключом.

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

SELECT id, COUNT(*)
FROM users
GROUP BY id
HAVING COUNT(*) > 1;

Кроме того, нужно проверить, что в id нет NULL:

SELECT *
FROM users
WHERE id IS NULL;

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

FOREIGN KEY

FOREIGN KEY связывает две таблицы. Например, есть таблица orders, где хранится user_id, и таблица users, где есть id. Чтобы заказ ссылался только на существующего пользователя, добавляют внешний ключ:

ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id);

Эта команда создает правило, что значение в orders.user_id должно существовать в users.id.

Перед добавлением внешнего ключа нужно проверить, нет ли в orders.user_id значений, которых нет в таблице users:

SELECT o.*
FROM orders o
LEFT JOIN users u ON u.id = o.user_id
WHERE u.id IS NULL;

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

UNIQUE

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

ALTER TABLE users
ADD CONSTRAINT uq_users_email UNIQUE (email);

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

Перед добавлением UNIQUE нужно проверить, есть ли дубли:

SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Если такие строки есть, сначала нужно решить, какие записи оставить, а какие необходимо изменить или удалить.

Если столбец допускает NULL, поведение UNIQUE с пустыми значениями может отличаться в разных СУБД. Поэтому для полей вроде email, где значение должно быть обязательным и уникальным, лучше использовать связку NOT NULL и UNIQUE.

CHECK

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

ALTER TABLE users
ADD CONSTRAINT chk_users_age
CHECK (age >= 0);

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

Перед добавлением CHECK нужно проверить, что существующие строки уже соответствуют новому правилу:

SELECT *
FROM users
WHERE age < 0;

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

SQL ALTER TABLE: коротко о главном

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

Основные команды и особенности синтаксиса:

• Через ADD COLUMN можно добавлять один или несколько новых столбцов.
• При добавлении столбцов в непустую таблицу важно учитывать NULL, NOT NULL и DEFAULT.
• Для изменения типа данных используют:
  • PostgreSQL — ALTER COLUMN … TYPE
  • MySQL — MODIFY COLUMN
  • SQL Server — ALTER COLUMN
• Переименовать столбцы можно через RENAME COLUMN, а в SQL Server — через sp_rename.
• DROP COLUMN полностью удаляет столбец вместе со всеми данными.
• PRIMARY KEY задает уникальный идентификатор строки.
• FOREIGN KEY связывает таблицы и обеспечивает ссылочную целостность.
• UNIQUE запрещает дублирование значений.
• CHECK задает условия допустимых значений в столбце.
• Перед добавлением ограничений нужно проверять существующие данные на ошибки и дубликаты.
• Перед удалением столбцов рекомендуем проверять зависимости, запросы и делать резервные копии данных.