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