locked
Partioning Master Detail tables RRS feed

  • Question

  • Hi,

    I'm having trouble creating two partitioned tables that have a foreign key relationship.

    Msg 4967, Level 16, State 1, Line 1
    ALTER TABLE SWITCH statement failed. SWITCH is not allowed because source table 'TestPartitioning.dbo.Master' contains primary key for constraint 'FK_Master'.

    this is my code:

    CREATE PARTITION FUNCTION MasterTypeFN(int)
    	AS RANGE RIGHT FOR VALUES (1,2,3)
    GO
    
    CREATE PARTITION SCHEME MasterTypeScheme 
    AS PARTITION MasterTypeFN 
    	TO ([PRIMARY], [FG1], [FG2], [FG3] )
    GO
    
    CREATE TABLE [dbo].Master
    (
       [MasterID] [int] NOT NULL,
       [MasterTypeID] [int] NOT NULL CONSTRAINT chkMaster CHECK (MasterTypeID IN (1,2,3)),
       PRIMARY KEY (MasterID, MasterTypeID)
    ) 
    ON MasterTypeScheme (MasterTypeID)
    
    CREATE TABLE [dbo].Detail
    (
       DetailID [int] NOT NULL,
       MasterTypeID [int] NOT NULL,
       FKMaster int NOT NULL,
       CONSTRAINT FK_Master FOREIGN KEY (FKMaster, MasterTypeID) REFERENCES [dbo].[Master](MasterID, MasterTypeID)
    ) 
    ON MasterTypeScheme (MasterTypeID)
    GO
    
    
    CREATE TABLE [dbo].MasterType1 ([MasterID] [int] NOT NULL, [MasterTypeID] [int] NOT NULL 
    	CONSTRAINT chkMaster1 CHECK (MasterTypeID = 1), PRIMARY KEY (MasterID, MasterTypeID)) ON [FG1]
    
    
    CREATE TABLE [dbo].DetailType1
    (
       DetailID [int] NOT NULL,
       MasterTypeID [int] NOT NULL,
       FKMaster int NOT NULL,
       CONSTRAINT FK_Master1 FOREIGN KEY (FKMaster, MasterTypeID) REFERENCES [dbo].[Master](MasterID, MasterTypeID),
       CONSTRAINT chkDetail1 CHECK (MasterTypeID = 1)
    ) 
    ON [FG1]
    


    Monday, April 14, 2014 12:56 PM

Answers

  • Hello,
    According to the requirements of move partitions: No foreign key from another table can reference the source table. The source table cannot be referenced by a foreign key in another table.

    Based on the Statement you post above, you should try to remove the forign key which reference the "Master" table. Please refer to the following statements:

    alter table detail drop constraint FK_Master alter table detailtype1 drop constraint FK_Master1

    insert into Master values (1,1) alter table master switch partition 2 to mastertype1 partition 1


    Reference:
    Transferring Data Efficiently by Using Partition Switching

    Regards,
    Fanny Liu


    Fanny Liu
    TechNet Community Support

    Wednesday, April 16, 2014 11:43 AM