ALTER TABLE SWITCH statement failed. SWITCH is not allowed because source table '' contains primary key for constraint ''

I'm using SQL Server 2008.

I have two tables PARTITIONTESTTABLE and PARTITIONTESTTABLESUB. PARTITIONTESTTABLESUB has a foreign key to PARTITIONTESTTABLE. I created partition and both of these two tables are partitioned. I also created staging table from Managing partition wizard.

When I run....

ALTER TABLE  PARTITIONTESTTABLESUB SWITCH PARTITION 10 to [staging_PARTITIONTESTTABLESUB];

...It runs to success.

But...

ALTER TABLE  PARTITIONTESTTABLE SWITCH PARTITION 10 to [staging_PARTITIONTESTTABLE];

...Failed with error:


ALTER TABLE SWITCH statement failed. SWITCH is not allowed because
source table 'Enterprise.MSH2.PARTITIONTESTTABLE' contains primary key
for constraint
'staging_PARTITIONTESTTABLESUB_PARTITIONTESTTABLESUB_FK'.

DDL:

``
CREATE TABLE PARTITIONTESTTABLE NOT NULL,
[datacol1] [int] NULL,
[datacol2] [int] NULL,
[datacol3] varchar NULL,
[partitioncol] [datetime] NOT NULL,
CONSTRAINT [PK_PARTITIONTESTTABLE] PRIMARY KEY CLUSTERED
(
[pkcol] ASC,
[partitioncol] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

CREATE TABLE PARTITIONTESTTABLESUB NOT NULL,
[fkcol] [int] NOT NULL,
[datacol1] [int] NULL,
[datacol2] [int] NULL,
[datacol3] varchar NULL,
[partitioncol] [datetime] NOT NULL,
CONSTRAINT [PK_PARTITIONTESTTABLESUB] PRIMARY KEY CLUSTERED
(
[subpkcol] ASC,
[partitioncol] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [PARTITIONTESTTABLESUB] WITH CHECK ADD CONSTRAINT [PARTITIONTESTTABLESUB_FK] FOREIGN KEY([fkcol], [partitioncol])
REFERENCES [PARTITIONTESTTABLE] ([pkcol], [partitioncol])
GO

ALTER TABLE [MSH2].[PARTITIONTESTTABLESUB] CHECK CONSTRAINT [PARTITIONTESTTABLESUB_FK]
GO
`

Correct. You can't do a switch out if there's another table which is dependent on the switching table with a FK.

Imagine that you have PK values 1,2,3, and a foreign key into it. Now you want to switch out value 3, so it'll have to check that 3 isn't mentioned in the FK table - that's a large amount of work, and a lot more than the metadata operation that partition switching is meant to be.

...so it's not allowed. Do it without FKs.

StackExchange Database Administrators Q#114927, answer score: 7