NHibernate - Name Primary key constraint?

Question

We're starting to using NHibernate at my workplace, including generating the schema from the mappings. One thing our DBAs want is consistent names for the primary and foreign key relationships. I've been able to set the FK constraint name, but looking at the documentation for <id> it doesn't look like there's a way to name the primary key constraint. http://www.nhforge.org/doc/nh/en/index.html#mapping-declaration-id

I assume I'm missing something, as this seems like a pretty basic thing to do.

Answer 1

I run the following script after the schema is generated to fix the primary key names. Replace $(targetDb) with your database name.

BEGIN TRANSACTION
DECLARE @Rename nvarchar(MAX)
DECLARE RenameCursor CURSOR FOR
    SELECT
            'EXEC sp_rename ''[' + c.CONSTRAINT_SCHEMA + '].[' + c.CONSTRAINT_NAME + ']'', ''PK_' + c.TABLE_NAME + ''', ''OBJECT'''
        FROM $(targetDb).INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
        WHERE
            c.CONSTRAINT_TYPE = 'PRIMARY KEY'
            AND
            c.TABLE_NAME IS NOT NULL
        ORDER BY c.TABLE_NAME
OPEN RenameCursor
FETCH NEXT
    FROM RenameCursor
    INTO @Rename
WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC sp_executesql @Rename
    FETCH NEXT
        FROM RenameCursor
        INTO @Rename
END
CLOSE RenameCursor
DEALLOCATE RenameCursor
COMMIT TRANSACTION

Answer 2

It's not supported, unfortunately. Here's an ugly workaround.

Answer 3

From http://www.primordialcode.com/blog/post/nhibernate-give-primary-key-schemaexport-sql-server-sql-express , here is a workaround:

NHibernate doesn’t offer (yet) a facility to give a name to your primary key (nothing that I found however, I admit I’m not an NHibernate guru, but an average user). You can use an approach similar to that exposed in my previous post.

In this example I’m using SQL Server/SQL Express as my database engine and the queries are built with that in mind.

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-lazy="false">
    <class name="SID.Sphera.Controls.Extended.ImageRegion.Entities.ImageSheetData, SID.Sphera.Controls.Extended"
    table="ImageRegionImageSheetData" lazy="false">
        <id name="_Id" access="field" column="IRISD_Id" type="guid">
            <generator class="guid" />
        </id>
        <property name="_Name" access="field" column="IRISD_Name" type="string" not-null="true" />
        <property name="_ResourceId" access="field" column="IRISD_ResourceId" type="guid" not-null="true" />
        <property name="_Width" access="field" column="IRISD_Width" not-null="true" type="int" />
        <property name="_Height" access="field" column="IRISD_Height" not-null="true" type="int" />
        <property name="_BackgroundImageId" access="field" column="IRISD_BackgroundImageId" type="guid"
         not-null="false" />
        <bag name="_sensitiveRegions" access="field" cascade="all-delete-orphan" lazy="false">
            <key column="IRIRD_ParentImageSheetId" foreign-key="FK_IRIRD_IRISD" />
            <one-to-many class="SID.Sphera.Controls.Extended.ImageRegion.Entities.ImageRegionData, SID.Sphera.Controls.Extended" />
        </bag>
    </class>
    <!-- Primary Key Rename -->
    <database-object>
        <create>
         DECLARE @pkName Varchar(255)
         ;
         SET @pkName= (
             SELECT [name] FROM sysobjects
             WHERE [xtype] = 'PK'
             AND [parent_obj] = OBJECT_ID(N'[dbo].[ImageRegionImageSheetData]')
         )
         ;
         Exec sp_rename @pkName, 'PK_ImageRegionImageSheetData', 'OBJECT'
        </create>
        <drop/>
    </database-object>
</hibernate-mapping>

With this query you get the actual name of the primary key which was generated by NHibernate, this is specific to SQL server/SQL express and is you use a different database engine you have to adapt those queries (I know you loose the decoupling to the database engine offered by NHibernate, but you can setup some strategies to load different mappings according to you current dialect).

We use a system stored procedure that allow us to rename the object we got before.