Identity & datetime Partition dont go along?

Question

Post reply

Identity & datetime Partition dont go along?

Eric M.

Ten Centuries

Points: 1098
More actions
May 1, 2006 at 7:20 pm

#167872

I have a SQL Server 2005 table which is partitioned by month. The partition function and scheme works fine...until I try to add primary key cluster to the ID field. The ID field NEEDS to be PK and identity is set to true with increments of 1. When I try to add primary key, it gives the following error.
Msg 1908, Level 16, State 1, Line 1
Column 'EffectiveDate' is partitioning column of the index 'PK_ServiceOrder'. 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.

Here are the scripts

Create table script

CREATE
TABLE [dbo].[ServiceOrder](
[ServiceOrderKey] [int] IDENTITY(1,1) NOT NULL,
[EffectiveDate] [datetime] NULL
CONSTRAINT ServiceOrderMonthlyDateRangeCK
CHECK ([EffectiveDate] >= '20050101' AND [EffectiveDate] <= '20061231'),
ON MonthlyDateRangePScheme(EffectiveDate)
GO
Add PK script
alter
table dbo.serviceorder
add
constraint [PK_ServiceOrder] PRIMARY KEY CLUSTERED ([SERVICEORDERKEY] ASC)

This is when I get the error. Please advise
Thanks
Eric
May 4, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

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

Danielle Nguyen Valued Member Points: 59 More actions · Answer 1

Hello Eric,

I like that name. My 3 year old son's name is Eric too. OK ,try this and see if it will work for you.

alter table dbo.serviceorder

add

constraint [PK_ServiceOrder] PRIMARY KEY CLUSTERED ([SERVICEORDERKEY] ASC, [EffectiveDate] )

for more information check out BOL topic "Special Guidelines for Partitioned Indexes"

Partitioning Clustered Indexes

When partitioning a clustered index, the clustering key must contain the partitioning column. When partitioning a nonunique clustered index, and the partitioning column is not explicitly specified in the clustering key, SQL Server adds the partitioning column by default to the list of clustered index keys. If the clustered index is unique, you must explicitly specify that the clustered index key contain the partitioning column.

Thanks,

Danielle Nguyen | Sr. Database Consultant

Dhivya SSC Rookie Points: 39 More actions · Answer 2

Hello Danielle,

How to set foreign key for the table under partition? Can you help me out to solve this problem.

Thanks,

Dhivya