Зміна структури таблиці

1. Зміна структури бази даних

І нарешті, найсмачніша частина роботи з базами — зміна бази даних. Якщо ти думаєш, що це щось дуже просте, то ось тобі анекдот на цю тему:

Майстер СТО, витираючи руки, здає клієнту машину і принагідно базікає:
- А ось ви ким працюєте?
- Кардіохірургом, операції на серці проводжу.
– І багато платять?
- 20,000 $ за операцію.
- Ніфіга собі ... Адже ми, по суті, одним і тим самим займаємося, двигуни перебираємо, а мені лише 300 $ платять ...
- Хочеш так само, як я, заробити?
- Хочу...
Хірург заводить двигун у машині і звертається до майстра:
– Перебирай!

З базами даних така ж сама ситуація. Ти хочеш винести частину даних окрему таблицю? Добре, тобі потрібно:

• створити нову таблицю
• скопіювати дані до цієї нової таблиці
• видалити колонки зі старої таблиці
• змінити всі SQL-запити, які посилалися на стару таблицю
• додати SQL-запити, які будуть звертатися до нової таблиці
• змінити Java-код, який працював з таблицями по-старому
• змінити сценарії бекапу та відновлення даних, тому що тепер структури таблиць не збігаються
• якщо в тебе є тестові дані для розробників, потрібно змінити їх

Хоча насправді буває ще гірше:

Нова структура БД:

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

Скрипти міграції:

• пишеш скрипти, які змінюватимуть таблиці даних тощо на реальній базі даних
• запускаєш ці скрипти на машині зі старою структурою бази та дивишся, як вона змінюється
• верифікуєш усі критичні місця нової бази

Апдейт на продажі

• зупиняєш production-базу
• робиш повний бекап
• виконуєш скрипти
• оскільки на проді зазвичай купа, дані скрипти можуть працювати годинами

Запускаєш прод і сподіваєшся, що тобі не доведеться все відкочувати назад.

2. Оператор ALTER TABLE

З іншого боку, сам скрипт, який змінює структуру таблиць, дуже простий. Чимось він схожий на скрипт створення таблиці. Загальний вигляд запиту зміни таблиці:

ALTER TABLE таблиця
команда 1,
команда 2,
команда N

        

Команди бувають дуже різними, але можна виділити три основні групи:

• ADD — додає щось до таблиці
• MODIFY — змінює щось у таблиці
• DROP — видаляє щось у таблиці

І коли я говорю щось, то йдеться не про дані, а про структуру таблиці.

Наприклад, ти вирішуєш додати до таблиці нову колонку. В такому разі потрібно виконати такий запит:

ALTER TABLE таблиця
ADD COLUMN ім'я тип
        

Давай напишемо запит, який додасть до нашої таблиці employee колонку email:

ALTER TABLE employee
ADD COLUMN email  VARCHAR(10)
        

Тепер поміняємо довжину рядка email з 10 на 100: для цього нам знадобиться новий скрипт:

ALTER TABLE employee
MODIFY COLUMN email VARCHAR(100)
        

Якщо ти хочеш змінити якісь атрибути колонки, але не її тип, тобі потрібна команда ALTER COLUM. Давай встановимо значення email за замовчуванням:

ALTER TABLE employee
ALTER COLUMN email VARCHAR(100) DEFAULT 'test@test.com'
        

І, нарешті, колонку можна видалити, якщо вона тобі не потрібна:

ALTER TABLE employee
DROP COLUMN email 
        

3. Додавання та видалення первинного ключа

І ще кілька корисних прикладів.

Припустимо, ми забули оголосити колонку id первинним ключем. Завжди це можна зробити окремо через ALTER TABLE:

ALTER TABLE employee
ADD PRIMARY KEY (id);
        

Видалити його ще простіше:

ALTER TABLE employee
DELETE PRIMARY KEY;