I am partitioning a table based on a column that is not a primary key? I've read some conflicting information today on whether the partition column must be a part of the primary key. My gut says no, but I am not 100% sure. So questions...

  1. Must the partition column be part of the primary? Is it recommended one way or the other?
  2. Do I have to create an index for the partition key, or does the DBMS do it automatically on its own?

3 Answers 3


Not at all.

One of the most common scenarios for partitioning is to use a date field, which is totally unrelated to your PK.

For instance, if you have a table Orders with the field OrderDate you would most likely partition based on the month and year of OrderDate.

When records age out and are no longer relevant you can move those partitions off to an archive table or database so they are no longer processed.

Partitioning will work with pretty much any field, but in order for it to work WELL the field(s) you partition on should be used in most, if not all, of your queries. If you don't include your partition keys then you will get essentially an expensive table scan that goes across multiple tables (partitions).


For part 2, I think the answer is no as well. The partition key is used to determine which partition to put the row in, but I don't think an index is maintained. There may be stats in the back end on it though.

  • 13
    I know this is old, but it lead me down a wrong path so I thought I would comment for others. The Partition Column has to be in the Primary key if you want to use the SWITCH abilities of the Partition feature. If it is not in the primary key you will get this error: Partition columns for a unique index must be a subset of the index key.
    – Vaccano
    Commented Jun 4, 2015 at 21:21
  • I agree with @Vaccano
    – san
    Commented Aug 20, 2019 at 0:55
  • if you wish to partition a table using any other column or columns in the partitioning expression, you must first modify the table, either by adding the desired column or columns to the primary key, or by dropping the primary key altogether. dev.mysql.com/doc/mysql-partitioning-excerpt/5.7/en/…
    – abhij89
    Commented Jul 19, 2021 at 19:21
  • I stumbled over this in a scenario where I do a lot of upserts into an existing table (delta lake/databricks environment). Here I obviously have a lot of queries (update if exists) including the primary keys - so I derive an artificial partition column from my set of primary key columns to speed up those queries. Makes sense?
    – Marti Nito
    Commented Feb 23, 2022 at 10:57

In addition to JNK's answer, you probably should read this article which discusses aligning table partitions and index partitions.

There are many types of scenarios where partitioning scheme does exactly follows the primary key's first column - for instance in a data warehouse scenario where the snapshot date of a fact table is usually the partition column as well as the first column in the primary key.

But equally, in OLTP environments where the PK is an IDENTITY or other surrogate key, it makes little sense to use this for the partition, since partitioning on arbitrary numbers is not normally terribly useful. In OLTP systems, you also tend to partition by date the most (probably not in the PK), but potentially also regionally or by some kind of organizational division (maybe in the PK if you aren't using a surrogate).

But it's not a requirement.

  • Well, a whole lot of stuff is not a requirement. Even indexing is not a requirement! To make sense functionally, partitioning has to be done on the leading column of a candidate key. Otherwise how would the app architect use the table? Commented Jul 11, 2012 at 18:19
  • @srini.venigalla That's a common case, but another common case (equally so?) is to partition on something which is not a part of a primary or candidate key at all - because partitioning is often used for archiving, an expiry date can be a good partition choice. But there's nothing which implies that might be part of a key. Partitioning is a low-level feature which is pretty generic and there are at least two distinct and conflicting usage patterns here, both of which have legitimate best practices around them.
    – Cade Roux
    Commented Jul 11, 2012 at 19:00

It has to be part of a Candidate Key if not part of the Primary key itself. Idea being, your partitioning should align itself with the primary key.

So the answer is, yes, it is preferred to be part of the PK. If not another key, which is equally good enough to be a PK.

  • Never heard of the Candidate Key. How does one specify it in the Create/Alter table statement? Commented Jul 9, 2012 at 21:53
  • Candidate Key is just another key qualified to be a Primary Key. For example, ID is the primary key. But in the same table, if another column for eg. PERSON_ID also can uniquely identify a row, that is called Candidate Key. The 2nd and 3rd normalization rules should hold against all candidate keys also. Commented Jul 10, 2012 at 15:29
  • Understood. What about the 2nd part of my question? Commented Jul 10, 2012 at 16:50
  • Same as any other index. example: CREATE INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor (BusinessEntityID); Commented Jul 10, 2012 at 17:36
  • 5
    This is absolutely incorrect. You can partition on many fields that are not related to the PK at all, such as OrderDate. Do you have anything to back up your claims?
    – JNK
    Commented Jul 10, 2012 at 17:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.