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)

,
Previous Post
SQL SERVER 2016 – New T-SQL Functions – COMPRESS – DECOMPRESS
Next Post
SQL SERVER – Unable to drop login – Msg 15170, Level 16, State 1

Related Posts

4 Comments. Leave new

  • Thomas Franz
    May 3, 2016 12:38 pm

    I had just the same problem, but not on the PK. It took me about a half hour to find out, that someone had created a UNIQUE index not as index but as UNIQUE constraint -> my CREATE INDEX … WITH DROP_EXISTING failed -> had to do an ALTER TABLE … DROP CONSTRAINT … before creating the index (as “real” index this time) the usual way

    Reply
  • HI, I am using sql 2016. Was under the assumption that I will not receive this error but getting this, I have existing db which is working fine in sql 2008 and trying to do POC in sql 2016 assuming tha will get some performance benefits but found that to get the benefit I need to make tremendous changes in the existing db like relationships, constrainst etc which is difficilt to make.
    May be for new db it make sence but for existing i doubt.

    Bi the way why I am getting the same message in sql 2016 ?

    Reply
  • Thomas Franz
    June 16, 2016 2:28 pm

    Why shouldn’t you? If you have an UNIQUE CONSTRAINT instead of an UNIQUE INDEX you will receive the error message, when you use the usual DROP INDEX / CREATE INDEX … WITH DROP_EXISTING commands.

    You have to use ALTER TABLE … DROP CONSTRAINT instead.

    Reply
  • Nagarjuna Bandi
    February 9, 2020 10:46 pm

    @Pinal Dave On SQL Server 2017, I was able to create Clustered Columnstore Index with Foreign key constraints, I only had to drop Primary key constraint.

    Reply

Leave a Reply

Menu