Partition Columns For A Unique Index Must Be A Subset Of The Index Key.

Recently, I was trying to create a partitioned table in an SQL server; after executing the SQL script, I got this error. In this article, I will walk you through the solution.

Partition Columns For A Unique Index Must Be A Subset Of The Index Key.

I tried executing the SQL script below.

CREATE TABLE MyProducts (
    SalesID INT IDENTITY(1,1) PRIMARY KEY,
    ProductSaleDate DATETIME,
    CustomerID INT,
    ProductID INT,
    TotalQuantity INT,
    TotalAmount DECIMAL(10,2)
) ON ps_MyProductSalePerYear(ProductSaleDate);

The moment I executed the above script, I got this error. Check out the screenshot below for your reference.

Cause of this error

When creating unique indexes on partitioned tables, we must always include all the partition columns in the index key. But in this case, though the ProductSaleDate is a partition column, I missed including that I only included SalesID. The best way is to create a nonclustered index and ensure that all the partitioned columns are included. In this case, We need to include SalesID and ProductSaleDate.

Solution

I created a nonclustered index to solve this issue and included the SalesID and ProductSaleDate columns. The complete script looks like the one below.

CREATE TABLE MyProduct (
    SalesID INT IDENTITY(1,1),
    ProductSaleDate DATETIME,
    CustomerID INT,
    ProductID INT,
    TotalQuantity INT,
    TotalAmount DECIMAL(10,2)
   CONSTRAINT PK_Sales PRIMARY KEY NONCLUSTERED (SalesID, ProductSaleDate)
) ON ps_MyProductSalePerYear(ProductSaleDate);

After executing the above script, the command executed successfully without any issues, and the partitioned table was created successfully, as shown in the screenshot below.

To cross-check, we can execute the select statement below, and you can see in the screenshot below that the table was created successfully.

Conclusion

When creating unique indexes on partitioned tables, we must always include all the partition columns in the index key. If you miss including any partitioned column, you will end up with the error partition columns for a unique index that must be a subset of the index key. The above information in this article can help you fix this issue.

You may also like following the articles below.

• Cannot Define Primary Key Constraint On Nullable Column In Table
• Incorrect Syntax Near The Keyword ‘Order’
• Cannot create memory optimized tables. To create memory optimized tables, the database must have a MEMORY_OPTIMIZED_FILEGROUP that is online and has at least one container.