Understanding of Partitioning, Primary Key and Foreign Key

Question

Post reply

Understanding of Partitioning, Primary Key and Foreign Key

Tobias Kiemes

SSCommitted

Points: 1604
More actions
October 10, 2008 at 3:07 am

#194284

Hello,

let's assume I have 2 tables with a master detail relationship. The master table has a surrogate primary key which is referenced by the details foreign key.

master

-------

master_id (PK)

period

c1

c2

detail

------

fk_master (FK)

c3

Further the master table is big and could by partitioned by its column period.

When I do this, I have to extend the primary key to (master_id, period) which i don't understand. After this their is no matching criteria for the foreign key.

Is this right or is there something i miss?

Thanks,

Tobias

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

smithstj SSC Enthusiast Points: 124 More actions · Answer 1

I'm not sure I understand. You're saying in order to partition on Period you have to include it in the primary key? Why do you think that? The partition can be defined on any column, and it need not be a primary key or have any index on it at all. Are you getting an error when you try to do the partition? How big is the table you want to partition?

Chris Harshman SSC-Forever Points: 42179 More actions · Answer 2

I think the problem might be that the primary key is a CLUSTERED index. Since a clustered index and partitioning both relate to how the data is physically stored, then the clustered index needs the period column to determine what partition to store the row in.

One thing you can try is make your primary key that is on master_id NONCLUSTERED, and then create a separate CLUSTERED index on the period column.

Tobias Kiemes SSCommitted Points: 1604 More actions · Answer 3

Hi,

i tried to build the table with a nonclustered primary key, but it still doesn't work.



create table partitiontest(

   test_id int identity(1,1) not null

 , period datetime not null

 , CONSTRAINT [pk_test] PRIMARY KEY NONCLUSTERED

(

	[test_id] ASC

)

) ON [ps_stichtag](period)

Msg 1908, Level 16, State 1, Line 1

Column 'period' is partitioning column of the index 'pk_test'. Partition columns for a unique index must be a subset of the index key.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint. See previous errors.