I have some tables that we want to partition on a date column which is not the primary key. I kept getting error message "Partition columns for a unique index must be a subset of the index key." So I added the date column to the primary key. Now I can't recreate the foreign key because foreign key has to include all columns of the primary key and it doesn't make sense to include the date column in the foreign key.

  • Primary Key - is an identity column
  • Foreign Key wants to reference the Primary key (identity column only)
  • Partition should be on a date column
  • My question is: Is there any way to either 1. partition on the date column without including it in the primary key. or 2. set up a foreign key on only one column of a compound primary key. or 3. some other workaround that is eluding me but someone else might have thought of. – Beth Aug 19 '16 at 13:52
  • Regarding the clustered index comment. I tried creating a partitioned clustering index on the date column and then making the primary key non-clustered but as far as I can tell there is still a requirement for all unique indexes to include the partitioning column so it doesn't help at all to have a separate clustering index. – Beth Aug 19 '16 at 13:58
  • Creating a clustered index ensures you don't wind up with a heap. Step 1 - please try to create the clustering index on id and partitioned date column on the partition scheme - is that successful? – Scott Hodgin Aug 19 '16 at 15:16
  • We could help faster if you post your partition scheme and function along with you table definition and any indexes you want - we already know the primary key had to be id – Scott Hodgin Aug 19 '16 at 15:33
  • Regarding your Step 1. Yes, I did that and was happy with it until I tried to add the foreign key. The problem is that the foreign key needs to be created against a unique index and all unique indexes must have the partitioning column. I was hoping someone would have a clever workaround for this issue but at this point the only option I can see is to manually code the RI the way we did it before SQL Server introduced foreign keys. – Beth Aug 19 '16 at 19:04

Based on the comments, it seems the answer is to create a clustered index on the column you want to use to partition the data and then make sure your non-clustered Primary Key index is not partition aligned so you will not need to add the partitioned column to the primary key.

By doing this, you can keep the primary key "simple" and use it like you normally would in a non-partitioned table.

  • Just a comment - you might consider keeping the Id as the first column of the clustered index to reduce fragmentation (unless, the partitioning date column is also ever increasing), Unfortunately, you will not be able to SWITCH partitions in or out due the indexes being non-aligned (if that was even a goal of the partitioning effort). If the goal of the partitioning effort was for index maintenance, you'd still need to partition the primary key on a different schema to take advantage of rebuild/reorganizing indexes at the partition level. – Scott Hodgin Aug 20 '16 at 10:59

Since you’re already talking about partitioning this table, I’m assuming there is already a LOT of data in it, so this may be a bit painful. You may actually have to create a whole new table and batch load data from the existing table to keep from blowing out your log space. Perhaps someone has a more efficient way of doing this (and that’s fine as I’m open to learning better ways of doing stuff), but I think this is the way I would approach this problem.

Every index participating in a partition table scenario MUST contain the partition column in some manner. Clustered indexes cannot have INCLUDED columns, but non-clustered indexes can.

  • Disable all foreign keys pointing to the parent table.
  • On the parent table, create a new clustering index (DROP_EXISTING) and (not primary key) on (at least) Id and your partitioning date column. Clustering indexes cannot contain included columns.
  • Create a non-clustered primary key on Id and ‘INCLUDE’ the partitioning data column.
  • Re-enable the foreign keys pointing to the parent table.
  • Actually what we are doing is renaming the existing table and replacing it with a partitioned table which allows us to avoid the issues you discuss. However the problem is that the current foreign key will not create successfully because it no longer matches the primary key because we had to add the partitioning column to the primary key. We can't add the date column to the foreign key so I'm currently stuck with telling my customer we either can't partition the table or we have to remove the foreign key. – Beth Aug 19 '16 at 13:50
  • You need to create a 'non-clustered' primary key that is just id, but add the partitioning date as an INCLUDED column. The clustering index can then be Id and whatever the partitioning date column is. You don't add the partitioning key to the primary key - you 'INCLUDE' it, which is allowed because it will now be a non-clustered index – Scott Hodgin Aug 19 '16 at 13:56
  • tried that. Apparently ALL unique indexes have to have the partitioning key as part of the index and INCLUDEd columns do not qualify. "Column 'Loading_Date' is partitioning column of the index 'NonClusteredIndex-20160819-102222'. Partition columns for a unique index must be a subset of the index key." – Beth Aug 19 '16 at 18:43
  • Hmm - that's discouraging - that DOES appear to be the case (and I have been enlightened) - are you partitioning for SWITCHING or merely index maint in smaller sets of data - if not SWITCHING, you could have 2 partitioned schemes - one for primary key and one for other indexes - I'm still researching how others might handle this and will be interested to know if others recommend an acceptable solution – Scott Hodgin Aug 19 '16 at 19:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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