まとめ
MySQL
主キーにパーティションキーを含める必要があり、異なるパーティションに同じ主キーのデータは投入不可
Oracle
主キーにパーティションキーを含める必要があり、異なるパーティションに同じ主キーのデータは投入不可
PostgreSQL(トリガー型)
親テーブルの主キーは機能しておらず、異なるパーティションに同じ主キーのデータを投入できる
PostgreSQL(宣言型)
親テーブルから主キーを継承させる場合、主キーにパーティションキーを含める必要があり、異なるパーティションに同じ主キーのデータは投入不可
親テーブルから主キーを継承させない場合、主キーにパーティションキーを含める必要はなく、異なるパーティションに同じ主キーのデータを投入できる
(5.6)
drop table tab1;
create table tab1
( col1 int
, col2 int
, col3 int
)
partition by list (col3)
( partition p1 values in (100)
, partition p2 values in (200)
, partition p3 values in (300)
);
※defaultパーティション作成不可
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);
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 list (col3)
( partition p1 values (100)
, partition p2 values (200)
, partition p3 values (300)
, partition pmax values (default)
);
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 in (100)) ) inherits (tab1);
create table tab1_p2 ( check (col3 in (200)) ) inherits (tab1);
create table tab1_p3 ( check (col3 in (300)) ) inherits (tab1);
create table tab1_pmax ( check (col3 not in (100,200,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 in (100) ) then insert into tab1_p1 values (new.*);
elsif ( new.col3 in (200) ) then insert into tab1_p2 values (new.*);
elsif ( new.col3 in (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 list(col3);
create table tab1_p1 partition of tab1 for values in (100);
create table tab1_p2 partition of tab1 for values in (200);
create table tab1_p3 partition of tab1 for values in (300);
create table tab1_pmax partition of tab1 default;
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 list(col3);
create table tab1_p1 partition of tab1 for values in (100);
create table tab1_p2 partition of tab1 for values in (200);
create table tab1_p3 partition of tab1 for values in (300);
create table tab1_pmax partition of tab1 default;
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)
リストパーティションはサポートしていない