CODEWITHSUNDEEP
sql-servertemporal-tables
jleach
jleach

Reputation: 7800

Alter Column to Not Null where System Versioned column was nullable

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.

Upvotes: 14

Views: 7560

Answers (3)

obaylis
obaylis

Reputation: 3034

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);

Upvotes: -1

Harry
Harry

Reputation: 389

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;

Upvotes: 4

Paul v Zyl
Paul v Zyl

Reputation: 296

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

Upvotes: 21

Related Questions