x

Can I partition table which is referenced by another table.

Hi,

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.

Thanks, Shailendra

more ▼

asked Dec 15, 2009 at 07:32 AM in Default

Shailendra gravatar image

Shailendra
24 3 3 3

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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:

Source and target tables must have the same FOREIGN KEY constraints. If the target table has any FOREIGN KEY constraints, the source table must have the same foreign keys defined on the corresponding columns, and these foreign keys must reference the same primary key as those of the target table. The foreign keys of the source table cannot be marked is_not_trusted (viewable in the sys.foreign_keys catalog view), unless the corresponding foreign key of the target table is also marked is_not_trusted. For more information about this setting, see Guidelines for Disabling Indexes. SQL Server applies any CASCADE rules that are defined on the foreign keys of the target table to the newly moved partition.

But if you plan to do fast "partition switching" you have some additional requirements regarding foreign keys:

  • No primary key/foreign keys defined if source table has the primary key.
    There can be no active primary
    key/foreign key relationship between
    the source table and the target table in which the source table holds the
    primary key.
  • No primary key/foreign keys defined if target table has the foreign key.
    There can be no active primary
    key/foreign key relationship between
    the source table and the target table in which the target table holds the
    foreign key.
  • No foreign key from another table can reference the source table. The source table cannot be referenced by
    a foreign key in another table.

As you can see, in this case, you can't use FK relations.

reference : http://technet.microsoft.com/en-us/library/ms191160.aspx

more ▼

answered Jan 13, 2010 at 05:55 AM

Håkan Winther gravatar image

Håkan Winther
15.8k 35 37 48

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Dec 15, 2009 at 09:39 AM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Dec 16, 2009 at 04:15 AM

Shailendra gravatar image

Shailendra
24 3 3 3

Ha, I only just saw this. Basically, you can run DDL statements in a transaction just the same as DML statements. So you would need to begin a transaction, drop conflicting constraints, do the partitioning, re-create the constraints, commit the transaction.
Jan 13, 2010 at 09:08 AM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x53

asked: Dec 15, 2009 at 07:32 AM

Seen: 3631 times

Last Updated: Dec 15, 2009 at 07:32 AM