ALTER TABLE в SQL

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

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

Рассмотрим таблицу shippers в нашей базе данных. Ее структура выглядит следующим образом:

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| shipper_id   | int         | NO   | PRI | NULL    | auto_increment |
| shipper_name | varchar(60) | NO   |     | NULL    |                |
| phone        | varchar(60) | NO   |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

Мы будем использовать таблицу shippers во всех дальнейших примерах с ALTER TABLE.

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

Предположим, что нам нужно расширить существующую таблицу shippers, добавив еще один столбец. Давайте разберемся, как это сделать с помощью SQL-команд.

ALTER TABLE имя_таблицы ADD имя_столбца тип_данных ограничения;

Следующий оператор добавляет новый столбец fax в таблицу shippers.

ALTER TABLE shippers ADD fax VARCHAR(20);

Если вы посмотрите на структуру таблицы с помощью команды DESCRIBE shippers; после выполнения приведенной выше команды, то увидите следующее:

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| shipper_id   | int         | NO   | PRI | NULL    | auto_increment |
| shipper_name | varchar(60) | NO   |     | NULL    |                |
| phone        | varchar(60) | NO   |     | NULL    |                |
| fax          | varchar(20) | YES  |     | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

Примечание. Если вы хотите добавить NOT NULL-столбец в существующую таблицу, то нужно указать явное значение по умолчанию. Это значение используется для заполнения нового столбца для каждой строки, которая уже существует в таблице.

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

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

mysql> ALTER TABLE shippers ADD fax VARCHAR(20) AFTER shipper_name;

В MySQL существует еще одно условие — FIRST, которое можно использовать для добавления нового столбца на первое место в таблице. Просто замените AFTER на FIRST в предыдущем примере и тогда столбец fax добавится в начало таблицы shippers.

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

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

ALTER TABLE имя_таблицы
MODIFY имя_столбца определение_столбца AFTER имя_столбца;

Следующий оператор помещает столбец fax после столбца shipper_name в таблице shippers:

mysql> ALTER TABLE shippers MODIFY fax VARCHAR(20) AFTER shipper_name;

Как добавить ограничения

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

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

ALTER TABLE table_name ADD UNIQUE (column_name,...);

Следующий оператор добавляет ограничение UNIQUE к столбцу phone.

mysql> ALTER TABLE shippers ADD UNIQUE (phone);

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

Аналогично, если вы создали таблицу без PRIMARY KEY, можно добавить его с помощью следующего выражения:

ALTER TABLE имя_таблицы ADD PRIMARY KEY (имя_столбца,...);

А вот этот оператор добавляет ограничение PRIMARY KEY к столбцу shipper_id, если он не определен.

mysql> ALTER TABLE shippers ADD PRIMARY KEY (shipper_id);

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

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

ALTER TABLE имя_таблицы DROP COLUMN имя_столбца;

Следующий оператор удалит наш недавно добавленный столбец fax из таблицы shippers.

mysql> ALTER TABLE shippers DROP COLUMN fax;

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

+--------------+-------------+------+-----+---------+----------------+
| Field        | Type        | Null | Key | Default | Extra          |
+--------------+-------------+------+-----+---------+----------------+
| shipper_id   | int         | NO   | PRI | NULL    | auto_increment |
| shipper_name | varchar(60) | NO   |     | NULL    |                |
| phone        | varchar(60) | NO   | UNI | NULL    |                |
+--------------+-------------+------+-----+---------+----------------+

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

В SQL Server можно изменить тип данных столбца с помощью выражения ALTER, как показано ниже:

ALTER TABLE имя_таблицы ALTER COLUMN имя_таблицы новый_тип_данных;

Однако MySQL не поддерживает синтаксис ALTER COLUMN. Там используется альтернативное выражение MODIFY, которое изменяет столбец:

ALTER TABLE имя_таблицы MODIFY имя_столбца новый_тип_данных;

Следующий оператор изменяет текущий тип данных столбца phone в таблице shippers с VARCHAR на CHAR и длину с 20 на 15.

mysql> ALTER TABLE shippers MODIFY phone CHAR(15);

Аналогично можно использовать выражение MODIFY для переключения допущения нулевых значений в столбце таблицы MySQL. Это реализуется при помощи повторного определения столбца и добавления ограничения NULL или NOT NULL в конце, как показано ниже:

mysql> ALTER TABLE shippers MODIFY shipper_name CHAR(15) NOT NULL;

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

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

ALTER TABLE текущее_имя_таблицы RENAME новая_имя_таблицы;

Например, следующий оператор переименует таблицу shippers в shipper.

mysql> ALTER TABLE shippers RENAME shipper;

Такого же результата можно добиться с помощью оператора RENAME TABLE:

mysql> RENAME TABLE shippers TO shipper;