CODEWITHSUNDEEP
sqlsql-server
Condxlences
Condxlences

Reputation: 33

Partition columns for a unique index must be a subset of the index key error

so my project requires me to split a table named Leases in two using partitions(one side of the partition are the leases that are older then January 15 2003 and the other side are the ones that are newer then that date)

basically I have previously created a table named "Leases" and I would like to create a partition for it(I need to create a whole new table to do so) so I copied all the data from that table to a new table named "Leases2" and now when I try to transfer all the constraints, they all execute with no error on the new table except the primary key constraint.

PS:I am new to sql.

so I created my partition like so:


CREATE TABLE Leases2
(
    ContractDate        datetime    NOT NULL,
    FirstPaymentDate    datetime    NOT NULL,
    MonthlyPayment      money       NOT NULL,
    NumPayments         tinyint     NOT NULL,
    VIN                 char(23)    NOT NULL,
    CustomerID          int         NOT NULL,
    LeaseTermID         int         NOT NULL
)
ON LeasesOldNewScheme (ContractDate)
GO

CREATE PARTITION FUNCTION LeasesOldNew (datetime)
AS RANGE RIGHT FOR VALUES('2003-01-15 00:00:00.000')
GO


CREATE PARTITION SCHEME LeasesOldNewScheme AS
PARTITION LeasesOldNew TO (OLeases, NLeases)
GO

ALTER TABLE Leases2
ADD PRIMARY KEY (LeaseTermID);

I get this error

"Column 'ContractDate' is partitioning column of the index 'PK__Leases2__30F848ED'. Partition columns for a unique index must be a subset of the index key." (contract date is one of the columns)

Upvotes: 3

Views: 8768

Answers (2)

YoYo
YoYo

Reputation: 9415

It seems that the fix is just to include "ContractDate" in the Index columns. Partitionened indexes are created alongside the table partitions and need to be partitioned within those same partitions. This means that the index needs to be sorted in a way that it is first sorted along the partition column (ContractDate).

To add your PK, it would be something like:

ALTER TABLE Leases2
ADD PRIMARY KEY (ContractDate,LeaseTermID);

I am guessing here, because I am not too familiar with sql-server, and I would have to research documentation.

Note that it is also not the same for all database implementations that support partitioning. Oracle for example lets you create local indexes (within the partition) independent of the partitioning scheme.

Upvotes: 3

dougp
dougp

Reputation: 3089

Assuming MS SQL Server...

The simple answer is that the column you are partitioning on must be part of the primary key for all of the tables that are being partitioned.

Now the bad news...

It may be better to create a new database and pump in the data than to partition the database with the data in place. It's all about run times, down times, performance tradeoffs...

CREATE TABLE Leases2 (
  LeaseTermId int identity(1,1) not null,
  ContractDate date not null, 
  <other column definitions...>,
  CONSTRAINT [idxPKLeaseTermId] PRIMARY KEY CLUSTERED (
    LeaseTermID ASC,
    ContractDate ASC
  ) WITH ((PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [LeasesOldNewScheme]([ContractDate]),
  <other constraints...>
) ON [LeasesOldNewScheme]([ContractDate])

I did this earlier this year with a mature data mart on SQL Server 2016. Including the database creation; partition function and scheme; creating all of the tables, views, etc.; indexing; extended properties (metadata); foreign keys; pumping the data across from the old database to the new one; and logging to be sure my 4-hour process was staying on track -- it ended up being about 35000 lines of SQL. Most of it was generated by SSMS from the old database. But I had to be sure it was all in the right order, the partitioning was working correctly and completely, and it ran in 4 hours instead of 23.

Upvotes: 0

Related Questions