Toad World® Forums

'Cannot create more than one clustered index on table ' DDL

Hi

I am trying to create the database on an MS SQL server from a TDM model.

Here are the errors i'm getting:
Msg 1902, Level 16, State 3, Line 2
Cannot create more than one clustered index on table 'drgs'. Drop the existing clustered index 'key25' before creating another.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.
Msg 1902, Level 16, State 3, Line 2
Cannot create more than one clustered index on table 'mdcs'. Drop the existing clustered index 'key26' before creating another.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors

Here's a snippet i get from generating the DDL from Toad:
-- table drgs

create table [drgs]
(
[drg code] nvarchar(15) not null,
[short description] nvarchar(25) null,
[long description] nvarchar(255) null,
[weight] decimal(18,2) null,
[geometric mean los] decimal(18,4) null,
[arithmetic los] decimal(18,4) null,
[los threashold] decimal(18,4) null,
[mdc code] nvarchar(15) null
)
go

-- add keys for table drgs

alter table [drgs] add constraint [key25] primary key ([drg code])
go

alter table [drgs] add constraint [drg code] unique clustered ([drg code])
go

I attached a screenshot of the entity 'DRGs' key tab.

04-Jul-11 5-48-01 PM.png

A table can only have one clustered index. SQL Server, by default, assumes that a primary key is also the clustered index. If you’d prefer to have a different index be the clustered index you will need to drop the clustered index and re-create it as a unique index and then create a new clustered index on another field or set of fields.

On table there can be only one clustered index. Clustered index will order data stored in table based on columns used in index. It is not possible to order data in table more then one way, therefore MSSQL server complains. It is possible, MSSQL is set by default to create clustered index on primary key and then custom clustered index is actually second clustered index on the table.

Fast forward one year. Is there a way to make the default for indexes non clustered? I have the same problem as the original poster.

Thanks

Hi,

what version of TDM do you use, please and with what version of SQL Server do you work with? SQL Server 2005, 2008 or 2012?

In the current version of TDM indexes are non clustered. When you create a new model, add several attributes to it and create new index, is the checkbox CLUSTERED on tab General (Index Properties form) checked?

Please attach sample model with one table that shows the problem or send it to modeling@quest.com.

Thank you,

Vaclav

I am using the latest version, 4.2.6.12, and sql server 2008.

I attached a sample model with one table that has a PK and an AK. Both are marked as Clustered by default.

Thanks
TestModel.txp (27.7 KB)

Hi,

thanks. Now I know what you mean. We talk about alternate keys and not indexes.
You can change the settings of default values.

  1. Open your model or create new model for SQL Server 2008.
  2. Click Settings | Default Values…
  3. Select section Entities - Keys and change value of Clustered item to false (see screenshot).
  4. Confirm the change and RESTART Toad Data Modeler.

When you create new alternate key (or mark attribute to be unique) TDM will create the key as nonclustered. Small disadvantage is that the settings work for both primary keys and alternate keys.

We will work on this feature to offer you more comfortable managment of the items. One idea is to add to our new verification that will offer “quick fixes”.

Thank you for your feedback.

Vaclav

Screenshot

Thank you