Изменяем структуры таблицы

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

ALTER TABLE имя_таблицы ДЕЙСТВИЕ параметры

Это можно перевести как:

ПОДПРАВИТЬ ТАБЛИЦУ имя ДЕЙСТВИЕ параметры

Имя таблицы – тут все ясно, просто имя. В качестве действия может быть:

ADD – добавить колонку

MODIFY – обновить колонку

DROP – удалить колонку

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

Давайте добавим в таблицу player колонку position, в которой будем хранить в качестве числа позицию игрока – вратарь, нападающий, защитник и т.д: Колонка будет числовая int и обязательно должна хранить какое-то значение NOT NULL. Такая операция может быть выполнена следующей командой:

ALTER TABLE player ADD position int not null;

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

ALTER TABLE player ADD position int null;

То тут вопросов нет, тогда база просто для существующих строк в колонке position будет показывать NULL – отсутствующее значение. MySQL не из таких, она позволяет NOT NULL, просто если это числовой поле, то для существующих записей колонка будет содержать значение – число 0:

+----------+-----------+----------+----------+
| playerid | firstname | lastname | position |
+----------+-----------+----------+----------+
|        1 | Ronaldo   | NULL     |        0 |
|        2 | Andre     | NULL     |        0 |
+----------+-----------+----------+----------+

А что, если вы реально хотите NOT NULL, а ваша конкретная база данных не позволяет создавать такое поле при наличии строк, что тогда делать? Для таких баз данных есть два выхода:

Вариант 1: создать поле со значением по умолчанию. Давайте поле position2 со значением по умолчанию -1:

ALTER TABLE player ADD position2 int not null default -1;

Для всех существующих записей будет установлено значение -1 и поэтому колонка NOT NULL будет создана без проблем:

+----------+-----------+----------+----------+-----------+
| playerid | firstname | lastname | position | position2 |
+----------+-----------+----------+----------+-----------+
|        1 | Ronaldo   | NULL     |        0 |        -1 |
|        2 | Andre     | NULL     |        0 |        -1 |
+----------+-----------+----------+----------+-----------+

Первый вариант работает, но у него есть недостаток – теперь при вставке новых записей если пользователь не укажет позицию, то будет использоваться значение по умолчанию. Не факт, что мы хотим этого. Ладно при создании использовать какое-то начальное значение, а потом мы скорей всего захотим заставить пользователя четко указывать позицию.

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

Второй вариант – создать поле с разрешением нулевых значений, потом установить значение для всех существующих записей в какое-то начальное и потом изменить тип данных:

ALTER TABLE player ADD position3 int null;
UPDATE player SET position3 = -1;
ALTER TABLE player MODIFY position3 int not null;

Здесь мы создаем числовое поле position3, которое позволяет NULL значения. Потом мы выполняем UPDATE и устанавливаем начальное значение для существующих строк. Теперь все существующие строки имеют какое-то число в колонке position3, и мы можем теперь сделать его NOT NULL с помощью ALTER TABLE MODIFY.

Таким образом мы плавно подошли к тому, как изменять существующие поля. Как видите команда MODIFY выглядит практически идентично созданию новых колонок. Вы можете не только менять NULL на NOT NULL, но и даже менять тип данных и в совместимых типах это сработает без проблем.

Колонка position – это число, и оно легко превращается в строку, поэтому мы можем без проблем превратить position3 в строку:

ALTER TABLE player MODIFY position3 varchar(10) not null;

Эта команда сменила колонку position3 с числа на строку.

Ну и последнее, что мы можем сделать с колонками – удалить их. Если колонка уже больше не нужна, то ее можно убить с помощью команды:

ALTER TABLE player DROP position3;

Заодно можно убить и колонку position2, которую я создал просто для примера и она дальше нам не понадобиться.

ALTER TABLE player DROP position2;