Sql-server – ALTER TABLE SWITCH statement failed. SWITCH is not allowed because source table ” contains primary key for constraint ”

partitioningsql server

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](
    [pkcol] [int] IDENTITY(1,1) NOT NULL,
    [datacol1] [int] NULL,
    [datacol2] [int] NULL,
    [datacol3] [varchar](50) 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](
    [subpkcol] [int] IDENTITY(1,1) NOT NULL,
    [fkcol] [int] NOT NULL,
    [datacol1] [int] NULL,
    [datacol2] [int] NULL,
    [datacol3] [varchar](50) 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

Best Answer

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.

Related Question