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]