hashtagnatty hashtagnatty - 2 months ago 17
ASP.NET (C#) Question

Change constraint names with migration

I have some Code-First generated ASP.NET Identity tables that I generated in the default

dbo
schema. I changed the schema of these tables by using
modelBuilder.HasDefaultSchema("Intranet");


and then applying my SchemaChange migration to update the database:

public override void Up()
{
MoveTable(name: "dbo.AspNetRoles", newSchema: "Intranet");
MoveTable(name: "dbo.AspNetUserRoles", newSchema: "Intranet");
MoveTable(name: "dbo.AspNetUsers", newSchema: "Intranet");
MoveTable(name: "dbo.AspNetUserClaims", newSchema: "Intranet");
MoveTable(name: "dbo.AspNetUserLogins", newSchema: "Intranet");
}


I am now attempting to do another migration to change the primary key types from string to int.

public override void Up()
{
DropForeignKey("Intranet.AspNetUserRoles", "RoleId", "Intranet.AspNetRoles");
DropForeignKey("Intranet.AspNetUserClaims", "UserId", "Intranet.AspNetUsers");
DropForeignKey("Intranet.AspNetUserLogins", "UserId", "Intranet.AspNetUsers");
DropForeignKey("Intranet.AspNetUserRoles", "UserId", "Intranet.AspNetUsers");
DropIndex("Intranet.AspNetUserRoles", new[] { "UserId" });
DropIndex("Intranet.AspNetUserRoles", new[] { "RoleId" });
DropIndex("Intranet.AspNetUserClaims", new[] { "UserId" });
DropIndex("Intranet.AspNetUserLogins", new[] { "UserId" });
DropPrimaryKey("Intranet.AspNetRoles");
DropPrimaryKey("Intranet.AspNetUserRoles");
DropPrimaryKey("Intranet.AspNetUsers");
DropPrimaryKey("Intranet.AspNetUserLogins");
AlterColumn("Intranet.AspNetRoles", "Id", c => c.Int(nullable: false, identity: true));
AlterColumn("Intranet.AspNetUserRoles", "UserId", c => c.Int(nullable: false));
AlterColumn("Intranet.AspNetUserRoles", "RoleId", c => c.Int(nullable: false));
AlterColumn("Intranet.AspNetUsers", "Id", c => c.Int(nullable: false, identity: true));
AlterColumn("Intranet.AspNetUserClaims", "UserId", c => c.Int(nullable: false));
AlterColumn("Intranet.AspNetUserLogins", "UserId", c => c.Int(nullable: false));
AddPrimaryKey("Intranet.AspNetRoles", "Id");
AddPrimaryKey("Intranet.AspNetUserRoles", new[] { "UserId", "RoleId" });
AddPrimaryKey("Intranet.AspNetUsers", "Id");
AddPrimaryKey("Intranet.AspNetUserLogins", new[] { "LoginProvider", "ProviderKey", "UserId" });
CreateIndex("Intranet.AspNetUserRoles", "UserId");
CreateIndex("Intranet.AspNetUserRoles", "RoleId");
CreateIndex("Intranet.AspNetUserClaims", "UserId");
CreateIndex("Intranet.AspNetUserLogins", "UserId");
AddForeignKey("Intranet.AspNetUserRoles", "RoleId", "Intranet.AspNetRoles", "Id", cascadeDelete: true);
AddForeignKey("Intranet.AspNetUserClaims", "UserId", "Intranet.AspNetUsers", "Id", cascadeDelete: true);
AddForeignKey("Intranet.AspNetUserLogins", "UserId", "Intranet.AspNetUsers", "Id", cascadeDelete: true);
AddForeignKey("Intranet.AspNetUserRoles", "UserId", "Intranet.AspNetUsers", "Id", cascadeDelete: true);
DropColumn("Intranet.AspNetRoles", "Discriminator");
}


My problem is that the schema change migration did not change the name of the constraints on all of the tables. So now I have a bunch of constraints titled something like:
PK_dbo.AspNetRoles
instead of
PK_Intranet.AspNetRoles
and when the migration attempts to drop these primary keys, it fails because it is attempting to drop a constraint that doesn't exist.

ALTER TABLE [Intranet].[AspNetRoles] DROP CONSTRAINT [PK_Intranet.AspNetRoles]
System.Data.SqlClient.SqlException (0x80131904): 'PK_Intranet.AspNetRoles' is not a constraint.
...
'PK_Intranet.AspNetRoles' is not a constraint.
Could not drop constraint. See previous errors.


I'm wondering what would be the best way to change all the names of these constraints using a migration. That way, if we ever need to roll back to
dbo
, for whatever reason, we can just roll back the migration.

Answer

I ended up looking at the SQL statements that were being executed using the -Verbose keyword at the end of my Update-Database -TargetMigration MigrationName statements to solve this.

I removed all foreign and primary keys from each table, then added them back with the correct names.

Where entity-framework was trying to do this:

DropForeignKey("Intranet.AspNetUserRoles", "RoleId", "Intranet.AspNetRoles");
DropIndex("Intranet.AspNetUserRoles", new[] { "UserId" });
DropPrimaryKey("Intranet.AspNetRoles");

Which translated to these SQL statements:

IF object_id(N'[Intranet].[FK_Intranet.AspNetUserRoles_Intranet.AspNetRoles_RoleId]', N'F') IS NOT NULL
    ALTER TABLE [Intranet].[AspNetUserRoles] DROP CONSTRAINT [FK_Intranet.AspNetUserRoles_Intranet.AspNetRoles_RoleId]
IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_UserId' AND object_id = object_id(N'[Intranet].[AspNetUserRoles]', N'U'))
    DROP INDEX [IX_UserId] ON [Intranet].[AspNetUserRoles]
ALTER TABLE [Intranet].[AspNetRoles] DROP CONSTRAINT [PK_Intranet.AspNetRoles]

I did this:

IF object_id(N'[Intranet].[FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId]', N'F') IS NOT NULL
    ALTER TABLE[Intranet].[AspNetUserRoles] DROP CONSTRAINT[FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId]
IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_UserId' AND object_id = object_id(N'[Intranet].[AspNetUserRoles]', N'U'))
    DROP INDEX[IX_UserId] ON[Intranet].[AspNetUserRoles]
ALTER TABLE[Intranet].[AspNetRoles] DROP CONSTRAINT[PK_dbo.AspNetRoles]

So, to change a single constraint name you would end up with this in your migration:

Sql("IF object_id(N'[Intranet].[FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId]', N'F') IS NOT NULL \r\n" +
        "ALTER TABLE[Intranet].[AspNetUserRoles] DROP CONSTRAINT[FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId]\r\n" +
    "IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_UserId' AND object_id = object_id(N'[Intranet].[AspNetUserRoles]', N'U')) \r\n" +
        "DROP INDEX[IX_UserId] ON[Intranet].[AspNetUserRoles] \r\n" +
    "ALTER TABLE[Intranet].[AspNetRoles] DROP CONSTRAINT[PK_dbo.AspNetRoles]");
AddPrimaryKey("Intranet.AspNetUserRoles", "Id");
CreateIndex("Intranet.AspNetUserRoles", "UserId");
AddForeignKey("Intranet.AspNetUserRoles", "RoleId", "Intranet.AspNetRoles", "Id", cascadeDelete: true);

Where "Intranet" is your new schema name, "dbo" is the old schema name, "Id" is the column name, "AspNetUserRoles" is the table with the foreign key, and "AspNetRoles" is your table with the primary key (the one you were trying to change in the first place).

To take care of the Down() migration method, just reverse the schema names.

Here is my new SchemaChange migration:

public override void Up()
{
    MoveTable(name: "dbo.AspNetRoles", newSchema: "Intranet");
    MoveTable(name: "dbo.AspNetUserRoles", newSchema: "Intranet");
    MoveTable(name: "dbo.AspNetUsers", newSchema: "Intranet");
    MoveTable(name: "dbo.AspNetUserClaims", newSchema: "Intranet");
    MoveTable(name: "dbo.AspNetUserLogins", newSchema: "Intranet");

    Sql("IF object_id(N'[Intranet].[FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId]', N'F') IS NOT NULL \r\n" +
            "ALTER TABLE[Intranet].[AspNetUserRoles] DROP CONSTRAINT[FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId]\r\n" +
        "IF object_id(N'[Intranet].[FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId]', N'F') IS NOT NULL \r\n" +
            "ALTER TABLE[Intranet].[AspNetUserClaims] DROP CONSTRAINT[FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId] \r\n" +
        "IF object_id(N'[Intranet].[FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId]', N'F') IS NOT NULL \r\n" +
            "ALTER TABLE[Intranet].[AspNetUserLogins] DROP CONSTRAINT[FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId] \r\n" +
        "IF object_id(N'[Intranet].[FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId]', N'F') IS NOT NULL \r\n" +
            "ALTER TABLE[Intranet].[AspNetUserRoles] DROP CONSTRAINT[FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId] \r\n" +
        "IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_UserId' AND object_id = object_id(N'[Intranet].[AspNetUserRoles]', N'U')) \r\n" +
            "DROP INDEX[IX_UserId] ON[Intranet].[AspNetUserRoles] \r\n" +
        "IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_RoleId' AND object_id = object_id(N'[Intranet].[AspNetUserRoles]', N'U')) \r\n" +
            "DROP INDEX[IX_RoleId] ON[Intranet].[AspNetUserRoles] \r\n" +
        "IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_UserId' AND object_id = object_id(N'[Intranet].[AspNetUserClaims]', N'U')) \r\n" +
            "DROP INDEX[IX_UserId] ON[Intranet].[AspNetUserClaims] \r\n" +
        "IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_UserId' AND object_id = object_id(N'[Intranet].[AspNetUserLogins]', N'U')) \r\n" +
            "DROP INDEX[IX_UserId] ON[Intranet].[AspNetUserLogins]\r\n" +
        "ALTER TABLE[Intranet].[AspNetRoles] DROP CONSTRAINT[PK_dbo.AspNetRoles]\r\n" +
        "ALTER TABLE[Intranet].[AspNetUserRoles] DROP CONSTRAINT[PK_dbo.AspNetUserRoles]\r\n" +
        "ALTER TABLE[Intranet].[AspNetUsers] DROP CONSTRAINT[PK_dbo.AspNetUsers]\r\n" +
        "ALTER TABLE[Intranet].[AspNetUserLogins] DROP CONSTRAINT[PK_dbo.AspNetUserLogins]\r\n" +
        "ALTER TABLE[Intranet].[AspNetUserClaims] DROP CONSTRAINT[PK_dbo.AspNetUserClaims]");

    AddPrimaryKey("Intranet.AspNetRoles", "Id");
    AddPrimaryKey("Intranet.AspNetUserRoles", new[] { "UserId", "RoleId" });
    AddPrimaryKey("Intranet.AspNetUsers", "Id");
    AddPrimaryKey("Intranet.AspNetUserLogins", new[] { "LoginProvider", "ProviderKey", "UserId" });
    AddPrimaryKey("Intranet.AspNetUserClaims", "Id");
    CreateIndex("Intranet.AspNetUserRoles", "UserId");
    CreateIndex("Intranet.AspNetUserRoles", "RoleId");
    CreateIndex("Intranet.AspNetUserClaims", "UserId");
    CreateIndex("Intranet.AspNetUserLogins", "UserId");
    AddForeignKey("Intranet.AspNetUserRoles", "RoleId", "Intranet.AspNetRoles", "Id", cascadeDelete: true);
    AddForeignKey("Intranet.AspNetUserClaims", "UserId", "Intranet.AspNetUsers", "Id", cascadeDelete: true);
    AddForeignKey("Intranet.AspNetUserLogins", "UserId", "Intranet.AspNetUsers", "Id", cascadeDelete: true);
    AddForeignKey("Intranet.AspNetUserRoles", "UserId", "Intranet.AspNetUsers", "Id", cascadeDelete: true);
}

public override void Down()
{
    MoveTable(name: "Intranet.AspNetUserLogins", newSchema: "dbo");
    MoveTable(name: "Intranet.AspNetUserClaims", newSchema: "dbo");
    MoveTable(name: "Intranet.AspNetUsers", newSchema: "dbo");
    MoveTable(name: "Intranet.AspNetUserRoles", newSchema: "dbo");
    MoveTable(name: "Intranet.AspNetRoles", newSchema: "dbo");

    Sql("IF object_id(N'[dbo].[FK_Intranet.AspNetUserRoles_Intranet.AspNetRoles_RoleId]', N'F') IS NOT NULL \r\n" +
            "ALTER TABLE[dbo].[AspNetUserRoles] DROP CONSTRAINT[FK_Intranet.AspNetUserRoles_Intranet.AspNetRoles_RoleId]\r\n" +
        "IF object_id(N'[dbo].[FK_Intranet.AspNetUserClaims_Intranet.AspNetUsers_UserId]', N'F') IS NOT NULL \r\n" +
            "ALTER TABLE[dbo].[AspNetUserClaims] DROP CONSTRAINT[FK_Intranet.AspNetUserClaims_Intranet.AspNetUsers_UserId] \r\n" +
        "IF object_id(N'[dbo].[FK_Intranet.AspNetUserLogins_Intranet.AspNetUsers_UserId]', N'F') IS NOT NULL \r\n" +
            "ALTER TABLE[dbo].[AspNetUserLogins] DROP CONSTRAINT[FK_Intranet.AspNetUserLogins_Intranet.AspNetUsers_UserId] \r\n" +
        "IF object_id(N'[dbo].[FK_Intranet.AspNetUserRoles_Intranet.AspNetUsers_UserId]', N'F') IS NOT NULL \r\n" +
            "ALTER TABLE[dbo].[AspNetUserRoles] DROP CONSTRAINT[FK_Intranet.AspNetUserRoles_Intranet.AspNetUsers_UserId] \r\n" +
        "IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_UserId' AND object_id = object_id(N'[dbo].[AspNetUserRoles]', N'U')) \r\n" +
            "DROP INDEX[IX_UserId] ON[dbo].[AspNetUserRoles] \r\n" +
        "IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_RoleId' AND object_id = object_id(N'[dbo].[AspNetUserRoles]', N'U')) \r\n" +
            "DROP INDEX[IX_RoleId] ON[dbo].[AspNetUserRoles] \r\n" +
        "IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_UserId' AND object_id = object_id(N'[dbo].[AspNetUserClaims]', N'U')) \r\n" +
            "DROP INDEX[IX_UserId] ON[dbo].[AspNetUserClaims] \r\n" +
        "IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_UserId' AND object_id = object_id(N'[dbo].[AspNetUserLogins]', N'U')) \r\n" +
            "DROP INDEX[IX_UserId] ON[dbo].[AspNetUserLogins]\r\n" +
        "ALTER TABLE[dbo].[AspNetRoles] DROP CONSTRAINT[PK_Intranet.AspNetRoles]\r\n" +
        "ALTER TABLE[dbo].[AspNetUserRoles] DROP CONSTRAINT[PK_Intranet.AspNetUserRoles]\r\n" +
        "ALTER TABLE[dbo].[AspNetUsers] DROP CONSTRAINT[PK_Intranet.AspNetUsers]\r\n" +
        "ALTER TABLE[dbo].[AspNetUserLogins] DROP CONSTRAINT[PK_Intranet.AspNetUserLogins]\r\n" +
        "ALTER TABLE[dbo].[AspNetUserClaims] DROP CONSTRAINT[PK_Intranet.AspNetUserClaims]");

    AddPrimaryKey("dbo.AspNetRoles", "Id");
    AddPrimaryKey("dbo.AspNetUserRoles", new[] { "UserId", "RoleId" });
    AddPrimaryKey("dbo.AspNetUsers", "Id");
    AddPrimaryKey("dbo.AspNetUserLogins", new[] { "LoginProvider", "ProviderKey", "UserId" });
    AddPrimaryKey("dbo.AspNetUserClaims", "Id");
    CreateIndex("dbo.AspNetUserRoles", "UserId");
    CreateIndex("dbo.AspNetUserRoles", "RoleId");
    CreateIndex("dbo.AspNetUserClaims", "UserId");
    CreateIndex("dbo.AspNetUserLogins", "UserId");
    AddForeignKey("dbo.AspNetUserRoles", "RoleId", "dbo.AspNetRoles", "Id", cascadeDelete: true);
    AddForeignKey("dbo.AspNetUserClaims", "UserId", "dbo.AspNetUsers", "Id", cascadeDelete: true);
    AddForeignKey("dbo.AspNetUserLogins", "UserId", "dbo.AspNetUsers", "Id", cascadeDelete: true);
    AddForeignKey("dbo.AspNetUserRoles", "UserId", "dbo.AspNetUsers", "Id", cascadeDelete: true);
}
Comments