Alter Column to Not Null where System Versioned column was nullable

Question

I'm using SQL Server and system-versioned (temporal) tables. In my main table, I have an INT column that's currently allowing NULLs. I want to update this to not allow nulls, but the system/history copy of the table allows nulls.

I run this statement:

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

And I get this error:

Cannot insert the value NULL into column 'MyInt', table 'mydb.dbo.MyTable_History'; column does not allow nulls. UPDATE fails.

I had created the system versioned table using this script:

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

Is there some other way I can make my main table's column non-nullable in this scenario? I suppose I could (maybe) manually update the existing system-versioned null values with an arbitrary garbage value, but it seems like this scenario should be supported with temporal tables.

Answer 1

I also looked at this and it seems you have to update the NULL values in the system version column to some value.

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

Answer 2

I got this issue when I was trying to add a new non-null column. I was originally trying to create the column as nullable, update all the values, and then set it to non-nullable:

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;

But I managed to get around it by using a temporary default constraint instead:

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;