SQL Server Add a column with IDENTITY to an existing temporal table with data

yhived7q 于 2023-03-22 发布在其他

关注(0)|答案(1)|浏览(170)

I have a temporal table with data, and I need to add a column with IDENTITY to it.

But when I try to add, I get an error like it:
Schema change of table with system versions failed, IDENTITY column specified in history table 'TEMP.dbo.tArticleHistory'. Try removing all IDENTITY column references and try again.

Is it possible to add an autoincrement field?

CREATE TABLE dbo.tArticle(
    articleId INTEGER NOT NULL,
    articleName VARCHAR(50),
    beginDtm DATETIME2(3) GENERATED ALWAYS AS ROW START NOT NULL,
    endDtm DATETIME2(3) GENERATED ALWAYS AS ROW END NOT NULL,
    PERIOD FOR SYSTEM_TIME (beginDtm,endDtm),
    CONSTRAINT pkArticle PRIMARY KEY CLUSTERED(articleId ASC)
);

CREATE TABLE dbo.tArticleHistory(
    articleId INTEGER NOT NULL,
    articleName VARCHAR(50),
    beginDtm DATETIME2(3) NOT NULL,
    endDtm DATETIME2(3) NOT NULL
);

CREATE CLUSTERED INDEX idxArticleHistory
ON dbo.tArticleHistory (
     articleId ASC
) WITH (DATA_COMPRESSION = PAGE) ON [PRIMARY]
GO

ALTER TABLE dbo.tArticle SET (
SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.tArticleHistory));
GO

INSERT INTO dbo.tArticle(
    articleId,
    articleName
)
VALUES (1,'article 1'),(2,'article 2'),(3,'article 3'),(4,'article 4')

DELETE FROM dbo.tArticle WHERE articleId IN (1,2);

ALTER TABLE dbo.tArticle ADD  operationId INTEGER IDENTITY(1,1) NOT NULL;

sql-server

来源：https://stackoverflow.com/questions/75790466/add-a-column-with-identity-to-an-existing-temporal-table-with-data

1条答案

按热度按时间

3bygqnnd1#

As the error states, you can't add a IDENTITY column after you have created the table to a table which has system versioning enabled.

As a result what you will need to do is turn versioning off and add the IDENTITY column to the table. You'll then need to add the same column to the history table, however, as you can't add it as a NULL able value (as that will cause an error later), you'll need to define a default value; I use 0 to denote that the value was not known previously. After the column is added I DROP the constraint, as it isn't needed anymore.

Then you can switch system versioning back on:

SET XACT_ABORT ON;

BEGIN TRANSACTION
ALTER TABLE dbo.tArticle SET (SYSTEM_VERSIONING = OFF);

ALTER TABLE dbo.tArticle ADD IdentityColumn int IDENTITY(1,1);

ALTER TABLE dbo.tArticleHistory ADD IdentityColumn int NOT NULL CONSTRAINT DF_IdentityColumn_temp DEFAULT 0;

ALTER TABLE dbo.tArticleHistory DROP CONSTRAINT DF_IdentityColumn_temp;

ALTER TABLE dbo.tArticle SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.tArticleHistory));
COMMIT;

db<>fiddle

The documentation actually also gives a similar example in it's Important Remarks section.

赞(0）回复(0）举报 2023-03-22

我来回答

SQL Server Add a column with IDENTITY to an existing temporal table with data

1条答案

相关问题

热门标签

最新问答