logo

Изменение колонки в SQL: делаем её идентификационной

Быстрый ответ

Преобразование существующего столбца в SQL Server в столбец с идентификатором включает выполнение нескольких действий:

  1. Создание копии таблицы без данных при помощи SELECT INTO с условием WHERE 1=0.
  2. Добавление в копию дополнительного столбца IDENTITY.
  3. Перенос данных с помощью SET IDENTITY_INSERT, синхронизируя при этом идентификаторы.
  4. Удаление исходной таблицы и переименование копии.

Вот пример соответствующего кода:

SQL
Скопировать код
BEGIN TRANSACTION;

-- Сначала создаем точную копию структуры таблицы
SELECT * INTO NewTable FROM OldTable WHERE 1=0;
ALTER TABLE NewTable ADD NewID INT IDENTITY(1,1);

-- Затем аккуратно переносим данные, сохраняя идентификаторы.
SET IDENTITY_INSERT NewTable ON;
INSERT INTO NewTable (NewID, ...) SELECT OldID, ... FROM OldTable;
SET IDENTITY_INSERT NewTable OFF;

-- Удаляем оригинальную таблицу и заменяем её новой версией.
DROP TABLE OldTable;
EXEC sp_rename 'NewTable', 'OldTable';

COMMIT TRANSACTION;

Стратегия добавления идентификатора

Особенности добавления идентификатора

Добавление столбца с идентификатором требует учесть следующее:

  • Существующие данные: Новый IDENTITY будет незаполненным столбцом.
  • Непрерывность: Чтобы сохранить последовательность ID, используйте DBCC CHECKIDENT.
  • Безопасность: Применение IF EXISTS помогает избежать ошибочных удалений.
  • Обновление связей: Не забывайте корректировать внешние ключи, индексы и другие зависимости.

Добавление идентификатора в большие таблицы

Для больших таблиц более эффективным будет метод ALTER TABLE...SWITCH, минимизирующий время простоя:

  • Доступность: Таблица остается доступной во время всего процесса.
  • Требовательность: Этот подход требует внимательности и аккуратности.

Пример применения операции переключения:

SQL
Скопировать код
BEGIN TRANSACTION;

-- "Обмен" таблицами делает процесс быстрым и эффективным.
ALTER TABLE OldTable SWITCH TO NewTable;
ALTER TABLE NewTable ADD NewID INT IDENTITY(1,1);

-- Включаем режим вставки идентификаторов.
SET IDENTITY_INSERT NewTable ON;
INSERT INTO NewTable (NewID, ...) SELECT OldID, ... FROM OldTable;
SET IDENTITY_INSERT NewTable OFF;

-- Подчищаем следы – удаляем исходную таблицу и переименовываем новую.
DROP TABLE OldTable;
EXEC sp_rename 'NewTable', 'OldTable';

COMMIT TRANSACTION;

Использование последовательностей вместо идентификатора

В SQL Server объект SEQUENCE может быть предпочтительнее для автоинкремента ключей:

  • Гибкость: Одна последовательность может быть использована для нескольких таблиц.
  • Предотвращение конфликтов: Последовательности помогают избежать проблем с автоинкрементом в процессе вставки.

Реализация последовательности:

SQL
Скопировать код
CREATE SEQUENCE Seq_As_Identity
    START WITH 1
    INCREMENT BY 1;

ALTER TABLE MyTable
    ADD NewIDCol INT DEFAULT NEXT VALUE FOR Seq_As_Identity
    CONSTRAINT PK_MyTable PRIMARY KEY CLUSTERED;

-- Удаляем старый ID, он нам больше не пригодится.
ALTER TABLE MyTable DROP COLUMN OldIDCol;

Визуализация 🚂

Markdown
Скопировать код
До: [🚃, 🚃, 🚃] 
// Строки без идентификаторов ждали своего часа!
SQL
Скопировать код
ALTER TABLE Train ADD COLUMN Car_ID INT IDENTITY(1,1);
Markdown
Скопировать код
После: [🚃1, 🚃2, 🚃3]
// Теперь каждый вагон обзавелся своим уникальным номером!

Работа с активными таблицами

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

  • Предотвращать конфликты: Назначение высокого начального ID предотвращает дублирование.
  • Обеспечивать последовательность данных: Гарантируйте непрерывность вставок в процессе миграции.

Создание скрипта добавления идентификатора

SQL Management Studio предоставляет возможность автоматической генерации скрипта изменений:

  • Откройте конструктор таблиц и внесите нужные изменения.
  • Вместо сохранения выберите Generate Change Script.

Финальная очистка после миграции

Завершая переход, необходимо:

  • Освободить пространство: Удалить старые элементы.
  • Обеспечить консистенцию имён: Использовать sp_rename для переименования.

Полезные материалы

  1. ALTER TABLE (Transact-SQL) – SQL Server | Microsoft Learn — полное описание команды ALTER TABLE.
  2. IDENTITY (Property) (Transact-SQL) – SQL Server | Microsoft Learn — руководство от Microsoft по свойству IDENTITY.
  3. SQL Server: ALTER TABLE Statement — содержательная статья об использовании команды ALTER TABLE.
  4. Database Journal: Working with Identity Columns — полезная статья об работе со столбцами идентификатора.