Fluent Nhibernate foreign key constraint throw exception on delete

Question

I have a one-many relation:

Object ReportCategory:

Table("RCA_REPORT_CATEGORY");
    Id(r => r.Id, "RCA_ID");
    Map(r => r.Name, "RCA_NAME");
    HasMany<Report>(r => r.Reports)
        .Table("REP_REPORT")
        .KeyColumns.Add("REP_RCA_ID").Cascade.None();

Object Report:

Table("REP_REPORT");
        Id(r => r.Id, "REP_ID");
        Map(r => r.Name, "REP_NAME");
        References(r => r.Category, "REP_RCA_ID")
            .Cascade.None();

There is a foreign key on REP_REPORT.REP_RCA_ID which references RCA_REPORT_CATEGORY.RCA_ID.

When I delete a ReportCategory which has a Report, it works and the REP_RCA_ID column in the Report Table is set to NULL. But I don't want that. I want an exception to be thrown and tell me that the category can't be deleted because it is used by a report and it violate the foreign key constraint. How can I achieve that?

Answer 1

Make Category reference not nullable:

Table("REP_REPORT");
        Id(r => r.Id, "REP_ID");
        Map(r => r.Name, "REP_NAME");
        References(r => r.Category, "REP_RCA_ID")
            .Not.Nullable()
            .Cascade.None();

Then the database column REP_RCA_ID cannot be NULL and attempt to delete not-empty category will lead to SQL exception. Such case should be avoided in your domain model first and the exception from db should be throwed only when there is a bug in your application.

Maybe you should mark HasMany collection as inverse to avoid some troubles:

Table("RCA_REPORT_CATEGORY");
    Id(r => r.Id, "RCA_ID");
    Map(r => r.Name, "RCA_NAME");
    HasMany<Report>(r => r.Reports)
        .Table("REP_REPORT")
        .Inverse()
        .KeyColumns.Add("REP_RCA_ID").Cascade.None();