NHibernate mapping not adding ON DELETE CASCADE option to foreign key reference

Question

Here's my NHibernate mapping.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" assembly="HelloNHibernate" namespace="HelloNHibernate">
  <class name="Showing" table="showing">
    <id name="Id" column="showing_id">
      <generator class="identity"/>
    </id>
    <many-to-one class="Theater" name="Theater" column="theater_id" foreign-key="fk_showing_theater_theater_id" cascade="delete" lazy="false" fetch="join"/>
    <many-to-one class="Movie" name="Movie" column="movie_id" foreign-key="fk_showing_movie_movie_id" cascade="delete" lazy="false" fetch="join" />
  </class>
</hibernate-mapping>

Here's the SQL (PostgreSQL) generated by the SchemaExport tool:

CREATE TABLE showing
(
  showing_id serial NOT NULL,
  theater_id integer,
  movie_id integer,
  CONSTRAINT showing_pkey PRIMARY KEY (showing_id),
  CONSTRAINT fk_showing_movie_movie_id FOREIGN KEY (movie_id)
      REFERENCES movie (movie_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_showing_theater_theater_id FOREIGN KEY (theater_id)
      REFERENCES theater (theater_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

What am I doing wrong? Thanks!

Answer 1

NHibernate can only generate on delete cascade constraints on inverse collections.

Example for your domain:

<class name="Movie">
  ...
  <bag name="Showings" inverse="true" cascade="all">
    <key column="Foo" on-delete="cascade" /><!--Here's the magic-->
    <one-to-many class="Showing" />
  </bag>
</class>

Answer 2

To supplement the accepted answer, here's how you'd do it with Fluent NHibernate:

public class MovieMap : ClassMap<Movie>
{
     public MovieMap()
     {
         ...
         HasMany(c => c.Showings)
              .Inverse()
              .KeyColumn("Foo")
              .Cascade.All()
              .ForeignKeyCascadeOnDelete() // here's the magic
              .ForeignKeyConstraintName("FK_Movie_Showing"); // this is optional - name is autogenerated otherwise

Answer 3

NHibernate's cascade setting does not generation cascading foreign keys. It controls actions NHibnerate will take when a session is flushed.

Also, it's very unusual for the many side of the relationship to cascade deletes to the one side. Your mapping would delete a related Movie and Theater when a Showing is deleted.