Мастерство ALTER TABLE: руководство по изменению структуры SQL-таблиц

Общий синтаксис команды ALTER TABLE выглядит так:

Где:

• имя_таблицы — название таблицы, в которую хотите внести изменения;

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

• [параметры] — необязательный элемент, в этом месте вы указываете параметры, которые уточнят операцию.

Общий синтаксис команды в разных СУБД похож, но между конкретными системами есть различия — при работе стоит их учитывать. Например, одно из ключевых различий синтаксиса ALTER TABLE в PostgreSQL и MySQL — количество действий за один запрос. В PostgreSQL за раз можно сделать что-то одно, а в MySQL можно перечислять действия через запятую.

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

Здесь расскажем о двух базовых операциях с ALTER TABLE — добавлении и удалении столбцов. Рассмотрим конкретные примеры и дадим варианты кода.

Добавление столбцов (ADD COLUMN)

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

Эта команда добавляет в таблицу products новый столбец description, чтобы хранить там текстовую информацию.

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

Если столбец обязателен для заполнения, вы можете сразу добавить ограничение NOT NULL.

Удаление столбцов (DROP COLUMN)

Бывает, что некоторые столбцы становятся ненужными, например, там информация в них устарела или данные добавлены по ошибке. Удалить столбец можно одной командой:

Удаление столбца — это необратимая операция. Перед тем, как выполнить команду, проверьте вот что:

• отсутствуют внешние ключи — ссылки на этот столбец из других таблиц;

• столбец не используется в представлениях (views) или хранимых процедурах (stored procedures);

• данные из столбца точно не понадобятся в будущем.

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

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

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

Чтобы изменить тип данных, выполните:

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

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

Переименование столбца (RENAME COLUMN)

Названия столбцов в таблице могут быть непонятными. Допустим, раздел с адресами офисов компании называется location вместо office addresses. Первое наименование не дает ответа на вопрос, о какой локации идет речь, и при работе с БД можно легко запутаться.

Это не единственная ситуация, когда может понадобиться переименовать столбец. Чтобы это сделать, выполните:

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

Важно: если на столбец ссылаются другие объекты базы (views, functions), их тоже нужно будет обновить. Проверить зависимости можно через графический интерфейс pgAdmin или через специальные утилиты.

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

Переименование таблицы

Бывают ситуации, когда название таблицы перестает отражать ее содержание или не соответствует новым бизнес-правилам. Например, таблица user_orders может стать просто orders, если в системе остались только заказы пользователей.

Для этого используется:

Что важно знать о переименовании:

• Операция выполняется мгновенно и не блокирует таблицу надолго.

• Информация внутри остается нетронутой — меняется только название самой таблицы.

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

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

Рефакторинг структуры таблицы

Когда простого переименования недостаточно, может потребоваться более глубокая переработка структуры таблицы. В качестве примера рассмотрим ситуацию, когда в БД предприятия имена и фамилии работников находятся в одном столбце, но нужно разнести их по разным. Тогда нужно разделить столбец «полное имя» на два — «имя» и «фамилия»:

Рассмотрим обратную ситуацию: есть два столбца, но вы хотите их объединить:

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

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

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

Добавление и удаление первичных и внешних ключей

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

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

Чтобы удалить ключи, вам надо знать имя ограничения. Важно — имя именно ограничения, а не ключа. Если имя не задано явно, значит, система сгенерировала его автоматически.

Управление ограничениями

Ограничения помогают контролировать качество данных на уровне БД. Вот основные типы:

• UNIQUE гарантирует уникальность значений в столбце:

• CHECK проверяет значения по заданному условию:

• NOT NULL запрещает пустые значения:

Ограничения CHECK и NOT NULL установятся, только когда существующие данные им соответствуют. Что это значит: если в таблице есть строки price = 0, ограничение CHECK (price > 0) не сработает. А при попытке добавить NOT NULL к столбцу, где есть пустые значения (NULL), команда завершится ошибкой.

• DEFAULT задает значение по умолчанию:

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

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

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

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

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

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