Измените поле автоинкремента первичного ключа с SMALLINT на INT в SQL Server

Question

Каков наилучший способ (низкий уровень воздействия / низкий риск) изменить поле первичного ключа с SMALLINT на INT? Поле сконфигурировано для использования "Приращения идентичности" для автоматического приращения.

Я начинаю со следующего SQL:

ALTER TABLE category_types ALTER COLUMN id INT NOT NULL;

Тем не менее, он генерирует следующую ошибку:

Ошибка ALTER TABLE ALTER COLUMN id, поскольку один или несколько объектов обращаются к этому столбцу.

Что еще требуется? Нужно ли бросать ключи, а затем воссоздавать их? Повлияет ли это на автоинкремент?

Примечание: таблица не имеет слишком много строк, поэтому производительность решения не критична.

Answer 1

Я понимаю, что это старый пост, но на тот случай, если кто-то наткнется на него: jciberta дал ответ с небольшой ошибкой. это должно читать:

- Изменить от smallint до int

SET IDENTITY_INSERT category_types ON

изменить таблицу category_types drop CONSTRAINT CategoryTypes

изменить таблицу category_types изменить идентификатор столбца int

изменить таблицу category_types add CONSTRAINT CategoryTypesPK ПЕРВИЧНЫЙ КЛЮЧ (id)

SET IDENTITY_INSERT category_types OFF

Answer 2

Это вывод из студии управления сервером sql, но он должен помочь. Это включает создание временной таблицы и затем копирование существующих данных в новую таблицу. Удаление исходной таблицы и переименование временной таблицы. Если бы таблица была пустой, она бы этого не сделала и могла просто изменить исходную таблицу. SET IDENTITY_INSERT [table] ON/OFF позволяет установить столбец идентификации при вставке.

/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_category_types
    (
    id int NOT NULL IDENTITY (1, 1),
    )  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_category_types SET (LOCK_ESCALATION = TABLE)
GO
SET IDENTITY_INSERT dbo.Tmp_category_types ON
GO
IF EXISTS(SELECT * FROM dbo.category_types)
     EXEC('INSERT INTO dbo.Tmp_category_types (id, x)
        SELECT CONVERT(int, id), x FROM dbo.category_types WITH (HOLDLOCK TABLOCKX)')
GO
SET IDENTITY_INSERT dbo.Tmp_category_types OFF
GO
DROP TABLE dbo.category_types
GO
EXECUTE sp_rename N'dbo.Tmp_category_types', N'category_types', 'OBJECT' 
GO
ALTER TABLE dbo.category_types ADD CONSTRAINT
    PK_category_types PRIMARY KEY CLUSTERED 
    (
    id
    ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO
COMMIT

Answer 3

Помимо отключения IDENTITY_INSERT, вы должны отменить связь (первичный ключ, внешние ключи) перед изменением типов данных. После этого вы должны снова связать их.

Например:

-- Change from smallint to int
SET IDENTITY_INSERT category_types OFF
alter table category_types drop CONSTRAINT CategoryTypesPK
alter table category_types alter column id int
alter table category_types add CONSTRAINT CategoryTypesPK PRIMARY KEY (id)
SET IDENTITY_INSERT category_types ON