まとめ
MySQL
主キーにパーティションキーを含める必要があり、異なるパーティションに同じ主キーのデータは投入不可
Oracle
主キーにパーティションキーを含める必要があり、異なるパーティションに同じ主キーのデータは投入不可
PostgreSQL(トリガー型)
親テーブルの主キーは機能しておらず、異なるパーティションに同じ主キーのデータを投入できる
PostgreSQL(宣言型)
親テーブルから主キーを継承させる場合、主キーにパーティションキーを含める必要があり、異なるパーティションに同じ主キーのデータは投入不可
親テーブルから主キーを継承させない場合、主キーにパーティションキーを含める必要はなく、異なるパーティションに同じ主キーのデータを投入できる
SQL Server
主キーにパーティションキーを含める必要があり、異なるパーティションに同じ主キーのデータは投入不可
(5.6)
drop table tab1;
create table tab1
( col1 int
, col2 int
, col3 int
)
partition by range (col3)
( partition p1 values less than (100)
, partition p2 values less than (200)
, partition p3 values less than (300)
, partition pmax values less than (maxvalue)
);
create unique index ind1 on tab1(col1,col2);
→ERROR 1503 (HY000): A UNIQUE INDEX must include all columns in the table's partitioning function
主キーにパーティションキーを含める必要がある
create unique index ind1 on tab1(col1,col2,col3);
create index ind2 on tab1(col2);
alter table tab1 add constraint tab1pk primary key (col1,col2,col3);
→
ユニークインデックスが重複する
select * from tab1;
select * from tab1 partition (p1);
select * from tab1 partition (p2);
select * from tab1 partition (p3);
select * from tab1 partition (pmax);
insert into tab1 values(1,1,0);
insert into tab1 values(1,2,100);
insert into tab1 values(1,3,200);
insert into tab1 values(1,4,300);
→
異なるパーティションに同じ主キーのデータは投入不可
(12cR1)
drop table tab1 purge;
create table tab1
( col1 int
, col2 int
, col3 int
)
partition by range (col3)
( partition p1 values less than (100)
, partition p2 values less than (200)
, partition p3 values less than (300)
, partition pmax values less than (maxvalue)
);
create unique index ind1 on tab1(col1,col2) local;
→
ORA-14039: パーティション化列はUNIQUE索引のキー列のサブセットを構成する必要があります
パーティションテーブルにユニークローカルインデックスを作成するためにはパーティションキーを含める必要がある
create unique index ind1 on tab1(col1,col2,col3) local;
create index ind2 on tab1(col2) local;
alter table tab1 add constraint tab1pk primary key (col1,col2,col3) using index ind1;
select * from tab1;
select * from tab1 partition (p1);
select * from tab1 partition (p2);
select * from tab1 partition (p3);
select * from tab1 partition (pmax);
insert into tab1 values(1,1,0);
insert into tab1 values(1,2,100);
insert into tab1 values(1,3,200);
insert into tab1 values(1,4,300);
commit;
→
異なるパーティションに同じ主キーのデータは投入不可
(9.4)
drop table tab1 cascade;
create table tab1
( col1 int
, col2 int
, col3 int
);
create table tab1_p1 ( check (col3 < 100 ) ) inherits (tab1);
create table tab1_p2 ( check (col3 >= 100 and col3 < 200) ) inherits (tab1);
create table tab1_p3 ( check (col3 >= 200 and col3 < 300) ) inherits (tab1);
create table tab1_pmax ( check (col3 >= 300 ) ) inherits (tab1);
create unique index ind1 on tab1(col1,col2);
create unique index ind1_p1 on tab1_p1(col1,col2);
create unique index ind1_p2 on tab1_p2(col1,col2);
create unique index ind1_p3 on tab1_p3(col1,col2);
create unique index ind1_pmax on tab1_pmax(col1,col2);
create index ind2 on tab1(col2);
create index ind2_p1 on tab1_p1(col2);
create index ind2_p2 on tab1_p2(col2);
create index ind2_p3 on tab1_p3(col2);
create index ind2_pmax on tab1_pmax(col2);
alter table tab1 add constraint tab1pk primary key using index ind1;
alter table tab1_p1 add constraint tab1_p1pk primary key using index ind1_p1;
alter table tab1_p2 add constraint tab1_p2pk primary key using index ind1_p2;
alter table tab1_p3 add constraint tab1_p3pk primary key using index ind1_p3;
alter table tab1_pmax add constraint tab1_pmaxpk primary key using index ind1_pmax;
create or replace function tab1_func()
returns trigger as $$
begin
if ( new.col3 < 100 ) then insert into tab1_p1 values (new.*);
elsif ( new.col3 >= 100 and new.col3 < 200) then insert into tab1_p2 values (new.*);
elsif ( new.col3 >= 200 and new.col3 < 300) then insert into tab1_p3 values (new.*);
else insert into tab1_pmax values (new.*);
end if;
return null;
end;
$$
language plpgsql;
create trigger tab1_trigger
before insert on tab1
for each row execute procedure tab1_func();
select * from tab1;
select * from tab1_p1;
select * from tab1_p2;
select * from tab1_p3;
select * from tab1_pmax;
insert into tab1 values(1,1,0);
insert into tab1 values(1,2,100);
insert into tab1 values(1,3,200);
insert into tab1 values(1,4,300);
insert into tab1 values(1,1,100);
→
異なるパーティションに同じ主キーのデータを投入可能
親テーブルに作成した主キー制約は機能していない
(11)
[1]親テーブルに主キー制約を設定する場合
drop table tab1 cascade;
create table tab1
( col1 int
, col2 int
, col3 int
)
partition by range(col3);
create table tab1_p1 partition of tab1 for values from (minvalue) to (100);
create table tab1_p2 partition of tab1 for values from (100) to (200);
create table tab1_p3 partition of tab1 for values from (200) to (300);
create table tab1_pmax partition of tab1 for values from (300) to (maxvalue);
create unique index ind1 on tab1(col1,col2);
→
ERROR: insufficient columns in UNIQUE constraint definition
DETAIL: UNIQUE constraint on table "tab1" lacks column "col3" which is part of the partition key.
パーティションテーブルにユニークインデックスを作成するためにはパーティションキーを含める必要がある
create unique index ind1 on tab1(col1,col2,col3);
→
親テーブルにインデックスを作成すると自動で子テーブルにもインデックスが作成される
create index ind2 on tab1(col2);
alter table tab1 add constraint tab1pk primary key using index ind1;
→
ERROR: ALTER TABLE / ADD CONSTRAINT USING INDEX is not supported on partitioned tables
パーティションテーブルの場合、using indexによる主キー制約作成ができない
alter table tab1 add constraint tab1pk primary key(col1,col2,col3);
→
ユニークインデックスが重複する
select * from tab1;
select * from tab1_p1;
select * from tab1_p2;
select * from tab1_p3;
select * from tab1_pmax;
insert into tab1 values(1,1,0);
insert into tab1 values(1,2,100);
insert into tab1 values(1,3,200);
insert into tab1 values(1,4,300);
→
異なるパーティションに同じ主キーのデータは投入不可
[2]親テーブルに主キー制約を設定しない場合
drop table tab1 cascade;
create table tab1
( col1 int
, col2 int
, col3 int
)
partition by range(col3);
create table tab1_p1 partition of tab1 for values from (minvalue) to (100);
create table tab1_p2 partition of tab1 for values from (100) to (200);
create table tab1_p3 partition of tab1 for values from (200) to (300);
create table tab1_pmax partition of tab1 for values from (300) to (maxvalue);
create unique index ind1_p1 on tab1_p1(col1,col2);
create unique index ind1_p2 on tab1_p2(col1,col2);
create unique index ind1_p3 on tab1_p3(col1,col2);
create unique index ind1_pmax on tab1_pmax(col1,col2);
create index ind2_p1 on tab1_p1(col2);
create index ind2_p2 on tab1_p2(col2);
create index ind2_p3 on tab1_p3(col2);
create index ind2_pmax on tab1_pmax(col2);
alter table tab1_p1 add constraint tab1_p1pk primary key using index ind1_p1;
alter table tab1_p2 add constraint tab1_p2pk primary key using index ind1_p2;
alter table tab1_p3 add constraint tab1_p3pk primary key using index ind1_p3;
alter table tab1_pmax add constraint tab1_pmaxpk primary key using index ind1_pmax;
select * from tab1;
select * from tab1_p1;
select * from tab1_p2;
select * from tab1_p3;
select * from tab1_pmax;
insert into tab1 values(1,1,0);
insert into tab1 values(1,2,100);
insert into tab1 values(1,3,200);
insert into tab1 values(1,4,300);
insert into tab1 values(1,1,100);
→
異なるパーティションに同じ主キーのデータを投入可能
(2014)
use master
go
alter database test add filegroup fg1;
alter database test add filegroup fg2;
alter database test add filegroup fg3;
alter database test add filegroup fg4;
alter database test
add file
(
name = fg1,
filename = 'c:\fg\fg1.ndf',
size = 5mb
)
to filegroup fg1;
alter database test
add file
(
name = fg2,
filename = 'c:\fg\fg2.ndf',
size = 5mb
)
to filegroup fg2;
alter database test
add file
(
name = fg3,
filename = 'c:\fg\fg3.ndf',
size = 5mb
)
to filegroup fg3;
alter database test
add file
(
name = fg4,
filename = 'c:\fg\fg4.ndf',
size = 5mb
)
to filegroup fg4;
use test
go
drop table tab1;
go
drop partition scheme pscheme1;
go
drop partition function pfunc1;
go
create partition function pfunc1 (int)
as range right for
values (100, 200, 300)
go
create partition scheme pscheme1
as partition pfunc1
to (fg1, fg2, fg3, fg4)
go
create table tab1
( col1 int not null
, col2 int not null
, col3 int not null
) on pscheme1(col3)
;
go
create unique index ind1 on tab1(col1,col2);
→列 'col3' はインデックス 'ind1' のパーティション分割列です。一意インデックスのパーティション列は、インデックス キーのサブセットにしてください。
主キーにパーティションキーを含める必要がある
create unique index ind1 on tab1(col1,col2,col3);
create index ind2 on tab1(col2);
alter table tab1 add constraint tab1pk primary key(col1,col2,col3);
→
ユニークインデックスが重複する
insert into tab1 values(1,1,0);
insert into tab1 values(1,2,100);
insert into tab1 values(1,3,200);
insert into tab1 values(1,4,300);
select *, $partition.pfunc1(col3) as [パーティション番号] from tab1
→
異なるパーティションに同じ主キーのデータは投入不可
