CODEWITHSUNDEEP
sql-server-2008foreign-keyspartitioning
DeadZone
DeadZone

Reputation: 1690

SQL Server: Horizontal Partitioning vs. Foreign Keys

So I recently began working for an established client. They have an existing database and they want me to set up a partitioning scheme in their development database so that they can see the impact it has on performance. (They believe that it will improve performance to "archive" old data by moving it to another partition. From my analysis, I don't believe that the performance will improve in any significant way. But they want me to move ahead, anyway.) And so I've come to a situation where I have TableA and TableB with a Many to Many relationship. The Join table between them, we'll call TableJ.

TableA has a clustered primary key on a uniqueidentifier column. And so TableJ has a Foreign Key constraint that references TableA.IDColumn.

So here's my dilemma... In order to partition TableA on a partition column (we'll call it PartCol) PartCol needs to be included in the clustered index. My plan was to remove the clustered primary key on IDColumn and replace it with a nonclustered primary key on PartCol and IDColumn, then create a clustered index on PartCol in order to establish the horizontal partitioning. In order to do all that, I have to drop the Foreign Keys that reference TableA.IDColumn and then recreate them when I'm done. But when I try to recreate the FK Constraint, I get an error that looks like:

Msg 1776, Level 16, State 0, Line 1 There are no primary or candidate keys in the referenced table 'MySchema.TableA' that match the referencing column list in the foreign key 'FK_TableJ_TableA'.

Which makes sense, since my key is no longer defined as being unique on IDColumn. Because of the way partitioning works, I can't have a unique constraint that does not include the partition column. But if that's the case, then how do I support tables, like these, that have a M-to-M relationship? It seems to me that this would be a common issue. So I'm guessing that there's a solution that I've overlooked.

Thanks, in advance.

Additional notes, in case people need to know: The largest of these tables have 20M+ records. Not so big that partitioning needs to be implemented, but not so small as to immediately conclude that it's unwise. Also, I have about 20 tables on which I'll be trying to implement partitioning. And this is for SQL Server 2008 R2.

Upvotes: 1

Views: 1060

Answers (1)

DeadZone
DeadZone

Reputation: 1690

Okay, it looks like my only course of action is to create all my unique indexes/constraints on the [PRIMARY] filegroup instead of on the partition scheme. (Thus making them non-aligned indexes, which will interfere with partition switching, if we ever decide to implement partition switching. But it looks like that's not a concern in my case.) Then I can rebuild my FK constraints exactly like they were before the partitioning.

I'm not going to mark this as the answer, yet, since I don't particularly like it and am hoping that someone else out there has a better solution.

Upvotes: 1

Related Questions