Ormlite CreateTableWITHOUTForeignKeys

ServiceStack.OrmLite
1

I’ve asked previously about creating circular references in Ormlite for db tables (see http://stackoverflow.com/questions/33888442/servicestack-ormlite-circular-reference-between-parent-and-child-tables-prevent)

In that instance, I was able to work around the issue, but I am now in the situation where changes to entities over the course of db migrations (I use a wrapper for DbUp, where my db is initialised using ormlite Pocos), mean I experience pain when the POCO class changes over time, and results in a db initialisation / migration fail.

Take for example, step one: initialise a database with pocos

class Parent { 
    int Id, 

    string Name, 

    [Reference]
    List<Child> Children 
},

and

class Child { 
    int Id, 
    string Name, 
    [References(typeof(Parent))]
    int ParentId 
}

Step 1, in DbUp, i make use of Ormlite to CreateTableIfNotExists< Parent > and then CreateTableIfNotExists< Child >

So far, so good, i can initialise my db as often as i like (e.g. I do a full destroy-and-create for integration tests), I get the benefits of the reference retreival AND i get the benefit of Ormlite creating an explicit foreign key.

I later come back and decide that Parent class should include a reference to FavouriteChild, and modify my POCO accordingly:

class Parent { int Id, string Name, [References(typeof(Child))]]int FavouriteChildId }

When I then run my DB Migration sequence to create a new database, it will fail… can’t create the Parent table with foreign key to nonexistant Child table… so I am stuck with not being able to use the Ormlite to create tables in the migrations context, OR losing the Ormlite creation of foreign keys.

As a non-breaking change, could the CreateTableIfNotExists have a “skip foreign keys” optional overload, which and the foreign key creation be broken out into a distinct, separate piece of functionality? By doing that, one would be able to still use the ormlite table-creation but retain foreign key support, and then in future migrations, as new columns are added for example, I’d be able to

DbUp MigrationScript1:

CreateTableIfNotExists<Parent>(skipforeignkeys = true)
CreateTableIfNotExists<Child>()

DbMigrationScript2 (at which point, the FavouriteChildId property has later been added to the original Parent POCO class):

NewFunctionToAddKey<Parent,Child>(p => p.FavouriteChild, c => c.Id);

Something like that?

2

We’d typically use global configuration for modifier features like this which I’ve now added in this commit where you can disable creating foreign keys with the new OrmLiteConfig.SkipForeignKeys option which applies to all CreateTable* APIs, e.g:

OrmLiteConfig.SkipForeignKeys = true;

db.CreateTable<Parent>();
db.CreateTableIfNotExists<Child>();

OrmLiteConfig.SkipForeignKeys = false;

This change is available from v4.5.5 that’s now available on MyGet.

1 Like