0
Not a bug

Error 'Cannot create more than one clustered index on table' when running Vertabelo generated scripts

Dave Sopko 3 years ago updated by Michał Kołodziejski 3 years ago 2

SQL Server. When specifying a multi-column clustered index that is not the primary key I get the following error when running the Vertabelo generated scripts.


Cannot create more than one clustered index on table 'DietCalendar'.


-- Table: DietCalendar
CREATE TABLE DietCalendar (
ID uniqueidentifier NOT NULL,
UserID uniqueidentifier NOT NULL,
EntryDate datetime2 NOT NULL,
CONSTRAINT DietCalendar_pk PRIMARY KEY (ID)
);
CREATE UNIQUE CLUSTERED INDEX UCI_DieteCalendar_UserID_EntryDate_ID on DietCalendar (UserID ASC,EntryDate ASC,ID ASC);

In order for this to be compatible with SQL Server 2012 as well as Azure SQL versions, I need Vertabelo to generate the clustered index within the 'CREATE TABLE' statement in this fashion:


CREATE TABLE DietCalendar (
ID uniqueidentifier NOT NULL,
UserID uniqueidentifier NOT NULL,
EntryDate datetime2 NOT NULL,
CONSTRAINT DietCalendar_pk PRIMARY KEY NONCLUSTERED (ID),
INDEX UCI_DieteCalendar_UserID_EntryDate_ID UNIQUE CLUSTERED (UserID ASC,EntryDate ASC,ID ASC)
);


Creating clustered indexes in Azure

http://stackoverflow.com/a/39730175/1197553


The create table should use the keyword CONSTRAINT not index:


CREATE TABLE DietCalendar (
ID uniqueidentifier NOT NULL,
UserID uniqueidentifier NOT NULL,
EntryDate datetime2 NOT NULL,
CONSTRAINT DietCalendar_pk PRIMARY KEY NONCLUSTERED (ID),
CONSTRAINT UCI_DieteCalendar_UserID_EntryDate_ID UNIQUE CLUSTERED (UserID ASC,EntryDate ASC,ID ASC)
);





Not a bug

Vertabelo already supports doing this.


You got the error because PK is "clustered" by default. In order to make it "nonclustered", expand "Additional properties" section in "Table details" and change "Is primary key clustered" to "NONCLUSTERED".


Then you can make other index "clustered" just as you did before.