nHibernate Support for Foreign Keys with SQLite

Question

nHibernate does not appear to support foreign keys with SQLite. By default foreign keys are not supported in SQLite either, but may be enabled by running PRAGMA foreign_keys = ON. But I don't know how or where to do this in my nHibernate code.

Any ideas on how to get nHibernate to work with foreign keys in SQLite? I found an old work-around which did not work (Does SQLite coupled with NHibernate support referential integrity / foreign keys?).

Here is my code. The mapping is done with hbm xml files, but that shouldn't matter. The error returned is "SQL logic error or missing database near "constraint": syntax error".

namespace ConsoleApp
{
    public class Employee
    {
        public virtual int Id { get; set; }
        public virtual string FirstName { get; set; }
        public virtual string LastName { get; set; }
        public virtual string Email { get; set; }
        public virtual int DepartmentId { get; set; }
        public virtual Department Department { get; set; }
    }

    public class Department
    {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
        public virtual List<Employee> Employees { get; set; }
    }

    class Program
    {
        static void Main(string[] args)
        {
            var cfg = new Configuration();
            cfg.DataBaseIntegration(x =>
            {
                x.ConnectionString = "data source=:memory:";
                x.Driver<SQLite20Driver>();
                x.Dialect<MsSql2012Dialect>();
                x.ConnectionReleaseMode = ConnectionReleaseMode.OnClose;

            });
            cfg.AddAssembly(Assembly.GetExecutingAssembly());

            var sessionFactory = cfg.BuildSessionFactory();
            var session = sessionFactory.OpenSession();

            new SchemaExport(cfg).Execute(true, true, false, session.Connection, null);
        }
    }
}

Answer 1

the reason why you get "SQL logic error or missing database\r\nnear \"constraint\": syntax error" is because you have set the wrong dialect.

You are using the MsSql2012 dialect so NHibernate will create SQL statements thinking that database supports certain features, but because it is different database, it doesn't support them.

There is no pragma, because MsSQL doesn't have pragma, so use SQLiteDialect instead:

cfg.DataBaseIntegration(x =>
{
  x.ConnectionString = "data source=:memory:";
  x.Driver<SQLite20Driver>();
  x.Dialect<SQLiteDialect>();
  x.ConnectionReleaseMode = ConnectionReleaseMode.OnClose;
});

Documentation:

You should always set the dialect property to the correct NHibernate.Dialect.Dialect subclass for your database.

SQLite
NHibernate.Dialect.SQLiteDialect
Set driver_class to NHibernate.Driver.SQLite20Driver for System.Data.SQLite provider for .NET 2.0.

SchemaExport SQL Commands for MsSQLDialect:

if exists (select 1 from sys.objects where object_id = OBJECT_ID(N'[FK20E4895FB0DFE40D]') AND parent_object_id = OBJECT_ID('Employee')) alter table Employee  drop constraint FK20E4895FB0DFE40D
if exists (select * from dbo.sysobjects where id = object_id(N'Employee') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table Employee
if exists (select * from dbo.sysobjects where id = object_id(N'Department') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table Department

create table Employee (
    Id INT not null,
    FirstName NVARCHAR(255) null,
    LastName NVARCHAR(255) null,
    Email NVARCHAR(255) null,
    DepartmentId INT null,
    primary key (Id)
)

create table Department (
    Id INT not null,
    Name NVARCHAR(255) null,
    primary key (Id)
)

alter table Employee
    add constraint FK20E4895FB0DFE40D
    foreign key (DepartmentId)
    references Department

It will fail here, because SQLite doesn't support alter table add constraint syntax (=here is the syntax error message).

SchemaExport SQL Commands for SQLiteDialect:

PRAGMA foreign_keys = OFF

drop table if exists Employee

drop table if exists Department

PRAGMA foreign_keys = ON

create table Employee (
   Id INT not null,
   FirstName TEXT,
   LastName TEXT,
   Email TEXT,
   DepartmentId INT,
   primary key (Id),
   constraint FK20E4895FB0DFE40D foreign key (DepartmentId) references Department
)

create table Department (
   Id INT not null,
   Name TEXT,
   primary key (Id)
)

The pragma is turned off while deleting and then reenabled again.