- Vertabelo
-
Fehler
Error 'Cannot create more than one clustered index on table' when running Vertabelo generated scripts
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'.
CREATE TABLE DietCalendar (
ID uniqueidentifier NOT NULL,
UserID uniqueidentifier NOT NULL,
EntryDate datetime2 NOT NULL,
CONSTRAINT DietCalendar_pk PRIMARY KEY (ID)
);
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
Customer support service by UserEcho
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)
);
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.