I have requirement of partitioning the two tables which have foreign key relationship between them. In some articles I read that table can't be partitioned if referenced by another table.
My requirement is as below
Tab1 and Tab2 needs to be partioned. Tab1 is referenced by Tab2.
Can anyone tell me how can I partitioned both these tables.
asked Dec 15, 2009 at 07:32 AM in Default
It depends on what you want to accomplish with your partitioning. If you don't want "partition switching", you can use FK relations as long as you follow the rule below.
Qoute from books online:
But if you plan to do fast "partition switching" you have some additional requirements regarding foreign keys:
As you can see, in this case, you can't use FK relations.
answered Jan 13, 2010 at 05:55 AM
Depending on how you partition them, you may end up dropping the unique constraint/primary key/unique index on Tab1 which allows Tab2 to reference it. If you drop that, then you will have to end up dropping the foreign key while you do it anyway.
My advice to you would be to try to end up with a script which does the partitioning in a transaction before you roll it out to a production system - that way any failures will be backed out.
answered Dec 15, 2009 at 09:39 AM
Matt Whitfield ♦♦
Hi Matt, Thanks for your advise. What do you mean by script which does the partitioning in a transaction? These tables have continuous bulky inserts; are you suggesting to write a script to remove releationship between them before each insert and recreate it once insert is done? Is there a better way to do it? Thanks, Shailendra
answered Dec 16, 2009 at 04:15 AM