Migrations does not honor reference columns added to existing tables

ServiceStack.OrmLite
1

I have tried to add a new reference column to an existing table but no foreign key is created in the database.

I have tried using both the References and ForeignKey attributes without success.

        [AddColumn]
        [References(typeof(AppUser))]
        public int? Owner { get; set; }

The only thing that works is I have to define the FK manually like below:


 Db.AddForeignKey<Pipeline, SalesRegion>(
             field: t => t.SalesRegionId, 
             foreignField: tr => tr.Id,
             OnFkOption.NoAction,
             OnFkOption.NoAction,
             "FK_Pipeline_SalesRegion_SalesRegionId");

Please also note that using the the above command fails in Sqlite because the SQL generated is not correct and throws this error: SQLite Error 1: ‘near “CONSTRAINT”: syntax error’. the FK gets created correctly in my MS SQL 2019 server.

2

What does your Migration Up method look like? Is it just using Db.Migration<T>? Did you try using the ForeignKey attribute? Here is an example from the docs of a more complex example.

If you can create a minimal reproduction in a public GitHub repository I can take a look at the SQL generation issue for SQLite.

3

Yeah SQLite doesn’t support adding Foreign Keys Constraints. The recommended solution is to rename the old table, create the new table with all the Foreign Key constraints you need, then copy the data back in.

4

Yes, I tried ForeignKey attribute as well. Didn’t work.

The migration is pretty vanilla:

class ActionItem
    {
        [AddColumn]
        [References(typeof(AppUser))]
        public int? Owner { get; set; }
    }
    
    public override void Up()
    {
        Db.Migrate<ActionItem>();
    }

    public override void Down()
    {
        Db.Revert<ActionItem>();
    }
5

@bgiromini you are correct, the ForeignKey and Reference attributes currently do not call any path with FOREIGN KEY, so your use of Db.AddForeignKey is correct. I think this is the best approach since how/when/if populating FK row values is a task that generally will be more involved. We’ll take a look at how/if this might be better supported.

1 Like
6

My expectation is that it would have to obviously be nullable or it would never work. I am on a project that has spanned a year so requirements changed and I needed to add more references. I had done it a previous migration months ago assuming it created a foreign key but it did not.