Post reply

Partition columns for a unique index must be a subset of the index key.

  • December 8, 2010 at 1:43 am

    #231908

    begin

    -- Add with nocheck Constraint PRIMARY KEY (non-clustered)

    ALTER TABLE DemographicGeo WITH NOCHECK ADD constraint PK_DemographicGeo Primary Key Nonclustered (KeyDemographicGeo)

    end

    SET ARITHABORT ON

    SET CONCAT_NULL_YIELDS_NULL ON

    SET QUOTED_IDENTIFIER ON

    SETANSI_NULLS ON

    SET ANSI_PADDING ON

    SETANSI_WARNINGS ON

    SETNUMERIC_ROUNDABORT OFF

    CREATE UNIQUE INDEX AI_DemographicGeo_10841 ON DemographicGeo(KeyDemographicEntity, DemographicAsOf, DemographicYearReported) WHERE ([UpdOperation]<(2)) WITH (PAD_INDEX=on, FILLFACTOR = 95, MAXDOP = 2 )

    While i am adding a constraint and unique index i am getting below error.

    Msg 1908, Level 16, State 1, Line 5

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

    Msg 1750, Level 16, State 0, Line 5

    Could not create constraint. See previous errors.

    Msg 1908, Level 16, State 1, Line 12

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

    Can anyone guide on this?

    Its really urgent.

    Thanks

  • December 8, 2010 at 2:25 am

    #1259948

    It looks like your table is Partitioned so the Primary Key and Unique index must contain the partitioning column 'KeyGeographicType'. Try the following.

    ALTER TABLE DemographicGeo WITH NOCHECK ADD constraint PK_DemographicGeo Primary Key Nonclustered (KeyGeographicType, KeyDemographicGeo)

    CREATE UNIQUE INDEX AI_DemographicGeo_10841 ON DemographicGeo(KeyGeographicType, KeyDemographicEntity, DemographicAsOf, DemographicYearReported) WHERE ([UpdOperation]<(2)) WITH (PAD_INDEX=on, FILLFACTOR = 95, MAXDOP = 2 )

Viewing 2 posts - 1 through 1 (of 1 total)

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