foreign key constraint failed, Fluent NHibernate, SQLite

[user...@gmail.com]

We have three C# classes: Branch, Team and Employee. A branch has many teams and many employees. A team has many employees. Each employee belongs to one team only.

Implementation 1:

session.CreateQuery("DELETE FROM Employee e WHERE e.Branch.Id = 1"); // line 1
session.CreateQuery("DELETE FROM Team t WHERE t.Branch.Id = 1"); // line 2

On line 2, we get “foreign key constraint failed” if one of the teams has employees. So it's like line 1 did not really work.

Implementation 2:

ICriteria criteria = session.CreateCriteria(typeof(Employee));
criteria.Add(NHibernate.Criterion.Expression.Eq(“Branch”, branch));
var e = criteria.List();
if (e != null && e.Count > 0) foreach (var e2 in e) this.Session.Delete(e2); 

ICriteria criteria = session.CreateCriteria(typeof(Team));
criteria.Add(NHibernate.Criterion.Expression.Eq(“Branch”, branch));
var t = criteria.List();
if (t != null && t.Count > 0) foreach (var t2 in t) this.Session.Delete(t2);

Implementation 2 works, but we think it’s slower because it reads objects from the DB.  How can we get implementation 1 to work?

Also note:

class BranchMap : ClassMap<Branch> { ...
  HasMany(x => x.Teams).Table("TEAM").KeyColumn("BRANCH_ID").Inverse();
  HasMany(x => x.Employees).Table("EMPLOYEE").KeyColumn("BRANCH_ID").Inverse();

class TeamMap : ClassMap<Team> { ...
  HasMany(x => x.Employee).Table("EMPLOYEE").KeyColumn("TEAM_ID").Inverse();
  References(x => x.Branch).Column("BRANCH_ID").Not.Nullable().ForeignKey();

class EmployeeMap : ClassMap<Employee> { ...
  References(x => x.Team).Column("TEAM_ID").ForeignKey();
  References(x => x.Branch).Column("BRANCH_ID").Not.Nullable().ForeignKey();

[Ricardo Peres]

You forgot ExecuteUpdate:

session.CreateQuery("DELETE FROM Employee e WHERE e.Branch.Id = 1").ExecuteUpdate();

RP

[user...@gmail.com]

We call ExecuteUpdate(). I forgot to put it in the post.

[Patrick Doran]

nHibernate aside, your delete command implies that a team may only have employees from the same branch, however your schema doesn't imply that. If your schema didn't have branch_id on the employee then you could delete all employees on a team, all teams from a branch etc.  I am sure you are enforcing that part with business logic, but just something I noticed.

[Ricardo Peres]

Using Log4Net, can you see the SQL that is sent to the database during the ExecuteUpdate() calls? And does the first call return a number greater than 0?

[user...@gmail.com]

We have the branch_id because sometimes we insert employees without a team.

[Patrick Doran]

Ok, that makes sense, however is it possible that you have team members on a team who don't have the same branch ID?  I ask this because perhaps your method 2 is actually applying a cascade delete (could be configured further up than the mappings you show) on the team members when you go to delete a team, however your first method, doesn't take that situation in to account.

IE:

 DELETE FROM Employee E where e.team_id in (select team_id from team where team.branch_id=1)

 DELETE FROM Team T where t.branch_id=1

This would ensure no FK constraint issues based on the case I mentioned.

[user...@gmail.com]

>is it possible that you have team members on a team who don't have the same branch ID?

No.

>perhaps your method 2 is actually applying a cascade delete

It's not.