In the physical model for SQL Server, primary keys are clustered by default, which is good.
But for certain tables, particularly detail tables which are almost always accessed by way of the master table, I'd like to make the primary key nonclustered, and make a clustered index on the foreign key to the parent.
I can edit the index for the foreign key in the physical model and change it to clustered, but while I can SEE the primary key as clustered, I can't change it to be nonclustered. SQL Server only allows one clustered index so the DDL this generates is wrong.
There are two work-arounds:
I can edited the generated DDL and change the primary key constraint to nonclustered. Not too bad, considering that there are other changes I need to make to the generated DDL (like get some "go" commands onto a separate line).
I can actually find the XML file that stored the physical model of the table and change that. Then, I can see it when I edit the physical model (though I still can't change it there) and it generates DDL the way I want it.
But ...
Could this be changed in a future version of Data Modeler so that even though primary keys are still clustered by default, I can change it when I want to?
Thanks