or
sql-server
swasheck imported from SE
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'.

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.
Top Answer
Paul White
The thing about `CHECK` constraints is they only disallow rows for which the predicate returns `FALSE`. If the check returns `UNKNOWN`, that is not `FALSE`, so the row passes the check:

    CREATE TABLE dbo.T1 (id int NULL CHECK (id = 1));

    INSERT dbo.T1 VALUES (1); -- Ok
    INSERT dbo.T1 VALUES (2); -- Error
    INSERT dbo.T1 VALUES (NULL); -- Ok!

Your check constraint does not disallow `NULL` values, which is the out-of-range 'value' the `SWITCH` statement is objecting to. Your switch-in table might contain nulls, which do not belong in partition 2.

Add `AND source_id IS NOT NULL` to your `CHECK` constraint, when the destination partition is not partition 1 (where the nulls go).
Switching Data In Fails with “allows values that are not allowed by check constraints or partition function on target table”

This is a dedicated room for discussion about this question.

Once logged in you can direct comments to the question poster (or any answer poster) here.