3 edited tags
| link
    Tweeted twitter.com/#!/StackDBAs/status/509988983187578880
2 added 190 characters in body
source | link

Running:

select target_partition_id = $PARTITION.pf_f_word_source_id(source_id), 
    *
from dbo.f_word_swapin;

verifies that all data should go back into partition 13.

I'm really pretty new to partitioning so I'm sure that I'm doing things incorrectly, I just don't know what it is.

I'm really pretty new to partitioning so I'm sure that I'm doing things incorrectly, I just don't know what it is.

Running:

select target_partition_id = $PARTITION.pf_f_word_source_id(source_id), 
    *
from dbo.f_word_swapin;

verifies that all data should go back into partition 13.

I'm really pretty new to partitioning so I'm sure that I'm doing things incorrectly, I just don't know what it is.

1
source | link

Switching Data In Fails with "allows values that are not allowed by check constraints or partition function on target table"

Given the following

-- table ddl
create table dbo.f_word(
    sentence_id int NULL,
    sentence_word_id int NULL,
    word_id int NULL,
    lemma_id int NULL,
    source_id int NULL,
    part_of_speech_id int NULL,
    person_id int NULL,
    gender_id int NULL,
    number_id int NULL,
    tense_id int NULL,
    voice_id int NULL,
    mood_id int NULL,
    case_id int NULL,
    degree_id int NULL,
    citation nvarchar(100) NULL
);
-- create partition function
create partition function pf_f_word_source_id (int)
as range left for values 
(
    1,2,3,4,5,6,7,8,9,10,11,12,13,14,
    15,16,17,18,19,20,21,22,23
);

-- create the partition scheme
create partition scheme ps_f_word as partition pf_f_word_source_id to 
(
    [primary],[primary],[primary],[primary],[primary],[primary],[primary],[primary],[primary],
    [primary],[primary],[primary],[primary],[primary],[primary],[primary],[primary],[primary],
    [primary],[primary],[primary],[primary],[primary],[primary]
);

-- partition the index
create unique clustered index cix_fword on dbo.f_word 
(
    source_id,
    sentence_id,
    sentence_word_id,
    word_id,
    lemma_id,
    part_of_speech_id,
    person_id,
    gender_id,
    number_id,
    tense_id,
    voice_id,
    mood_id,
    case_id,
    degree_id 
)
on ps_f_word (source_id);

-- swapin table ddl

create table dbo.f_word_swapin(
    sentence_id int NULL,
    sentence_word_id int NULL,
    word_id int NULL,
    lemma_id int NULL,
    source_id int NULL,
    part_of_speech_id int NULL,
    person_id int NULL,
    gender_id int NULL,
    number_id int NULL,
    tense_id int NULL,
    voice_id int NULL,
    mood_id int NULL,
    case_id int NULL,
    degree_id int NULL,
    citation nvarchar(100) NULL
) on [primary];

-- create the same index on the swapin table
create unique clustered index cix_fword_swapin on dbo.f_word_swapin 
(
    source_id,
    sentence_id,
    sentence_word_id,
    word_id,
    lemma_id,
    part_of_speech_id,
    person_id,
    gender_id,
    number_id,
    tense_id,
    voice_id,
    mood_id,
    case_id,
    degree_id 
);

-- add check constraints WITH CHECK
ALTER TABLE dbo.f_word_swapin
WITH CHECK
ADD CONSTRAINT ck_f_word_swapin_lb
CHECK ( source_id > 12);

ALTER TABLE dbo.f_word_swapin
WITH CHECK
ADD CONSTRAINT ck_f_word_swapin_ub
CHECK ( source_id <= 13);

Then, move the data around:

-- switch data OUT of the partitioned table
ALTER TABLE dbo.f_word
SWITCH PARTITION 13 TO dbo.f_word_swapin;

-- attempt to switch data back IN 
ALTER TABLE dbo.f_word_swapin
SWITCH TO dbo.f_word PARTITION 13;

Below is the "Script Table As ... CREATE" DDL just to verify the same table structures.

/****** Object:  Table [dbo].[f_word_swapin]    Script Date: 9/10/2014 10:01:01 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[f_word_swapin](
    [sentence_id] [int] NULL,
    [sentence_word_id] [int] NULL,
    [word_id] [int] NULL,
    [lemma_id] [int] NULL,
    [source_id] [int] NULL,
    [part_of_speech_id] [int] NULL,
    [person_id] [int] NULL,
    [gender_id] [int] NULL,
    [number_id] [int] NULL,
    [tense_id] [int] NULL,
    [voice_id] [int] NULL,
    [mood_id] [int] NULL,
    [case_id] [int] NULL,
    [degree_id] [int] NULL,
    [citation] [nvarchar](100) NULL
) ON [PRIMARY]

/****** Object:  Table [dbo].[f_word]    Script Date: 9/10/2014 10:09:43 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[f_word](
    [sentence_id] [int] NULL,
    [sentence_word_id] [int] NULL,
    [word_id] [int] NULL,
    [lemma_id] [int] NULL,
    [source_id] [int] NULL,
    [part_of_speech_id] [int] NULL,
    [person_id] [int] NULL,
    [gender_id] [int] NULL,
    [number_id] [int] NULL,
    [tense_id] [int] NULL,
    [voice_id] [int] NULL,
    [mood_id] [int] NULL,
    [case_id] [int] NULL,
    [degree_id] [int] NULL,
    [citation] [nvarchar](100) NULL
)

GO

SWITCHing OUT works just fine. SWITCHing IN produces the following error:

Msg 4972, Level 16, State 1, Line 1 ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'greek.dbo.f_word_swapin' allows values that are not allowed by check constraints or partition function on target table 'greek.dbo.f_word'.

I'm really pretty new to partitioning so I'm sure that I'm doing things incorrectly, I just don't know what it is.