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,
g

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).

StackExchange Database Administrators Q#76232, answer score: 22