CODEWITHSUNDEEP
c#asp.netentity-frameworkentity-framework-migrations
Nicolas
Nicolas

Reputation: 4756

C# - Foreign key references invalid table - Basic Migration not working

Trying to make my first project in C#. However, it's very frustrating so far. This is a problem I can't solve, since I don't see anything wrong.

I'm trying to just do a simple migrate in my DB.

Users migration file

 public override void Up()
        {
            CreateTable(
                "dbo.Users",
                c => new
                    {
                        user_id = c.Int(nullable: false, identity: true),
                        first_name = c.String(),
                        last_name = c.String(),
                        email = c.String(),
                        role = c.String(),
                    })
                .PrimaryKey(t => t.user_id);
        }

Locations migration file

 public override void Up()
        {
            CreateTable(
                "dbo.Locations",
                c => new
                {
                    loc_id = c.Int(nullable: false, identity: true),
                    loc_name = c.String(),
                })
            .PrimaryKey(t => t.loc_id);

            CreateTable(
                "dbo.UserLoc",
                c => new
                {
                    ul_id = c.Int(nullable: false, identity: true),
                    fk_user_id = c.Int(nullable: false),
                    fk_loc_id = c.Int(nullable: false),
                })
            .PrimaryKey(t => t.ul_id);
            AddForeignKey("dbo.UserLoc", "fk_user_id", "dbo.Users", "user_id");
            AddForeignKey("dbo.UserLoc", "fk_loc_id", "dbo.Locations", "loc_id");
        }

Models:

User.cs

public class User
{
    [Key]
    public int user_id { get; set; }

    public string firs_tname { get; set; }
    public string last_name { get; set; }
    public string email { get; set; }
    public string role { get; set; }
}

Location.cs

public class Locations
{
    [Key]
    public int loc_id { get; set; }
    public int loc_name { get; set; }
}

UserLoc.cs

public class UserLoc
{
    [Key]
    public int ul_id { get; set; }

    [ForeignKey("Users")]
    public int fk_user_id { get; set; }
    public virtual User user { get; set; }

    [ForeignKey("Locations")]
    public int fk_location_id { get; set; }
    public virtual Locations location { get; set; }
}

Every time I want to migrate I get the same error

Foreign key 'FK_dbo.UserLoc_dbo.Users_fk_user_id' references invalid table 'dbo.Users'. Could not create constraint or index. See previous errors.

What am I doing wrong?

Upvotes: 4

Views: 4119

Answers (2)

Eric Conklin
Eric Conklin

Reputation: 747

I had this issue recently.

I deleted all migrations, ran remove-migration a few times to clean up some of the default ASP.Net identity migrations, then recreated the migrations from scratch.

Entity Framework Core was confusing the default scaffolding of the IdentityUser class with my custom implementation, hence it could not find the table ASPNetUsers when I went to create the table that relates to it, in this case, Referrals.

Be sure to clear out any default migrations and run remove-migration when using EF Core with identity built into the database.

Hope this helps anyone that is confused.

Upvotes: 0

ocuenca
ocuenca

Reputation: 39326

First I recommend you to change the FK name that you are using in the ForeignKey attributes for the navigation property names:

public class UserLoc
{
    [Key]
    public int ul_id { get; set; }

    [ForeignKey("user")]
    public int fk_user_id { get; set; }
    public virtual User user { get; set; }

    [ForeignKey("location")]
    public int fk_location_id { get; set; }
    public virtual Locations location { get; set; }
}

This way you are telling it which navigation property represents the relationship it is a foreign key for.

After that, remove the old migration and try to run again Add-Migration command to regenerate it. You should see now that CreateIndex method is called before AddForeignKey method:

CreateIndex("dbo.UserLoc", "fk_user_id");
AddForeignKey("dbo.UserLoc", "fk_user_id", "dbo.Users", "user_id");

CreateIndex("dbo.UserLoc", "fk_loc_id");
AddForeignKey("dbo.UserLoc", "fk_loc_id", "dbo.Locations", "loc_id");

Upvotes: 2

Related Questions