Любой разработчик, который рано или поздно начинает работать с секционированными таблицами, может столкнуться при переключении секций на ошибку в формате
Msg 4972, Level 16, State 1, Line 1
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].
Сейчас я покажу одну из самых частых причин подобных ошибок!
Для начала создадим тестовую функцию и схему секционирования, в которой будет всего 5 "отрезков" с данными, которые мы будем хранить:
create partition function pf_dt ( datetime )
as range left for values ( '20140101', '20140102', '20140103' );
go
create partition scheme ps_dt
as partition pf_dt all to ([primary]);
go
Т.к. у нас ранжирование LEFT, то схематически наше секционирование можно представить в виде рисунка ниже:
Точки, по которым идёт разбиение на секции буду принадлежать левому отрезку (опять же, т.к. у нас range left), кроме того, все строки, которым соответствуют значения NULL столбца секционирования, располагаются в самой левой секции, кроме случая, когда задано пустое граничное значение и параметр RIGHT. В данном случае самая левая секция является пустой, и в нее помещаются значения NULL.
Теперь создадим тестовую таблицу на нашей схеме секционирования:
create table tbl_test( id int, dt datetime, val varchar(50) ) on ps_dt(dt);
go
И создадим временную таблицу без схемы, которую мы попытаемся переключить в нашу секционированную таблицу. Т.к. мы помним, что необходимо создать на этой таблице ограничение (check constraint), чтобы данные соответствовали только одной секции, то добавим и его:
create table tbl_test_tmp( id int, dt datetime, val varchar(50) );
go
--Ограничение, которое соответствует секции №3 - dt > '20140102' and dt <= '20140103'
alter table tbl_test_tmp
add constraint cnt check ( dt > '20140102' and dt <= '20140103' );
go
После этого, даже не наполняя данными наши тестовые таблицы попробуем переключить данные (switch):
alter table tbl_test_tmp
switch to tbl_test partition 3;
go
В результате мы видим ошибку:
Msg 4972, Level 16, State 1, Line 1
ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table 'tempdb.dbo.tbl_test_tmp' allows values that are not allowed by check constraints or partition function on target table 'tempdb.dbo.tbl_test'.
Но в чём же дело? Ведь мы создали констрейнт с указанием верного интервала? Проблема в значениях NULL, которые, как я сказал выше, всегда попадают в самую левую секцию, а наше ограничение на временной таблице позволяет хранить эти самые значения NULL, т.е. мы нарушаем ограничения функции секционирования, поэтому пересоздадим наше ограничение:
alter table tbl_test_tmp
drop constraint cnt;
go
alter table tbl_test_tmp
add constraint cnt check ( dt > '20140102' and dt <= '20140103' and dt is not null ); --Добавил проверку на IS NOT NULL
go
И попробуем теперь сделать переключение:
alter table tbl_test_tmp
switch to tbl_test partition 3;
go
Бинго! Command(s) completed successfully.
Очистка за собой:
--Удаляем объекты
drop table dbo.tbl_20140101, dbo.tbl_20140101_tmp;
go
drop partition scheme ps_dt;
go
drop partition function pf_dt;
go
Подписаться