Data SWITCH partition fails with primary key constraint Error

Hi champs,

I am trying to use SWITCH partitions from one fact table out to another identical table. On some tables this does not work as I get an ERROR conserning primary key constraints; is there a way around this without deleting the primary key constraint?

ERROR:

"SWITCH PARTITION 1 TO my_switch_out_table PARTITION 1 " failed with the following error: "ALTER TABLE SWITCH statement failed. SWITCH is not allowed because source table 'my_fact_table' contains primary key for constraint "

/Many thanks

Please post some sample DDL that demonstrates the problem. It will be easier to suggest the solution. You should also take a look at the BOL topic below:

http://msdn2.microsoft.com/en-gb/library/ms191160.aspx

It lists the table, index and constraint requirements for the switch to work.

|||

I have one table that has a two colums as a PK and this table has a PK constraint to one other table and other constraints to 5 other tables.

I've constructed the "OLD_DATA" table as a exact duplicate, including index, of the source table.

However I cannot create the exact same constraints on the destination table, as these already exists in the database.

when I run the following SWITCH, I get an error that

ALTER TABLE dbo.source_table_fact
SWITCH PARTITION 1
TO dbo.OLD_DATA_source_table_fact
PARTITION 1
go

[Execute SQL Task] Error: Executing the query "ALTER TABLE dbo.Ordination_fact SWITCH PARTITION 1 TO dbo.OLD_DATA_MYtable_fact PARTITION 1 " failed with the following error: "ALTER TABLE SWITCH statement failed. SWITCH is not allowed because source table 'dbo.MYtable_fact' contains primary key for constraint 'FK_fact_Mytable_fact'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

This works fine on most of my fact tables but some SWITCHES will not work.

/Many thanks

|||

Here is the script
/* script start */
USE master
go
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'testdb')
BEGIN
ALTER DATABASE [testdb] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE [testdb]
END
go
CREATE DATABASE [testdb]
GO
USE [testdb]
GO

CREATE PARTITION FUNCTION [RangeMonth] (datetime)
AS RANGE RIGHT FOR VALUES (
N'2006-12-01 00:00:00',
N'2007-01-01 00:00:00',
N'2007-02-01 00:00:00',
N'2007-03-01 00:00:00',
N'2007-04-01 00:00:00',
N'2007-05-01 00:00:00'
);
GO

CREATE PARTITION SCHEME [RangeM]
AS PARTITION [RangeMonth] all
TO ([Primary]);

CREATE TABLE dbo.test1
(
A uniqueidentifier NOT NULL,
B uniqueidentifier NULL,
C uniqueidentifier NULL,
ST Datetime NOT NULL,

CONSTRAINT test1_pk
PRIMARY KEY NONCLUSTERED (A ,ST) ON [RangeM] (ST),
) on [RangeM] (ST)
;

CREATE TABLE dbo.Lefttest1
(
A uniqueidentifier NOT NULL,
B uniqueidentifier NULL,
C uniqueidentifier NULL,
ST Datetime NOT NULL,

CONSTRAINT Lefttest1_pk
PRIMARY KEY NONCLUSTERED (A,ST) ON [Primary]
) ON [Primary]
;

CREATE TABLE dbo.test2
(
D uniqueidentifier NOT NULL,
E uniqueidentifier NOT NULL,
F uniqueidentifier NOT NULL,
PartKey Datetime NOT NULL

CONSTRAINT test2_pk
PRIMARY KEY NONCLUSTERED (D,PartKey) ON [RangeM] ([PartKey]),
) ON [RangeM] ([PartKey])
go

CREATE TABLE dbo.Lefttest2
(
D uniqueidentifier NOT NULL,
E uniqueidentifier NOT NULL,
F uniqueidentifier NOT NULL,
PartKey Datetime NOT NULL

CONSTRAINT Lefttest2_pk
PRIMARY KEY NONCLUSTERED (D,PartKey) ON [Primary],
) ON [Primary]

;

CREATE TABLE dbo.test3 (
G uniqueidentifier NOT NULL,
D uniqueidentifier NOT NULL,
PartKey Datetime NOT NULL,
AnotherTime Datetime NOT NULL,

constraint test3_pk
primary key nonclustered (G),

constraint test3_D_PartKey_ref
foreign key (D,PartKey)
references test2(D,PartKey)
on delete cascade
)
;
GO
CREATE TABLE dbo.Lefttest3 (
G uniqueidentifier NOT NULL,
D uniqueidentifier NOT NULL,
PartKey Datetime NOT NULL,
AnotherTime Datetime NOT NULL,

constraint Lefttest3_pk
primary key nonclustered (G) ON [Primary],

constraint Lefttest3_D_PartKey_ref
foreign key (D,PartKey)
references Lefttest2(D,PartKey)
on delete cascade
) ON [Primary]
;
GO

-- Try to switch out the first partition
-- on test1

ALTER TABLE test1
SWITCH PARTITION 1
TO Lefttest1;

-- Try to switch out the first partition
-- on test2
-- Fails with
-- Msg 4967, Level 16, State 1, Line 1
-- ALTER TABLE SWITCH statement failed.
-- SWITCH is not allowed because source table 'testdb.dbo.test2'
-- contains primary key for constraint 'test3_did_ref'.

ALTER TABLE test2
SWITCH PARTITION 1
TO Lefttest2;