NHibernate SchemaExport: How to generate a meaningful unique key name?

Question

When I use SchemaExport with SQL Server 2005, it generates unique key names like:

UQ__Employees__03317E3D

How can I generate a name like: UQ__Employees__Name? Even in SQL Server!

Answer 1

I think that there is a couple of ways to do what you are trying to to.

The first one is to specify the name in the mapping file. I know it works for foreign keys, though I haven't tried with unique keys.

<property name="KeyId" column="KeyId" type="Int" unique="true" unique-key="MyKeyName"/>

Within NHibernate you can change the Naming Strategy by creating a class that implements NHibernate.Cfg.INamingStrategy and adding that class when you configure nhibernate.

ISessionFactory sf = new Configuration()  
     .SetNamingStrategy(new YourNamingStrategy())  
     .Configure()  
     .SchemaExport(true, false);

The is also an ImprovedNamingStrategy that is built in to nhibernate. Can't remember what it outputs off hand but worth a try

ISessionFactory sf = new Configuration()
    .SetNamingStrategy(ImprovedNamingStrategy.Instance)
    .Configure()
    .SchemaExport(true, false);

EDIT
There are a couple of other possibilities I have found the first one involves the property tag. there is a column tag that has a number of attributes that may be of use.

<property name=KeyID>
  <column name="KeyId" unique-key="MyKeyName"/>
</property>

the other one is a bit more involved You can either add something like this

<database-object >
   <create>
      create table MyTable(
      Id UNIQUEIDENTIFIER not null,
      Name NVARCHAR(10) not null,
      RowVersion INT not null,

      primary key (Id)
      )

ALTER TABLE dbo.MyTable ADD  
  CONSTRAINT IX_Table_1 UNIQUE NONCLUSTERED(Name) 
  WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
  ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    </create>
    <drop></drop>
</database-object>

Or create a class that implements NHibernate.Mapping.IAuxiliaryDatabaseObject which will create the DDL statements.
Have a look in the NHiberate manual on nhibernate.info and scroll down to

5.6. Auxiliary Database Objects

This explains what you need to do.

Answer 2

Currently there (still) isn't a way to do this natively in NHibernate. I've gone through the issue tracking system and found an issue that was opened in 2.0 and never fixed. I've gone ahead and downloaded the source for NHibernate and was easily able to fix this problem. Building the project isn't the easiest but once you get it, it's not that bad. I'd post the code snippit here but it's a little large for that so if you want it let me know.

Answer 3

After doing a SchemaExport you could rename all PK based on the name of the table where they reside:

DECLARE @PKname nvarchar(255), @TName nvarchar(255), @TName2 nvarchar(258) DECLARE PKCursor CURSOR FOR SELECT PK.name, T.name AS Tname FROM sys.sysobjects AS PK INNER JOIN sys.sysobjects AS T ON PK.parent_obj = T.id WHERE (PK.xtype = 'PK') OPEN PKCursor FETCH NEXT FROM PKCursor INTO @PKname, @TName WHILE @@FETCH_STATUS = 0 BEGIN SET @TName2 = 'PK_' + @TName PRINT 'table ' + @TName + ' : renaming ' + @PKname + ' in ' + @TName2 Exec sp_rename @PKname, @TName2, 'OBJECT' FETCH NEXT FROM PKCursor INTO @PKname, @TName END CLOSE PKCursor DEALLOCATE PKCursor

Saw it years ago at: http://www.primordialcode.com/blog/post/nhibernate-give-primary-key-schemaexport-sql-server-sql-express