ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table xxx allows values that are not allowed by check constraints or partition function on target table yyy.

Nov 12, 2012 at 4:05 PM
Edited Nov 12, 2012 at 6:50 PM

Can someone please help? I have googled and found that the constraint 'is not null' is required as well as the constraint on the column.


My partition function and scheme:
CREATE PARTITION FUNCTION [ItemsArchivePF1](int) AS RANGE right FOR VALUES (20111006)
GO
CREATE PARTITION SCHEME [ItemsArchivePS1]
AS PARTITION [ItemsArchivePF1] ALL TO ([PRIMARY])
GO

 My partitioned table's DROP/DEFINE
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblItemsArchive](
 [ItemDateId] [int] NOT NULL,
 [ItemSourceBank] [char](3) NOT NULL,
 [ItemFileId] [int] NOT NULL,
 [ItemId] [bigint]  identity(1,1) NOT NULL,
 [ItemDocType] [char](1) NOT NULL,
 [ItemIsn] [char](12) NOT NULL,
 [ItemPkt] [char](2) NOT NULL,
 [ItemRejectReason] [char](2) NOT NULL,
...
 [ItemKB_Count] [int] NOT NULL
 CONSTRAINT itemPK PRIMARY KEY CLUSTERED (ItemDateId, ItemSourceBank, ItemId)
) ON [ItemsArchivePS1]([ItemDateId])
WITH
(
DATA_COMPRESSION = PAGE
)

SET ANSI_PADDING OFF
GO
 

ObjectName IndexName IndexID PartitionScheme PartitionNumber FileGroupName LowerBoundaryValue UpperBoundaryValue Range Rows
tblItemsArchive itemPK 1 ItemsArchivePS1 1 PRIMARY NULL 20111006 RIGHT 0
tblItemsArchive itemPK 1 ItemsArchivePS1 2 PRIMARY 20111006 NULL RIGHT 0

When I run PartitionManager, the staging table created does not make the ItemId an identity field, so I manually changed it.
It is important for the ItemId to be an identity field in the daily staging table, but in the archive, it is okay not to be unique.
The constraint generated by PartitionManager reads:
ALTER TABLE [dbo].[staging_tblItemsArchive]  WITH NOCHECK ADD  CONSTRAINT [chk_myStagingTbl_partition_2] CHECK  (([ItemDateId]>=(20111006)))

From googling other people who have had the same experience have added the equivalent of "[ItemDateId is not null and " to the constraint.
I have tried both ways and am going absolutley nuts.
What am I doing wrong?
My tables do not allow nulls in any of the fields.
Can my problem be with those 'ansi_padding' and 'ansi_nulls' statements?
Can my problem be with the identity column?
Should I have the identity field in yet another clone of the table, and copy that into the staging table and then into the archive partition? We will have one partition per day in the final table, never aging any data out. Only adding new data.

 I use SSIS to load the staging table from a flat file, then attempt to switch it to partition 2.

I get:
ALTER TABLE staging_tblItemsArchive SWITCH  TO tblItemsArchive PARTITION 2 ;
Msg 4972, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'PATS.dbo.staging_tblItemsArchive' allows values that are not allowed by check constraints or partition function on target table 'PATS.dbo.tblItemsArchive'.

I am pulling my hair out.


 

Nov 17, 2012 at 4:56 AM

I confirmed that Partition Management tool will always create the constraint WITH CHECK, instead of using NOCHECK, and in your test case that will work.  (Manually editing the DDL to add a WITH NOCHECK to the partition-specific staging table's check constraint prevents SQL Server from verifying that any rows in the staging table meet the constraint condition, and will then not allow a switch.)

Nov 17, 2012 at 12:53 PM

Thank you ….

Eileen Eby

Svc Info Developer IV, Technical Design Team

Hewlett-Packard Company

+44 1327 264 057 / Tel

+44 7768 700 755 / Mobile

eileen.eby@hp.com / Email

44 Farndon Rd

Woodford Halse, Daventry, NN11 3TT

UK

Please consider the environment before printing this email.

From: stuartozer [email removed]
Sent: 17 November 2012 05:57
To: Eby, Eileen
Subject: Re: ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table xxx ... [SQLPartitionMgmt:402894]

From: stuartozer

I confirmed that Partition Management tool will always create the constraint WITH CHECK, instead of using NOCHECK, and in your test case that will work. (Manually editing the DDL to add a WITH NOCHECK to the partition-specific staging table's check constraint prevents SQL Server from verifying that any rows in the staging table meet the constraint condition, and will then not allow a switch.)

Coordinator
Nov 29, 2012 at 3:47 AM

Fixed in version 3.0