4

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
7

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.