NHibernate: Identity columns that are not the primary key

Sometimes you need a column in your database to automatically increment (like an identity column) in order to provide back to a user say a confirmation number (when an item is added to that table). In NHibernate there's no way to specify this kind of behavior with normal mappings because the column in the Id tag has to be the primary key. Here's a technique we used to do this. 

Let's say we have a ticketing system (like TicketMaster) and it's going to give back the user a confirmation number after adding their request. The TicketRequest table ID is keyed off of a Guid but we can't provide that back to the user so we need an additional property called ConfirmationNumber (plus we don't want to expose ID fields to the users). 

Specify your table mappings like so:

<class name="TicketRequest" lazy="false">
  <id name="Id" type="guid.comb">
    <generator class="assigned"/>
  </id>
<property name="ConfirmationNumber" generated="insert" insert="false" update="false">
</class>

Then in the same mapping file change the ConfirmationNumber column on creation to an identity column using the <database-object> tag:

<database-object>
<create>
  ALTER TABLE TicketRequest DROP COLUMN ConfirmationNumber
  ALTER TABLE TicketRequest ADD ConfirmationNumber INT IDENTITY
</create>
</database-object>

There you have it. When you insert your record, you'll be able to use a Guid ID field but provide back an auto-incrementing field to your users.

Hope that helps.