SQL-Ex blog

Зачем заменять столбец identity

Столбец identity может оказаться недостаточно гибким, чтобы удовлетворить всем бизнес-требованиям приложения в плане последовательности значений идентификаторов. В этом случае объект порядковых номеров может оказаться более гибким. Вот несколько причин, по которым вы можете захотеть поменять столбец identity на номер последовательности:

• Необходимо знать порядковый номер до вставки записи.


• Необходимо разнести порядковые номера по нескольким таблицам.


• Необходимо периодически проходить цикл по последовательности чисел.


• Необходимо получать несколько последовательных номеров одновременно без пропусков.


• Необходимо иметь возможность изменять спецификацию для числа последовательности время от времени.


• Необходимо сохранять порядковый номер как значение типа, отличного от числового.


• Необходимо, чтобы столбец порядковых номеров допускал NULL-значения.

Преобразование столбца identity в порядковый номер

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

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

Замена столбца identity в существующей таблице при использовании столбца с заполнением порядковыми номерами выполняется в несколько этапов. Для демонстрации примера создайте сначала пару тестовых таблиц.

Первая таблица называется Sample и может быть создана с помощью скрипта в листинге 1.

Листинг 1: код для создания таблицы Sample.

USE tempdb;

GO

CREATE TABLE Sample (ID int identity(1,1) NOT NULL, 

    SampleName varchar(30) NOT NULL,

    CONSTRAINT PK_Sample_ID PRIMARY KEY CLUSTERED(ID ASC));

INSERT INTO Sample(SampleName) VALUES ('First'),

                          ('Second'),

                          ('Third');

SELECT * FROM Sample;

Выполнение скрипта из листинга 1 даст следующий результат.

Строки, сгенерированные в таблице Sample


Таблица Sample имеет столбец identity, который будет изменен на использование порядкового номера.

При замене столбца identity столбцом, заполненным порядковым номером, следует быть внимательным, чтобы не испортить таблицы с внешним ключом, ссылающимся на столбец identity модифицируемой таблицы. Поэтому этот пример содержит вторую таблицу с именем Sample2, создаваемую в листинге 2. Sample2 моделирует таблицу с внешним ключом, ссылающимся на первичный ключ, которым служит столбец ID в первой таблице.

Листинг 2: создание таблицы Sample2

USE tempdb;

GO

CREATE TABLE Sample2 (ID int,

CONSTRAINT FK_Sample2_ID FOREIGN KEY (ID) REFERENCES Sample(ID));

Шаг 1: создание столбца порядкового номера

Первый шаг замены столбца identity на порядковый номер - создание нового столбца в таблице Sample для хранения порядкового номера. В этом примере столбец порядкового номера имеет тот же тип данных, что и исходный столбец identity - INT. Код в листинге используется для добавления нового столбца порядкового номера в таблицу Sample.

Листинг 3: Добавление столбца порядкового номера

USE tempdb;

GO

ALTER TABLE Sample ADD SequenceNumber int NULL;

GO

Шаг 2: обновление столбца SequenceNumber

После добавления нового столбца порядкового номера в таблицу Sample следующим шагом будет обновление нового столбца соответствующим значением для всех существующих записей. Новый столбец SequenceNumber будет заполнен теми же значениями, что и в столбце ID. Для обновления столбца SequenceNumber выполните код в листинге 4.

Листинг4: обновление столбца SequenceNumber

USE tempdb;

GO

UPDATE Sample

   Set SequenceNumber = ID;

SELECT * FROM Sample;

Вывод кода из листинга 4 приведен на рисунке ниже.

Таблица Sample

Шаг3: обнаружение и удаление ограничений внешнего ключа

Столбец identity таблицы Sample имеет связанный с ним первичный ключ с именем PK_Sample_ID. Этот первичный ключ необходимо убрать со столбца ID, а затем перевести его на новый столбец SequenceNumber. Прежде чем это может быть сделано, любые внешние ключи, которые ссылаются на этот первичный ключ должны быть удалены. Для нахождения внешних ключей, которые ссылаются на первичный ключ PK_Sample_ID, выполните код в листинге 5.

Нахождение всех внешних ключей

USE tempdb;

GO

SELECT FK.TABLE_NAME as ForeignKeyTable,

      C.CONSTRAINT_NAME as Constraint_Name 

FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C

    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK 

    ON C.CONSTRAINT_NAME =Fk.CONSTRAINT_NAME

    INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK 

    ON C.UNIQUE_CONSTRAINT_NAME=PK.CONSTRAINT_NAME 

    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU 

    ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME 

    INNER JOIN (

        SELECT i1.TABLE_NAME, i2.COLUMN_NAME

        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1

            INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 

     ON i1.CONSTRAINT_NAME =i2.CONSTRAINT_NAME 

        WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'

    ) PT ON PT.TABLE_NAME = PK.TABLE_NAME

WHERE PK.TABLE_NAME = 'Sample' and PT.COLUMN_NAME = 'ID';

После выполнения кода из листинга 5 будет получен следующий результат.



Чтобы удалить только найденный внешний ключ, можно выполнить код в листинге 6.

Листинг 6: удаление внешнего ключа

USE tempdb;

GO

ALTER TABLE Sample2 DROP CONSTRAINT FK_Sample2_ID;

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

Шаг 4: удаление первичного ключа на столбце identity

Когда все внешние ключи удалены, можно удалить первичный ключ таблицы Sample с помощью скрипта в листинге 7.

Листинг 7: удаление первичного ключа

USE tempdb;

GO

ALTER TABLE Sample

DROP CONSTRAINT PK_Sample_ID;

GO

Шаг 5: удаление столбца identity

Когда удален первичный ключ, можно удалить столбец identity с помощью кода в листинге 8.

Листинг 8: удаление столбца identity

USE tempdb;

GO

ALTER TABLE Sample

DROP COLUMN ID ;

GO

Шаг 6: переименование столбца SequenceNumber

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

Листинг 9: переименование столбца SequenceNumber

USE tempdb;

GO

EXEC sp_rename 'Sample.SequenceNumber',

'ID', 'COLUMN';

GO

Шаг 7: добавление ограничения NOT NULL и первичного ключа

Для столбца Identity было задано ограничение NOT NULL. Поэтому чтобы сделать для нового столбца порядкового номера зеркальные свойства исходного столбца Identity, следует добавить ограничение NOT NULL для столбца порядкового номера. Скрипт в листинге 10 изменяет столбец ID, чтобы он не допускал NULL, и добавляет новое ограничение первичного ключа для замены первичного ключа, удаленного на шаге 4.

Листинг 10: Добавление ограничения NOT NULL и первичного ключа

USE tempdb;

GO

ALTER TABLE Sample ALTER COLUMN [ID] int NOT NULL;

GO

ALTER TABLE Sample

   ADD CONSTRAINT PK_Sample_ID PRIMARY KEY CLUSTERED (ID ASC) ;

GO

Шаг 8: создание объекта порядкового номера

Поскольку таблица Sample уже содержит данные, нужно узнать максимальное значение в новом столбце ID. Это значение будет использовано для установки начального значения нового объекта порядкового номера. Начальное значение объекта порядкового номера будет равным максимальному значению столбца ID в таблице Sample плюс значение приращения для объекта последовательности (которое в данном примере будет равно 1, то же самое, что и значение приращения исходного столбца identity). Можно использовать код динамического TSQL в листинге 11 для определения наибольшего значения в столбце ID и создания нового объекта порядкового номера с корректным значением START.

Листинг 11: создание объекта порядкового номера

USE tempdb;

GO

DECLARE @NewStartValue int;

DECLARE @IncrementValue int = 1;

DECLARE @CMD nvarchar(1000);

SELECT @NewStartValue = MAX(ID) + @IncrementValue FROM Sample;

SET @CMD = 'CREATE SEQUENCE Sample_SequenceNumber AS INT START WITH ' +

           RTRIM(CAST(@NewStartValue as CHAR)) + 

           ' INCREMENT BY ' + RTRIM(CAST(@IncrementValue AS CHAR));

EXEC sp_executesql @CMD

GO

При использовании динамического SQL следует убедиться, что он не вызывает проблем с SQL-инъекцией. Поэтому перед выполнением любого динамического SQL убедитесь, что вы не открываете дверь потенциальным проблемам SQL-инъекции.

Шаг 9: установка значения по умолчанию для нового столбца ID

Когда новые строки добавляются в поле столбца identity, значение столбца identity автоматически заполняется следующим значением identity по умолчанию. Чтобы новое значение столбца ID автоматически заполнялось значением порядкового номера, необходимо добавить ограничение default для нового столбца ID. Это ограничение может быть добавлено с помощью кода, приведенного в листинге 12.

Лстинг 12: установка значения по умолчанию для столбца ID

USE tempdb;

GO

ALTER TABLE Sample

ADD CONSTRAINT ID_Default 

   DEFAULT (NEXT VALUE FOR Sample_SequenceNumber) FOR ID;

GO

Шаг 10: восстановление внешних ключей

На шаге 3 было удалено одно ограничение внешнего ключа, которое ссылалось на первичный ключ таблицы Sample. Этот шаг возвращает этот удаленный внешнй ключ, используя код в листинге 13.

Листинг 13: восстановление внешнего ключа

USE tempdb;

GO

ALTER TABLE Sample2

   ADD CONSTRAINT FK_Sample2_ID FOREIGN KEY (ID) REFERENCES Sample(ID);

Тестирование вставки новых строк

При замене столбца identity столбцом, заполняемым объектом порядкового номера, все что осталось - это протестировать новое определение схемы. Тестирование проверяет, что новый столбец ID заполняется следующим порядковым номером при всяком добавлении новой строки. Тестирование можно провести, выполнив код из листинга 14.

Листинг 14: вставка трех новых строк в таблицу Sample

USE tempdb;

GO 

INSERT INTO Sample (SampleName)    

   VALUES ('Fourth'),

          ('Fifth'),

          ('Sixth');

SELECT * FROM Sample;

При исполнении кода в листинге 14, получаются следующе результаты:

Вывод кода листинга 14


Видно, что последние три вставленные строки получили следующие три последовательные порядковые номера (4, 5 и 6). Также обратите внимание, что позиция столбца ID больше не является первой, как это было для исходного столбца ID.

Вариант 2: замена столбца identity при использовании операции SWITCH

Другим вариантом замены столбца identity является использование операции ALTER TABLE SWITCH. Этот метод часто используется совместно с секционированием таблиц, которое выходит за рамки этой статьи, но полезно в этом сценарии. Оператор SWITCH не перемещает данные. Вместо этого он переключает серкцию между источником и целевыми таблицами. Этот процесс упрощает перенос, но должны быть выполнены определенные требования. Ниже перечислены эти требования, приведенные в документации Microsoft:

• Обе таблицы должны существовать к моменту применения операции SWITCH. Таблица, из которой будет перемещаться секция (исходная таблица) и таблица, в которую будет перещаться секция (целевая таблица) должны существовать в базе данных до выполнения операции переключения.


• Принимающая секция должна существовать и быть пустой. Добавляете ли вы таблицу как секцию в уже существующую секционированную таблицу, или перемещаете секцию из одной секционированной таблицы в другую, секция, которая принимает новую секцию, должна существовать и она должна быть пустой.


• Принимающая несекционированная таблица должна существовать и быть пустой. Если вы переназначаете секцию для формирования одной несекционированной таблицы, таблица, которая получает новую секцию, должна существовать и быть пустой несекционированной таблицей.


• Секции должны быть определены на одном и том же столбце. Если вы переключаете секцию из одной секционированной таблицы в другую, обе таблицы должны быть секционированы по одному и тому же столбцу.


• Источник и целевая таблицы должны находиться в одной и той же файловой группе. Источник и целевая таблица в операторе ALTER TABLE…SWITCH должны находиться в одной и той же файловой группе, и их столбцы с большими данными должны храниться в одной и той же файловой группе. Любые соответствующие индексы, секции индексов или секции индексированных представлений должны находиться в одной и той же файловой группе. Однако эта файловая группа может отличаться от группы, содержащей соответствующие таблицы или другие соответствующие индексы.

Очистка и воссоздание артефактов из Варианта 1

Перед изучением использования операции SWITCH для замены столбца identity на столбец, заполняемый порядковыми номерами, почистим базу данных от того, что было создано в первом варианте, и воссоздадим таблицы примера и объект последовательности. Для выполнения чистки и воссоздания объектов выполните код в листнге 15.

Листинг 15: чистка и воссоздание таблиц примера

USE tempdb;

GO

DROP TABLE Sample2;

DROP TABLE Sample;

DROP SEQUENCE Sample_SequenceNumber;

GO

CREATE TABLE Sample (ID int identity(1,1) NOT NULL, 

      SampleName varchar(30) NOT NULL,

      CONSTRAINT PK_Sample_ID PRIMARY KEY CLUSTERED(ID ASC));

INSERT INTO Sample(SampleName) 

   VALUES ('First'),

          ('Second'),

          ('Third');

CREATE TABLE Sample2 (ID int,

CONSTRAINT FK_Sample2_ID FOREIGN KEY (ID) REFERENCES Sample(ID));

GO

USE tempdb;

GO

DECLARE @NewStartValue int;

DECLARE @IncrementValue int = 1;

DECLARE @CMD nvarchar(1000);

SELECT @NewStartValue = MAX(ID) + @IncrementValue FROM Sample;

SET @CMD = 'CREATE SEQUENCE Sample_SequenceNumber AS INT START WITH ' +

           RTRIM(CAST(@NewStartValue as CHAR)) + 

           ' INCREMENT BY ' + RTRIM(CAST(@IncrementValue AS CHAR));

EXEC sp_executesql @CMD

GO

Шаг 1: создание новой таблицы, которая использует порядковый номер

Для использования операции SWITCH нужно создать целевую таблицу. Используйте код в листинге 16 для создания новой таблицы, которая использует порядковый номер для заполнения столбца ID вместо спецификации identity.

Листинг 16: создание новой таблицы

USE tempdb;

GO

CREATE TABLE Sample_New (

   ID int NOT NULL DEFAULT NEXT VALUE FOR Sample_SequenceNumber, 

   SampleName varchar(30) NOT NULL,

   CONSTRAINT PK_Sample_New_ID PRIMARY KEY CLUSTERED(ID ASC)

 );

 GO 

Шаг 2: Удаление ограничений внешнего ключа

Операция SWITCH завершится неудачно, если на первичный ключ в таблице, которая переключается, ссылаются какие-либо внешние ключи. Поэтому все внешние ключи на таблицу Sample необходимо сначала удалить. Таблица Sample2 имеет внешний ключ, который ссылается на первичный ключ, который будет удален при выполнении кода в листинге 6.

Переключение таблиц

После удаления всех ограничений внешнего ключа можно выполнить операцию SWITCH. Код в листинге 17 выполнит операцию SWITCH.

Листинг 17: переключение таблиц

USE tempdb;

GO

ALTER TABLE Sample SWITCH TO Sample_New;

SELECT * FROM Sample_New;

При выполнении переключения переключается секция между источником (Sample) и целевой (Sample_New) таблицей. Меняются только метаданные; сами данные не перемещаются. Вывод оператора SELECT в листинге 17 приводится ниже. Тем самым проверяется, что строки из таблицы Sample теперь связаны с таблицей Sample_New.

Строки в таблице Sample_New

Удаление старой таблицы и переименование новой

После выполнения операции SWITCH старая таблица Sample не содержит никаких строк. Поэтому она может быть удалена, а новая таблица - переименована. Удаление и переименование может быть выполнено с помощью кода в листинге 18.

Листинг 18: удаление старой и переименование новой

USE tempdb;

GO

DROP TABLE Sample

EXEC sp_rename N'Sample_New',N'Sample';

Шаг 5: Воссоздание удаленных ограничений внешнего ключа

Последний шаг переноса - это воссоздание внешнего ключа, удаленного ранее. Этот ключ можно создать заново с помощью кода в листинге 13.

Проверка переноса

Для проверки успешности переноса вы можете выполнть код в листинге 14. При выполнении скрипта он должен дать те же самые результаты, что и в предыдущем выводе скрипта из листинга 14 за одним исключением. Столбец ID теперь имеет порядковую позицию 1. Я оставлю вам выполнение это кода и проверку, что операция SWITCH была успешной при преобразовании столбца identity в столбец, заполняемого объектом порядкового номера.

Защита порядковых номеров от обновления

Значения столбца identity нельзя обновить, в то время как столбцы с порядковыми номерами могут обновляться. Если вы хотите сделать так, чтобы порядковые номера нельзя было обновить, то вам потребуется создать триггер AFTER UPDATE. Для проверки, что имеющаяся таблица Sample допускает обновление столбца ID, выполните код в листинге 19.

Листинг 19: обновление столбца ID

USE tempdb;

GO 

BEGIN TRAN

UPDATE Sample

   SET ID = ID + 100;

SELECT * FROM Sample;

ROLLBACK TRAN;

GO

Код успешно выполняется, что видно в представленных ниже результатах.

Вывод кода из листинга 17


Из результатов видно, что все значения ID обновились. Код в листинге 19 содержит операторы BEGIN TRAN и ROLLBACK TRAN для отката этих обновлений для следующего теста.

Чтобы сделать столбец ID в таблице Sample необновляемым, необходимо создать триггер AFTER UPDATE, приведенный в листинге 20.

Листинг 20: триггер AFTER UPDATE

USE tempdb

GO

CREATE TRIGGER trg_UpdateSample

ON Sample

AFTER UPDATE

AS

BEGIN

   SET NOCOUNT ON;

   DECLARE @OriginalID int

   DECLARE @UpdatedID int

   SELECT @OriginalID =[ID] FROM deleted

   SELECT @UpdatedID =[ID] FROM inserted

   IF @OriginalID <> @UpdatedID

   BEGIN

      RAISERROR('Failed: Update performed on ID column', 16, 1);

      ROLLBACK TRANSACTION

   END

END

GO

Чтобы проверить работу триггера, выполните код в листинге 21.

Листинг 21: Тестирование триггера, запрещающего обновление столбца ID

USE tempdb;

GO

UPDATE Sample

	SET ID = ID + 100

WHERE ID = 1;

GO

При выполнении кода из листинга 21 возникает следующая ошибка:

Ошибка при выполнении кода из листинга 21


Триггер AFTER UPDATE trg_UpdateSample не позволил коду в листинге 21 обновить столбец ID.

Замена в столбце identity на порядковый номер

Может возникнуть ситуация, когда вам потребуется заменить столбец identity порядковым номером. В этой статье представлены пара примеров того, как выполнить эту замену. Если вы планируете заменить столбец identity столбцом заполняемым последовательными значениями, помните, что столбцы порядкового номера могут обновляться. Для запрета этого обновления потребуется определить триггер AFTER UPDATE.