Foreign Keys with SchemaExport in Fluent NHibernate using SQLite

Question

I am attempting to create a simple database application which keeps track of loans of various types of equipment using Fluent NHibernate and SQLite. However, when I try to generate the database structure with SchemaExport for use in unit testing, foreign keys for one-to-many relationships aren't created.

Here is my Equipment entity:

public virtual int Id { get; set; }

public virtual EquipmentType Type { get; set; }

public virtual int StockId { get; set; }

And here are my mappings for Equipment:

Id(x => x.Id);
References(x => x.Type);
Map(x => x.StockId);

The SQL is generated correctly, except for the lack of foreign keys:

create table "Equipment" (
       Id integer,
       StockId INTEGER,
       Type_id INTEGER,
       primary key (Id)
    )

Is it possible for SchemaExport to generate foreign keys when using an SQLite database?

Thanks.

Answer 1

I hit the same problem.

SQLite didn't initially support foreign keys(feature introduced in 3.6.19) so the NHibernate SQLiteDialect implementation doesn't know about foreign keys.

As SQLite doesn't support adding constraints through ALTER TABLE, only through CREATE TABLE parameters, the default foreign key creation of NHibernate is not used.

There's an incident logged on NHJIRA http://216.121.112.228/browse/NH-2200