If in your project you let NHibernate generate the database from the information you provide with the schema mappings (a thing extremely useful, especially for testing cause you can build up the database in engines like SQLite), sometimes you may need to control some attribute in a foreign key of a table.

Here is a sample mapping:

   1: <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" >
   2:     <class name="Structura.UsersManager.Entities.User, Structura.UsersManager.Entities" table="tblUSERS" lazy="false" >
   3:         <id name="ID" column="ID" type="guid" >
   4:             <generator class="assigned" />
   5:         </id>
   6:         <property name="CustomerID" column="CUSTOMERID" type="guid" />
   7:         <property name="UserName" column="USERNAME" type="String" length="100" />
   8:         <property name="mSalt" access="field" column="SALT" type="String" length="10" />
   9:         <property name="mPassword" access="field" column="PASSWORD" type="String" length="200" />
  10:         <bag name="mGroups" access="field" table="tblUSERS_GROUPS" lazy="false" cascade="none" >
  11:             <key column="USERID" foreign-key="FK_USERS" />
  12:             <many-to-many lazy="false"
  13:                               class="Structura.UsersManager.Entities.Group, Structura.UsersManager.Entities"
  14:                               column="GROUPID"
  15:                               foreign-key="FK_GROUPS" />
  16:         </bag>
  17:     </class>
  18: </hibernate-mapping>

Consider the Users-Groups relation, you see it’s a many-to-many with two foreign keys defined; if we look at what NHibernate generates for us in SQL Express:

NHibernateForeignKeyProperties

As you can see the Delete Rule and the Update Rule are set to ‘No Action’, cause NHibernate handles object relationship internally using the ‘cascade’ attribute of the bag.

If you need to alter these values and use ‘Cascade’ instead of ‘No Action’, here are your options: in NHibernate 1.2 you have no direct way to modify the default behavior of a foreign key; in NHibernate 2.0 (and up) you can specify the delete rule using the on-delete attribute of the key tag, but it will work only with one-to-many relations.

So if you want full control, it seems you have no choice but to write some good old SQL code and ask NHibernate to execute it when creating the schema. For this purpose you can use the <database-object> tag in your mapping.

The <database-object> was created/introduced to let you create some object which NHibernate is not able to handle directly (like views and stored procedures for example), but you can use it to execute almost any SQL code. It has tree nested tags <create>, <drop>, <dialect-scope> for a full description of their functionality have a look at the official NHibernate documentation.

The SchemaExport() works by first dropping all the elements it can find in mappings and then recreating them: <drop> is used to supply NHibernate a script to use when dropping the objects, <create> is used...try to guess...you won, to create objects.

For our purpose we do not need to use the <drop> tag, so all the work will be made in the <create> tag; here we can add the SQL code needed to remove the foreign key automatically generated by NHibernate and create our key that specify the correct rules. Using this technique it’s important to explicitly specify the names of the foreign keys, otherwise will be impossible to interact with them because NHibernate will generate an ‘unpredictable’ name for them.

Our final mapping will look like this:

   1: <hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" >
   2:     <class name="Structura.UsersManager.Entities.User, Structura.UsersManager.Entities" table="tblUSERS" lazy="false" >
   3:         <id name="ID" column="ID" type="guid" >
   4:             <generator class="assigned" />
   5:         </id>
   6:         <property name="CustomerID" column="CUSTOMERID" type="guid" />
   7:         <property name="UserName" column="USERNAME" type="String" length="100" />
   8:         <property name="mSalt" access="field" column="SALT" type="String" length="10" />
   9:         <property name="mPassword" access="field" column="PASSWORD" type="String" length="200" />
  10:         <bag name="mGroups" access="field" table="tblUSERS_GROUPS" lazy="false" cascade="none" >
  11:             <key column="USERID" foreign-key="FK_USERS" />
  12:             <many-to-many lazy="false"
  13:                               class="Structura.UsersManager.Entities.Group, Structura.UsersManager.Entities"
  14:                               column="GROUPID"
  15:                               foreign-key="FK_GROUPS" />
  16:         </bag>
  17:     </class>
  18:     <database-object>
  19:         <create>
  20:             ALTER TABLE [dbo].[tblUSERS_GROUPS] DROP CONSTRAINT [FK_GROUPS]
  21:             ;
  22:             ALTER TABLE [dbo].[tblUSERS_GROUPS]  WITH CHECK ADD  CONSTRAINT [FK_GROUPS] FOREIGN KEY([GROUPID])
  23:             REFERENCES [dbo].[tblGROUPS] ([ID])
  24:             ON UPDATE CASCADE
  25:             ON DELETE CASCADE
  26:             ;
  27:             ALTER TABLE [dbo].[tblUSERS_GROUPS] CHECK CONSTRAINT [FK_GROUPS]
  28:         </create>
  29:         <drop></drop>
  30:     </database-object>
  31:     <database-object>
  32:         <create>
  33:             ALTER TABLE [dbo].[tblUSERS_GROUPS] DROP CONSTRAINT [FK_USERS]
  34:             ;
  35:             ALTER TABLE [dbo].[tblUSERS_GROUPS]  WITH CHECK ADD  CONSTRAINT [FK_USERS] FOREIGN KEY([USERID])
  36:             REFERENCES [dbo].[tblUSERS] ([ID])
  37:             ON UPDATE CASCADE
  38:             ON DELETE CASCADE
  39:             ;
  40:             ALTER TABLE [dbo].[tblUSERS_GROUPS] CHECK CONSTRAINT [FK_USERS]
  41:         </create>
  42:         <drop></drop>
  43:     </database-object>
  44: </hibernate-mapping>

This time we’ll have the correct rules for our foreign keys relations.

As you can imagine being able to intervene in the database schema creation can be extremely useful in a wide range of circumstances.

Related Content