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.

Balf

You could try an ALTER TABLE ... WITH NOCHECK ADD CONSTRAINT CK_MyInt_NotNull CHECK (MyInt IS NOT NULL) to add the constraint for new rows after the fact without checking existing rows. (Disclaimer: not tested.) This is a little iffy since it's not literally the same thing as marking a column NOT NULL and it will break on reimporting data, but then that's also exactly why your scenario fails: versioned tables version data, but not the structure. Any successful structure changes are propagated to the history table instead, which, even when it works, is not exactly faithful to history. – Quincuncial

@JeroenMostert In my pre-temporal audit system's I'd have left the audit table as nullable and left it up to the main table to enforce the constraint. Was hoping there might have been a way to do the same here, as it seems all other workarounds are less than ideal. Thanks for the additional idea, hadn't thought of it myself. – Balf

Temporal tables are really cool as long as your use case matches their implementation exactly, and really quite frustrating if they slightly diverge. Here's to hoping future versions (no pun intended) give us history querying goodness without the extremely close ties to the engine's implementation. – Quincuncial

Of cause you cannot do that when you already has null values. Update them or drop the table. – Ciccia

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

Vetiver

I'm well beyond the requirement at this point, but thanks for answering. I hadn't thought of that when the problem was on my desk. – Balf

Annoyingly learn.microsoft.com/en-us/sql/relational-databases/tables/… says "If you add a non-nullable column or alter existing column to become non-nullable, you must specify the default value for existing rows. The system will generate an additional default with the same value and apply it to the history table." however I don't think it's possible to set a default at the same time as changing a col to NOT NULL and adding DEFAULT constraint first doesn't work as history data is unchanged. – Shanelleshaner

So it appears this solution from @paulvzyl is the only way – Shanelleshaner

Raised as a docs issue here github.com/MicrosoftDocs/sql-docs/issues/7499 – Shanelleshaner

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;

Ovotestis

Answer 3

Whilst you can change the schema of temporal tables there are certain actions that you cannot do by a direct ALTER whilst a table is system versioned. One of those is to change a Nullable column to be NOT NULL.

See Important Remarks - Changing the schema of a system-versioned temporal table

In this scenario the only thing you can do is to turn off system versioning using the following:

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

This leaves you with 2 separate tables - the table itself and it's history table both as separate objects. You can now make your schema updates to BOTH tables (they have to be schema aligned) and then you can turn system versioning back on:

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

Neron