You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Describe the bug
If you use advanced database features, SetExistingRowsTo will only set values on the main table, not the history table.
Create.Table("Example").InSchema("dbo").WithColumn("ExampleId").AsInt64().NotNullable().PrimaryKey().WithColumn("Version").AsRowVersion().NotNullable();.WithColumn("ValidFromInUTC").AsDateTime2().WithDefault(SystemMethods.CurrentUTCDateTime).WithColumn("ValidToInUTC").AsDateTime2().WithDefaultValue("CONVERT(DATETIME2(7), '9999-12-31 23:59:59.9999999')");// ExampleHistory is a near verbatim copy of Example, except for keys.
Create.Table("ExampleHistory").InSchema("dbo").WithColumn("ExampleId").AsInt64().NotNullable().WithColumn("Version").AsRowVersion().NotNullable();.WithColumn("ValidFromInUTC").AsDateTime2().WithDefault(SystemMethods.CurrentUTCDateTime).WithColumn("ValidToInUTC").AsDateTime2().WithDefaultValue("CONVERT(DATETIME2(7), '9999-12-31 23:59:59.9999999')");varschema="dbo";vartable="Example";varhistorySchema="dbo";varhistoryTable=table+"History";varrowStartDateColumn="ValidFromInUtc";varrowEndDateColumn="ValidToInUtc";
Execute.Sql("ALTER TABLE ["+schema+"].["+table+"] ADD PERIOD FOR SYSTEM_TIME(["+rowStartDateColumn+"], ["+rowEndDateColumn+"]);");
Execute.Sql("ALTER TABLE ["+schema+"].["+table+"] SET(SYSTEM_VERSIONING = ON(HISTORY_TABLE = ["+historySchema+"].["+historyTable+"], DATA_CONSISTENCY_CHECK = ON))");// Due to semantics of how T-SQL works, any changes to dbo.Example table will flow through to dbo.ExampleHistory
Alter.Table("Example").InSchema("dbo").AddColumn("IsEnabled").AsBoolean().Nullable().SetExistingRowsTo(false);// This will fail with an error
Alter.Column("IsEnabled").OnTable("Example").InSchema("dbo").AsBoolean().NotNullable();
To Reproduce
Observe the following error:
ALTER TABLE [dbo].[Example] ALTER COLUMN [IsEnabled] BIT NOT NULL
The error was Cannot insert the value NULL into column 'IsEnabled', table 'FluentMigratorTests_John.Zabroski.dbo.ExampleHistory'; column does not allow nulls. UPDATE fails.
The statement has been terminated.
Expected behavior
Should be in-line with Microsoft documented behavior in article Changing the Schema of a System-Versioned Temporal Table (or, at minimum, the Remarks visible in IntelliSense of FluentMigrator should call out this gotcha):
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. Adding DEFAULT to a non-empty table is a size of data operation on all editions other than SQL Server Enterprise Edition (on which it is a metadata operation).
We can also look at what Postgres does.
Information (please complete the following information):
FluentMigrator 3.3.1
Additional context
Add any other context about the problem here.
The text was updated successfully, but these errors were encountered:
Describe the bug
If you use advanced database features, SetExistingRowsTo will only set values on the main table, not the history table.
To Reproduce
Observe the following error:
Expected behavior
Should be in-line with Microsoft documented behavior in article Changing the Schema of a System-Versioned Temporal Table (or, at minimum, the Remarks visible in IntelliSense of FluentMigrator should call out this gotcha):
We can also look at what Postgres does.
Information (please complete the following information):
FluentMigrator 3.3.1
Additional context
Add any other context about the problem here.
The text was updated successfully, but these errors were encountered: