ИТ.03 - 13 - Изменение структуры таблиц. Операция ALTER TABLE

Введение

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

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

Учебная база данных «Библиотека»

-- Таблица авторов
CREATE TABLE
  authors (
    id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    birth_year INTEGER
  )
;

-- Таблица книг
CREATE TABLE
  books (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    publication_year INTEGER,
    isbn TEXT UNIQUE,
    genre TEXT,
    author_id INTEGER NOT NULL,
    pages INTEGER,
    available_copies INTEGER DEFAULT 1,
    FOREIGN KEY (author_id) REFERENCES authors(id)
  )
;

-- Вставка данных в таблицу авторов
INSERT INTO
  authors (id, first_name, last_name, birth_year)
VALUES
  (1, 'Лев', 'Толстой', 1828),
  (2, 'Федор', 'Достоевский', 1821),
  (3, 'Михаил', 'Булгаков', 1891),
  (4, 'Александр', 'Пушкин', 1799),
  (5, 'Александр', 'Грибоедов', 1795)
;

-- Вставка данных в таблицу книг
INSERT INTO
  books (id, title, publication_year, isbn, genre, author_id, pages, available_copies)
VALUES
  (1, 'Война и мир', 1869, '978-5-17-081547-7', 'Роман', 1, 1225, 3),
  (2, 'Преступление и наказание', 1866, '978-5-17-079456-3', 'Роман', 2, 671, 2),
  (3, 'Мастер и Маргарита', 1967, '978-5-17-082141-6', 'Роман', 3, 480, 1),
  (4, 'Евгений Онегин', 1833, '978-5-17-081548-4', 'Поэма', 4, 224, 4),
  (5, 'Анна Каренина', 1877, '978-5-17-081549-1', 'Роман', 1, 864, 2),
  (6, 'Собачье сердце', 1925, '978-5-17-081550-7', 'Повесть', 3, 120, 3),
  (7, 'Идиот', 1869, '978-5-17-081551-4', 'Роман', 2, 640, 1),
  (8, 'Братья Карамазовы', 1880, '978-5-17-081552-1', 'Роман', 2, 992, 2),
  (9, 'Горе от ума', 1825, '978-5-17-081553-8', 'Комедия', 5, 224, 2)
;

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

Команда .tables в консоли SQLite выводит все таблицы и представления текущей базы. Её удобно запускать перед и после операций ALTER TABLE или DROP TABLE, чтобы быстро увидеть, как изменилась структура.

-- Посмотрим, какие таблицы есть в учебной базе
.tables

Команда ALTER TABLE

ALTER TABLE — это команда SQL, которая позволяет изменять структуру существующей таблицы в базе данных. С её помощью можно:

• добавлять новые столбцы
• удалять существующие столбцы
• изменять типы данных столбцов
• переименовывать столбцы
• переименовывать таблицу

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

Как запускать примеры

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

-- включаем явный вывод NULL
.nullvalue 'NULL'

-- включаем проверку внешних ключей
PRAGMA foreign_keys = ON;

-- дальше может идти ваш код
##CODE##

• PRAGMA foreign_keys = ON действует только в текущем подключении, поэтому команда должна выполняться перед CREATE TABLE, INSERT и другими примерами.
• .nullvalue 'NULL' помогает отличить настоящий NULL от пустой строки в текстовом выводе консоли.
• Все интерактивные блоки ниже уже завязаны на шаблон #show_null_and_enable_foreign_keys, но в собственных проектах команды следует добавлять вручную.

Добавление столбца

Чтобы добавить новый столбец в таблицу, используйте следующий синтаксис:

ALTER TABLE table_name ADD COLUMN column_name data_type [constraints];

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

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

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

-- Проверим структуру до изменения
PRAGMA table_info(books);

-- Добавим новый столбец
ALTER TABLE books ADD COLUMN description TEXT;

-- Убедимся, что столбец появился
PRAGMA table_info(books);

После выполнения этой команды в таблице появится новый столбец description типа TEXT.

Пример: добавление столбца с ограничениями

Можно также добавлять столбцы с ограничениями. Например, добавим столбец для хранения рейтинга книги (от 1 до 5):

-- Сравним структуру до изменения
PRAGMA table_info(books);
.schema books

-- Добавим столбец с ограничением
ALTER TABLE books ADD COLUMN rating INTEGER
CHECK (rating >= 1 AND rating <= 5);

-- Проверим актуальную схему
PRAGMA table_info(books);
.schema books

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

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

ALTER TABLE old_table_name RENAME TO new_table_name;

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

Если мы хотим переименовать таблицу books в library_books:

-- Посмотрим список таблиц до переименования
.tables

-- Выполним переименование
ALTER TABLE books RENAME TO library_books;

-- Убедимся, что новое имя появилось
.tables

После выполнения этой команды таблица будет доступна под новым именем library_books.

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

Для переименования столбца используется следующий синтаксис:

ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name;

Пример: переименование столбца

Если мы хотим переименовать столбец title в book_title:

-- Фиксируем структуру до переименования столбца
PRAGMA table_info(books);

-- Переименуем столбец
ALTER TABLE books RENAME COLUMN title TO book_title;

-- Проверим результат
PRAGMA table_info(books);

Удаление столбца

Удаление столбца выполняется тем же оператором ALTER TABLE, что и другие операции изменения структуры таблицы:

ALTER TABLE table_name DROP COLUMN column_name;

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

Если мы хотим удалить столбец publication_year из таблицы books:

-- Снимем структуру до удаления столбца
PRAGMA table_info(books);

-- Удалим столбец
ALTER TABLE books DROP COLUMN publication_year;

-- Убедимся, что столбца больше нет
PRAGMA table_info(books);

Ограничения SQLite при работе с ALTER TABLE

SQLite имеет некоторые ограничения при работе с командой ALTER TABLE по сравнению с другими СУБД:

1. Невозможно изменить тип данных существующего столбца напрямую
2. Невозможно добавить ограничения к существующим столбцам
3. Невозможно удалить ограничения из существующих столбцов

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

Обходные пути для ограничений SQLite

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

Для изменения типа данных столбца в SQLite необходимо:

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

Пример: изменение типа данных

Предположим, мы хотим хранить не только числовой год издания, но и текстовые пометки вроде «не позднее 1850» или полную ISO‑дату (2024-09-12). Создадим новое текстовое поле published, в которое перенесём прежние значения publication_year:

-- Базовая структура исходной таблицы
PRAGMA table_info(books);

-- 1. Создаём новую таблицу с нужной структурой
CREATE TABLE books_new (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  published TEXT,
  isbn TEXT UNIQUE,
  genre TEXT,
  author_id INTEGER NOT NULL,
  pages INTEGER,
  available_copies INTEGER DEFAULT 1,
  FOREIGN KEY (author_id) REFERENCES authors(id)
);

-- 2. Копируем данные, преобразуя тип
INSERT INTO books_new SELECT
  id, title, CAST(publication_year AS TEXT), isbn, genre,
  author_id, pages, available_copies
FROM books;

-- 3. Удаляем старую таблицу
DROP TABLE books;

-- 4. Переименовываем новую таблицу
ALTER TABLE books_new RENAME TO books;

-- Подтверждаем структуру обновленной таблицы
PRAGMA table_info(books);

-- Добавляем книгу с текстовой пометкой вместо года
INSERT INTO books (id, title, published, isbn, genre, author_id, pages, available_copies)
VALUES (10, 'Старинная рукопись', 'не позднее 1850', '978-5-17-099999-7', 'Рукопись', 4, 180, 1);

-- Проверяем, что новое поле сохраняет текст
SELECT title, published FROM books;

Вставка через INSERT INTO ... SELECT ...

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

INSERT INTO новая_таблица (список_столбцов)
SELECT выражения_или_столбцы
FROM старая_таблица
WHERE условия;

Такая запись читается как «вставь в новую таблицу результаты запроса».

Внутри SELECT можно:

• перечислить столбцы 1:1;
• вычислять новые значения (например, при помощи различных функций или математических выражений);
• фильтровать строки через WHERE, чтобы переносить только часть данных.

Ниже — практическая демонстрация, в которой мы переносим список сотрудников и на лету формируем двухбуквенный код страны.

-- создаём таблицу-источник
CREATE TABLE employees_src (id INTEGER PRIMARY KEY, full_name TEXT, country TEXT);
INSERT INTO employees_src VALUES
  (1, 'Анна Иванова', 'Russia'),
  (2, 'Эльчин Сафаралиев', 'Azerbaijan');

-- создаём новую таблицу с дополнительным столбцом
CREATE TABLE employees_dst (
  id INTEGER PRIMARY KEY,
  full_name TEXT,
  country TEXT,
  country_code TEXT
);

-- переносим данные и сразу вычисляем новое поле
INSERT INTO employees_dst (id, full_name, country, country_code)
SELECT id, full_name, country, UPPER(SUBSTR(country, 1, 2))
FROM employees_src;

SELECT * FROM employees_dst;

Изменение поведения внешнего ключа

В SQLite нельзя «переключить» ON DELETE/ON UPDATE у существующего внешнего ключа. Чтобы, например, включить каскадное удаление для столбца books.author_id, придётся пересоздать таблицу. Шаги похожи на предыдущий пример:

1. сохранить данные и удалить таблицу books;
2. создать структуру заново с нужными параметрами внешнего ключа;
3. вернуть данные в обновлённую таблицу.

-- Посмотрим текущее поведение внешнего ключа
PRAGMA foreign_key_list('books');

-- 1. Сохраняем данные и удаляем исходную таблицу
CREATE TABLE books_backup AS SELECT * FROM books;
DROP TABLE books;

-- 2. Создаём таблицу заново, добавляя ON DELETE CASCADE / ON UPDATE CASCADE
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  publication_year INTEGER,
  isbn TEXT UNIQUE,
  genre TEXT,
  author_id INTEGER NOT NULL,
  pages INTEGER,
  available_copies INTEGER DEFAULT 1,
  FOREIGN KEY (author_id) REFERENCES authors(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

-- 3. Возвращаем данные
INSERT INTO books SELECT * FROM books_backup;
DROP TABLE books_backup;

-- Проверяем, что поведение обновилось
PRAGMA foreign_key_list('books');

Самопроверка

Практические задания

Задание 1. Добавление нового столбца

-- Проверим текущую структуру таблицы
PRAGMA table_info(books);

-- Ваш код можете писать тут
-- CODE --

-- Проверим структуру таблицы после изменений
PRAGMA table_info(books);

ALTER TABLE books ADD COLUMN language TEXT DEFAULT 'ru';

Задание 2. Переименование столбца

-- Проверим текущую структуру таблицы
PRAGMA table_info(books);

-- Ваш код можете писать тут
-- CODE --

-- Проверим структуру таблицы после изменений
PRAGMA table_info(books);

ALTER TABLE books RENAME COLUMN pages TO page_count;

Задание 3. Добавление столбца с ограничениями

-- Проверим текущую структуру таблицы
.schema authors

-- Ваш код можете писать тут
-- CODE --

-- Проверим структуру таблицы после изменений
.schema authors

ALTER TABLE authors ADD COLUMN awards_count INTEGER DEFAULT 0 CHECK (awards_count >= 0);

Задание 4. Переименование таблицы

-- Проверим список таблиц до изменений
.tables

-- Убедимся, что книги ссылаются на authors
PRAGMA foreign_key_list('books');

-- Ваш код можете писать тут
-- CODE --

-- Проверим список таблиц после изменений
.tables

-- Убедимся, что внешний ключ теперь указывает на writers
PRAGMA foreign_key_list('books');

ALTER TABLE authors RENAME TO writers;

Задание 5. Удаление столбца

-- Проверим структуру таблицы до изменений
PRAGMA table_info(books);

-- Ваш код можете писать тут
-- CODE --

-- Проверим, что столбца больше нет
PRAGMA table_info(books);

ALTER TABLE books DROP COLUMN pages;

Задание 6. Пересоздание таблицы с новым типом столбца

-- Зафиксируем текущую структуру
PRAGMA table_info(books);

-- Ваш код можете писать тут
-- CODE --

-- Добавим пометку к одной книге
INSERT INTO books (id, title, publication_year, isbn, genre, author_id, pages, stock_note)
VALUES (20, 'Новая поставка', 2024, '978-5-17-012345-6', 'Современная проза', 3, 320, 'ожидаем поставку');

-- Проверим обновлённую схему
PRAGMA table_info(books);
SELECT title, stock_note FROM books;

CREATE TABLE books_new (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  publication_year INTEGER,
  isbn TEXT UNIQUE,
  genre TEXT,
  author_id INTEGER NOT NULL,
  pages INTEGER,
  stock_note TEXT,
  FOREIGN KEY (author_id) REFERENCES authors(id)
);

INSERT INTO books_new SELECT
  id, title, publication_year, isbn, genre,
  author_id, pages, CAST(available_copies AS TEXT)
FROM books;

DROP TABLE books;
ALTER TABLE books_new RENAME TO books;

Задание 7. Изменение поведения внешнего ключа

-- Посмотрим текущее поведение
PRAGMA foreign_key_list('books');

-- Ваш код можете писать тут
-- CODE --

-- Проверим, что каскад добавлен
PRAGMA foreign_key_list('books');

-- Добавьте временного автора и книгу, чтобы проверить ON DELETE SET NULL
-- CODE (INSERT INTO authors ...)
-- CODE (INSERT INTO books ...)

-- Удалите автора и убедитесь, что ссылка обнулилась
-- CODE (DELETE FROM authors ...)
SELECT id, title, author_id FROM books WHERE id = <id_вашей_книги>;

CREATE TABLE books_backup AS SELECT * FROM books;
DROP TABLE books;

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  publication_year INTEGER,
  isbn TEXT UNIQUE,
  genre TEXT,
  author_id INTEGER,
  pages INTEGER,
  available_copies INTEGER DEFAULT 1,
  FOREIGN KEY (author_id) REFERENCES authors(id)
    ON DELETE SET NULL
    ON UPDATE CASCADE
);

INSERT INTO books SELECT * FROM books_backup;
DROP TABLE books_backup;

-- Проверим поведение: создадим временного автора и книгу
INSERT INTO authors (id, first_name, last_name, birth_year)
VALUES (99, 'Тестовый', 'Автор', 1900);

INSERT INTO books (id, title, publication_year, isbn, genre, author_id, pages, available_copies)
VALUES (99, 'Тестовая книга', 2023, '978-5-17-012346-3', 'Эксперимент', 99, 200, 1);

-- Удаляем автора и смотрим, как `author_id` обнуляется
DELETE FROM authors WHERE id = 99;
SELECT id, title, author_id FROM books WHERE id = 99;

-- Убираем тестовую запись, чтобы не засорять данные
DELETE FROM books WHERE id = 99;