SQL SERVER – Error and Fix for Msg 1907 Cannot recreate index The new index definition does not match the constraint being enforced by the existing index

This particular blog post is around usage of Clustered ColumnStore Indexes and how one of my customer in a recent conversation brought this up to my notice. They were on SQL Server 2012 and were upgrading to SQL Server 2014 and were very much interested in the usage of Clustered ColumnStore Index that was introduced with SQL Server. It made a lot of sense for them because now the data is updatable with CCI implementation.

I was with the dev team and I saw an interesting error message and since I was standing there, they just turned to me and asked – “Hey Pinal, I am getting an error in SQL Server and can you help me quickly.” Not expecting this coming my way, I told them with a warm smile – please goto my blog and search for the error message. I am sure there is already the solution that you are looking forward to. I thought I solved their problem and in less than 5 seconds the developer turned around and said – “Buddy, you don’t have any blog on it.” This got me curious because he is feeding an interesting conversation that I can use it in my blog. So I immediately sat down with him and the only activity I did was to read the error message and I was convinced with the solution in hand. The message read:

Msg 1907, Level 16, State 1, Line 5
Cannot recreate index ‘PK_FactResellerSales_SalesOrderNumber_SalesOrderLineNumber’.
The new index definition does not match the constraint being enforced by the existing index.

Let me walk you through the steps of achieving the same and what was the workaround. For this example, I am creating the table with a bunch of constraints.

CREATE DATABASE ColumnStoreDemos
GO
USE ColumnstoreDemos
GO
-- Create a normal table.
IF OBJECT_ID('FactResellerSales_Trim') IS NOT NULL
DROP TABLE FactResellerSales_Trim
GO
CREATE TABLE [dbo].FactResellerSales_Trim (
[ProductKey] [int] NOT NULL
,[OrderDateKey] [int] NOT NULL
,[DueDateKey] [int] NOT NULL
,[ShipDateKey] [int] NOT NULL
,[ResellerKey] [int] NOT NULL
,[EmployeeKey] [int] NOT NULL
,[PromotionKey] [int] NOT NULL
,[CurrencyKey] [int] NOT NULL
,[SalesTerritoryKey] [int] NOT NULL
,[SalesOrderNumber] [nvarchar](20) NOT NULL
,[SalesOrderLineNumber] [tinyint] NOT NULL
,CONSTRAINT [PK_FactResellerSales_SalesOrderNumber_SalesOrderLineNumber] PRIMARY KEY CLUSTERED (
[SalesOrderNumber] ASC
,[SalesOrderLineNumber] ASC
)
) ON [PRIMARY] GO

You can add any data if required, but now assuming you have an existing table with data already there and you create an Clustered ColumnStore Index like below, you will get the same error:

-- Now Create a Columnstore Index to convert this table into Columnstore
CREATE CLUSTERED columnstore INDEX [PK_FactResellerSales_SalesOrderNumber_SalesOrderLineNumber] ON FactResellerSales_Trim
WITH (DROP_EXISTING = ON)
GO


Msg 1907, Level 16, State 1, Line 7
Cannot recreate index ‘PK_FactResellerSales_SalesOrderNumber_SalesOrderLineNumber’. The new index definition does not match the constraint being enforced by the existing index.

The error above indicates that we have an unsupported constraint. Primary keys or foreign key constraints are not supported on a table with clustered columnstore. Given that, if we want to create a clustered columnstore, we have to drop the constraints. Are we able to create the clustered columnstore index?

ALTER TABLE FactResellerSales_Trim
DROP CONSTRAINT [PK_FactResellerSales_SalesOrderNumber_SalesOrderLineNumber] GO
CREATE CLUSTERED COLUMNSTORE INDEX [CCI_FactResellerSales] ON FactResellerSales_Trim
GO

We can look at the successful creation of our Clustered ColumnStore Index using the query to DMVs:

SELECT * FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID('FactResellerSales_Trim')


Though this is a simple and self-explanatory error message. I thought I have not covered the same in this blog and just making sure that next time I tell someone to search – it shows up to help them. Do let me know if you ever get this error while you were migrating to the new version of SQL Server.

Reference: Pinal Dave (https://blog.sqlauthority.com)