[SOLVED] Create nonclustered primary keys using NHibernate and SchemaExport

Create nonclustered primary keys using NHibernate and SchemaExport

We're using SchemaExport via ActiveRecord. By default it generates a table like this:

create table List (
  Id      UNIQUEIDENTIFIER   not null,
  Name    NVARCHAR(255)   null,
  OwnerId UNIQUEIDENTIFIER   null,
    primary key ( Id ))

SQL Server then defaults to adding a clustered index for the primary key. But I want this to be nonclustered. I want to add a clustered index to OwnerId as this will be much more efficient.

Now, I could run a script afterwards to create a non-clustered index. This would involve dropping the original primary key constraint and adding a non-clustered one. However, SchemaExport has already helpfully created all my foreign key constraints which stop me dropping the primary key.

So I need to drop the foreign keys, which have an unhelpful name like FK4BAD9607D2BEDDB5, then recreate them (can I do this again automatically?). It's all a bit of a headache.

It would be a lot easier if I could just get in there somehow and add a nonclustered specification to the primary key as it generates it. Is there a relevant bit of the export tool I can override to do this?

Thanks

Solution

I believe your best option is using SchemaExport to create the script, and modify it manually.

Otherwise, you'll need to override Dialect.GetAddPrimaryKeyConstraintString.