Disabling Computed Columns in Database Migrations

======================================================

As a developer, it’s not uncommon to encounter issues when trying to modify database schema during migrations. In this article, we’ll explore how to “disable” a computed column so that you can apply a migration without encountering errors.

Understanding Computed Columns

Computed columns are a feature in databases that allow you to store the result of a computation as a column in your table. This can be useful for simplifying queries or reducing storage requirements. However, when you try to modify a computed column during a migration, things get complicated.

In our example, we have a migration that alters the CancelledVisit column in the Activities table. The IsComplete column is a computed column that uses CancelledVisit in its calculation. When we run this migration, we encounter an error because the IsComplete column depends on the CancelledVisit column.

The Error: Dependent Columns

The error message indicates that the column ‘IsComplete’ is dependent on column ‘CancelledVisit’. This makes sense, since IsComplete is a computed column that uses CancelledVisit in its calculation. However, this also means that we can’t simply remove or alter the CancelledVisit column without affecting the IsComplete column.

Alternative Approach: Drop and Re-add

One approach to dealing with this issue is to drop the IsComplete column from the table and then re-add it after the migration is complete. However, this approach has its own set of problems.

When we try to insert data into the sys.computed_columns system catalog table, we encounter an error because ad hoc updates to system catalogs are not allowed. This means that we can’t simply remove or alter a column in the system catalog without going through a formal migration process.

The Solution: Drop and Re-add Computation

The solution to this problem involves dropping the IsComplete column from the table, altering the CancelledVisit column, and then re-adding the IsComplete column with its original computation. Here’s an example of how you can do this:

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropColumn("IsComplete", "Activities");

    migrationBuilder.AlterColumn<bool>(
        name: "CancelledVisit",
        table: "Activities",
        type: "bit",
        nullable: false,
        defaultValue: false,
        oldClrType: typeof(bool),
        oldType: "bit",
        oldNullable: true);

    string comp = @"SELECT CASE WHEN CancelledVisit = 1 THEN 1 ELSE 0 END AS IsComplete";
    migrationBuilder.AddColumn<bool>(
        name: "IsComplete",
        table: "Activities",
        computedColumnSql: comp);
}

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropColumn("IsComplete", "Activities");

    migrationBuilder.AlterColumn<bool>(
        name: "CancelledVisit",
        table: "Activities",
        type: "bit",
        nullable: true,
        oldClrType: typeof(bool),
        oldType: "bit");

    string comp = @"SELECT CASE WHEN CancelledVisit = 1 THEN 1 ELSE 0 END AS IsComplete";
    migrationBuilder.AddColumn<bool>(
        name: "IsComplete",
        table: "Activities",
        computedColumnSql: comp);
}

In this example, we drop the IsComplete column from the Activities table and then alter the CancelledVisit column to make it non-nullable. We then re-add the IsComplete column with its original computation.

Conclusion

Disabling a computed column during a migration can be tricky, but by using the approach outlined in this article, you should be able to modify your database schema without encountering errors. Remember to drop and re-add the computed column with its original computation to ensure that everything works as expected.

Example Use Cases

• Suppose you’re working on a migration that involves altering the CancelledVisit column in the Activities table. However, the IsComplete column is a computed column that uses CancelledVisit in its calculation.
• In this case, you can use the approach outlined in this article to drop and re-add the IsComplete column with its original computation.

Tips and Tricks

• When working with computed columns during migrations, make sure to drop and re-add them with their original computation.
• Remember that ad hoc updates to system catalogs are not allowed, so be careful when modifying system catalog tables like sys.computed_columns.

Last modified on 2023-10-25