sql-server add tag
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).

This room is for discussion about this question.

Once logged in you can direct comments to any contributor here.

Enter question or answer id or url (and optionally further answer ids/urls from the same question) from

Separate each id/url with a space. No need to list your own answers; they will be imported automatically.