Post reply

Identity & datetime Partition dont go along?

  • 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

  • June 9, 2006 at 11:47 am

    #642870

    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

     

  • December 14, 2006 at 1:23 am

    #678010

    Hello Danielle,

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

    Thanks,

    Dhivya

     

     

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

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