October 10, 2008 at 3:07 am
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
October 10, 2008 at 8:26 am
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?
October 10, 2008 at 9:06 am
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.
October 13, 2008 at 5:05 am
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.
Does this mean, that i have to add the partition criteria to the primary key?
Tobias
October 13, 2008 at 2:40 pm
OK sorry, you're right. I misunderstood the nature of the problem.
October 14, 2008 at 2:58 am
hmm..now I'm a little disappointed from sqlserver.
Thanks for help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply