The object 'PK_AspNetUserTokens' is dependent on column 'Name'. ALTER TABLE ALTER COLUMN Name failed because one or more objects access this column

4
votes

I am trying to extend the IdentityUser class. I added a new class ApplicationUser and inherit IdentityUser class. Migration is added successfully but on updating database, I am getting the error "The object 'PK_AspNetUserTokens' is dependent on column 'Name'.ALTER TABLE ALTER COLUMN Name failed because one or more objects access this column.". I opened the SSMS and looked for data in AspNetUserToken, the table was empty.

a couple of things I have tried but end up with the same error. I replaced all the references to the IdentityUser class in my code. Delete the data in table 'AspNetUsers'. Remove the migration after replacing references and deleting data. Again added migration and update database, the error was still there. 

AppDbContext.cs


namespace PieShop.Data_Access_Layer
{
    public class AppDbContext :IdentityDbContext<ApplicationUser>
    {
        public AppDbContext(DbContextOptions<AppDbContext> options)
            :base(options)
        {
        }
        public DbSet<Pie> Pies { get; set; }
        public DbSet<Feedback> Feedbacks { get; set; }
    }
}


IdentityHostingStartup.cs


[assembly: HostingStartup(typeof(PieShop.Areas.Identity.IdentityHostingStartup))]
namespace PieShop.Areas.Identity
{
    public class IdentityHostingStartup : IHostingStartup
    {
        public void Configure(IWebHostBuilder builder)
        {
            builder.ConfigureServices((context, services) => {
                services.AddDefaultIdentity<ApplicationUser>().AddEntityFrameworkStores<AppDbContext>();
            });
        }
    }
}
ApplicationUser.cs


namespace PieShop.Models
{
    public class ApplicationUser : IdentityUser
    {
        [Required]
        [MaxLength(30)]
        public string City { get; set; }
        [Required]
        public string Address  { get; set; }
        [Required]
        [MaxLength(20)]
        public string Country { get; set; }
    }
}

Migration

using Microsoft.EntityFrameworkCore.Migrations;

namespace PieShop.Migrations
{
    public partial class ApplicationUserAdded : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
        migrationBuilder.AlterColumn<string>(
            name: "Name",
            table: "AspNetUserTokens",
            maxLength: 128,
            nullable: false,
            oldClrType: typeof(string));

        migrationBuilder.AlterColumn<string>(
            name: "LoginProvider",
            table: "AspNetUserTokens",
            maxLength: 128,
            nullable: false,
            oldClrType: typeof(string));

        migrationBuilder.AddColumn<string>(
            name: "Address",
            table: "AspNetUsers",
            nullable: false,
            defaultValue: "");

        migrationBuilder.AddColumn<string>(
            name: "City",
            table: "AspNetUsers",
            maxLength: 30,
            nullable: false,
            defaultValue: "");

        migrationBuilder.AddColumn<string>(
            name: "Country",
            table: "AspNetUsers",
            maxLength: 20,
            nullable: false,
            defaultValue: "");

        migrationBuilder.AlterColumn<string>(
            name: "ProviderKey",
            table: "AspNetUserLogins",
            maxLength: 128,
            nullable: false,
            oldClrType: typeof(string));

        migrationBuilder.AlterColumn<string>(
            name: "LoginProvider",
            table: "AspNetUserLogins",
            maxLength: 128,
            nullable: false,
            oldClrType: typeof(string));
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(
            name: "Address",
            table: "AspNetUsers");

        migrationBuilder.DropColumn(
            name: "City",
            table: "AspNetUsers");

        migrationBuilder.DropColumn(
            name: "Country",
            table: "AspNetUsers");

        migrationBuilder.AlterColumn<string>(
            name: "Name",
            table: "AspNetUserTokens",
            nullable: false,
            oldClrType: typeof(string),
            oldMaxLength: 128);

        migrationBuilder.AlterColumn<string>(
            name: "LoginProvider",
            table: "AspNetUserTokens",
            nullable: false,
            oldClrType: typeof(string),
            oldMaxLength: 128);

        migrationBuilder.AlterColumn<string>(
            name: "ProviderKey",
            table: "AspNetUserLogins",
            nullable: false,
            oldClrType: typeof(string),
            oldMaxLength: 128);

        migrationBuilder.AlterColumn<string>(
            name: "LoginProvider",
            table: "AspNetUserLogins",
            nullable: false,
            oldClrType: typeof(string),
            oldMaxLength: 128);
    }
}

}

1
c#asp.net-mvcentity-frameworkasp.net-coreentity-framework-core-2.2
Not reproduce , could you please provide detailed steps to help reproduce ? – Nan Yu
I am using asp.net core 2.2 along with entity framework core and identity core. I started from empty project templet and set required middlewares to work with entity-framework, static files, and identity. I register some user's with default identify user class and after that, I extended the IdentityUser class and made the migration. Migration was successful but when I updated the database the update database was failed with this ERROR mentioned above. – Saad Sid
Solved this problem by deleting the database and rollback migrations until the identity was added. after this, I added migration with ApplicationUser class (extended from identityUser class) and the fields in ApplicationUser class added successfully in AspNetUser table. – Saad Sid
You are changing the UserId from max length from 450 to 128 characters. Because of the primary and foreign key constraints you cannot. It seems to me, it is an issue with migrationBuilder.AlterColumn not handling the two keys. If I were to do this in SSMS, I would need to drop all PK and FK that reference UserId before making the change to all columns. Unfortunately, your solution didn't solve my issue. – Phil Huhn
I don't know why it didn't work for you but after this, my issue was resolved – Saad Sid

1 Answers

4
votes

I solved it by editing the migration and adding drop and add commands for the Primary Key.

At the top of the new migration, add:

migrationBuilder.DropPrimaryKey("PK_AspNetUserTokens", "AspNetUserTokens");

and after all modifications to AspNetUserTokens have taken place, add

migrationBuilder.AddPrimaryKey("PK_AspNetUserTokens", "AspNetUserTokens", new[] { "UserId", "LoginProvider", "Name" });