CODEWITHSUNDEEP
c#entity-frameworkentity-framework-core
Harsha Mullangi
Harsha Mullangi

Reputation: 564

how to remove the default constraint from migration script

I have a migration script and I want to remove the default value from the column please help

 migrationBuilder.AddColumn<bool>(
              name: "IsExternalLink",
              table: "Link",
              nullable: false,
              defaultValue: 0);

I want to alter the column and remove the default value.

I tried altering column like this :

  migrationBuilder.AlterColumn<bool>(
                  name: "IsExternalLink",
                  table: "Link");

but this does not remove the default value

Upvotes: 8

Views: 6480

Answers (3)

FireEmerald
FireEmerald

Reputation: 1370

If you want to remove the DEFAULT value of a column you must define the oldDefaultValue.

// drop default
migrationBuilder.AlterColumn<bool>(
                name: "col",
                table: "my_table",
                oldDefaultValue: true, <-- THIS must be defined!
                defaultValue: null);

// Resulting Query: ALTER TABLE my_table ALTER COLUMN col DROP DEFAULT

Make also sure that the <type>, name (..) are also correct, because EfCore won't complain anything if your written AlterColumn statement is garbage.

Furthermore if your table already contains rows and you would like to add a new column with none DEFAULT, do the following:

// add column WITH default for existing rows
migrationBuilder.AddColumn<bool>(
                name: "col",
                table: "my_table",
                type: "boolean",
                nullable: false,
                defaultValue: true);

// drop default for new entries
migrationBuilder.AlterColumn<bool>(
                name: "col",
                table: "my_table",
                oldDefaultValue: true,
                defaultValue: null);

Upvotes: 13

ajbeaven
ajbeaven

Reputation: 9562

The code in your question should cause the default constraint to be removed. Are you sure it still exists? It might pay to look at the SQL that is generated when this migration is applied to the database.

With the following migration:

migrationBuilder.AddColumn<bool>(
    name: "MyColumn",
    table: "MyTable",
    nullable: false,
    defaultValue: true);

migrationBuilder.AlterColumn<bool>(
    name: "MyColumn",
    table: "MyTable",
    nullable: false,
    defaultValue: null);

The following SQL is generated when applying the migration:

ALTER TABLE [MyTable] ADD [MyColumn] bit NOT NULL DEFAULT CAST(1 AS bit);

DECLARE @var0 sysname;
      SELECT @var0 = [d].[name]
      FROM [sys].[default_constraints] [d]
      INNER JOIN [sys].[columns] [c] ON [d].[parent_column_id] = [c].[column_id] AND [d].[parent_object_id] = [c].[object_id]
      WHERE ([d].[parent_object_id] = OBJECT_ID(N'[MyTable]') AND [c].[name] = N'MyColumn');
      IF @var0 IS NOT NULL EXEC(N'ALTER TABLE [MyTable] DROP CONSTRAINT [' + @var0 + '];');
      ALTER TABLE [MyTable] ALTER COLUMN [MyColumn] bit NOT NULL;

Ever since this issue was fixed, default constraints should be removed on each call to AlterColumn. You'll note the majority of that migration SQL is responsible for determining the name of and dropping the default constraint on the MyColumn field.

Upvotes: 1

Shahid Manzoor Bhat
Shahid Manzoor Bhat

Reputation: 1335

Booleans are value types, You need to specify a value for them unless you make a nullable boolean as @StepUp has mentioned. You can read more abouut value types here

However if you want to have a default value for IsExternal different then zero you can set it in your POCO class

public boolean IsExternalLink{ get; set; } = true; // To wahtever default value you choose

Upvotes: 1

Related Questions