リストパーティションテーブルの主キー

まとめ

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)

リストパーティションはサポートしていない