将系统版本控制列为可空的列改为非空。

Question

将系统版本控制列为可空的列改为非空。

12

我正在使用 SQL Server 和系统版本化（时间轴）表。在我的主表中，我有一个允许 NULL 值的 INT 列。我希望将其更新为不允许 NULL 值，但是系统/历史拷贝的表允许 NULL 值。

我运行以下语句：

ALTER TABLE dbo.MyTable 
    ALTER COLUMN MyInt INT NOT NULL;

我遇到了以下错误：

无法将空值插入到“mydb.dbo.MyTable_History”表的“MyInt”列中；该列不允许空值。更新失败。

我使用以下脚本创建了系统版本控制表：

ALTER TABLE dbo.MyTable
    ADD 
        ValidFrom DATETIME2 (2) GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DFMyTable_ValidFrom DEFAULT DATEADD(SECOND, -1, SYSUTCDATETIME()),
        ValidTo DATETIME2 (2) GENERATED ALWAYS AS ROW END HIDDEN CONSTRAINT DFMyTable_ValidTo DEFAULT '9999.12.31 23:59:59.99',
        PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);

ALTER TABLE dbo.MyTable 
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.MyTable_History));
GO

在这种情况下，我是否有其他方法可以使我的主表列成为非空列？我想我可以手动使用任意垃圾值更新现有的系统版本空值，但是似乎应该支持使用时间表解决此场景。

- jleach

你可以尝试使用 ALTER TABLE ... WITH NOCHECK ADD CONSTRAINT CK_MyInt_NotNull CHECK (MyInt IS NOT NULL) 来添加约束条件，以便在事后为新行添加约束条件而无需检查现有行。（免责声明：未经测试。）这有点棘手，因为它并不是字面上将列标记为 NOT NULL，并且在重新导入数据时会出现问题，但这也正是您的情况失败的原因：版本化表对数据进行版本控制，但不对结构进行版本控制。任何成功的结构更改都会传播到历史表中，即使它起作用，也不完全忠实于历史记录。 - Jeroen Mostert

1

@JeroenMostert 在我的预时态审计系统中，我会将审计表保留为可空，并由主表来强制执行约束。希望在这里也有一种类似的方法，因为似乎所有其他解决方法都不太理想。感谢您提供的额外想法，我自己没有想到。 - jleach

2

时间表非常酷，只要您的用例与它们的实现完全匹配，但如果它们稍微偏离，就会变得非常令人沮丧。希望未来版本（无意双关语）能够为我们提供历史查询功能，而不需要与引擎的实现极其紧密地联系在一起。 - Jeroen Mostert

当你已经有空值时，你当然不能这样做。更新它们或删除表格。 - SqlKindaGuy

3个回答

3

在我尝试添加一个新的非空列时，我遇到了这个问题。我最初尝试将该列创建为可空列，更新所有值，然后将其设置为不可空:

ALTER TABLE dbo.MyTable 
    ADD COLUMN MyInt INT NULL;

GO

UPDATE dbo.MyTable
    SET MyInt = 0;

GO

ALTER TABLE dbo.MyTable 
    ALTER COLUMN MyInt INT NOT NULL;

但我通过使用临时默认约束来解决了这个问题：

ALTER TABLE dbo.MyTable 
    ADD COLUMN MyInt INT NOT NULL CONSTRAINT DF_MyTable_MyInt DEFAULT 0;

ALTER TABLE dbo.MyTable
    DROP CONSTRAINT DF_MyTable_MyInt;

- Harry

-1

虽然您可以更改时间表的模式，但在表被系统版本化时，有某些操作是无法通过直接 ALTER 进行的。其中之一是将可空列更改为 NOT NULL。

请参见重要说明-更改系统版本化时间表的模式

在这种情况下，您唯一能做的就是使用以下方法关闭系统版本控制：

ALTER TABLE schema.TableName SET (SYSTEM_VERSIONING = OFF);

这将使你拥有两个单独的表 - 表本身和它的历史表都是单独的对象。现在，你可以对这两个表进行模式更新（它们必须是模式对齐的），然后你可以重新启用系统版本控制：

ALTER TABLE schema.TableName SET (SYSTEM_VERSIONING = ON);

- obaylis

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Paul v Zyl · Accepted Answer

我也看了一下，似乎你需要将系统版本列中的NULL值更新为某个值。

ALTER TABLE dbo.MyTable
    SET (SYSTEM_VERSIONING = OFF)
GO
UPDATE dbo.MyTable_History
    SET MyInt = 0 WHERE MyInt IS NULL --Update to default value
UPDATE dbo.MyTable
    SET MyInt = 0 WHERE MyInt IS NULL --Update to default value
ALTER TABLE dbo.MyTable
    ALTER COLUMN MyInt INT NOT NULL
ALTER TABLE dbo.MyTable_History
    ALTER COLUMN MyInt INT NOT NULL
GO
ALTER TABLE dbo.MyTable 
    SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.MyTable_History));
GO