达梦数据库分区表--局部唯一索引的限制

DM数据库分区表有这么一个限制：局部唯一索引必须包含全部分区列.

SQL> create table range_part_tab (id number,deal_date date,area_code number,nbr number,contents varchar2(4000))

2 partition by range (deal_date)
3 (
4 partition p_201801 values less than (TO_DATE(‘2018-02-01’, ‘YYYY-MM-DD’)),
5 partition p_201802 values less than (TO_DATE(‘2018-03-01’, ‘YYYY-MM-DD’)),
6 partition p_201803 values less than (TO_DATE(‘2018-04-01’, ‘YYYY-MM-DD’)),
7 partition p_201804 values less than (TO_DATE(‘2018-05-01’, ‘YYYY-MM-DD’)),
8 partition p_201805 values less than (TO_DATE(‘2018-06-01’, ‘YYYY-MM-DD’)),
9 partition p_201806 values less than (TO_DATE(‘2018-07-01’, ‘YYYY-MM-DD’)),
10 partition p_201807 values less than (TO_DATE(‘2018-08-01’, ‘YYYY-MM-DD’)),
11 partition p_201808 values less than (TO_DATE(‘2018-09-01’, ‘YYYY-MM-DD’)),
12 partition p_201809 values less than (TO_DATE(‘2018-10-01’, ‘YYYY-MM-DD’)),
13 partition p_201810 values less than (TO_DATE(‘2018-11-01’, ‘YYYY-MM-DD’)),
14 partition p_201811 values less than (TO_DATE(‘2018-12-01’, ‘YYYY-MM-DD’)),
15 partition p_201812 values less than (TO_DATE(‘2019-01-01’, ‘YYYY-MM-DD’)),
16 partition p_201901 values less than (TO_DATE(‘2019-02-01’, ‘YYYY-MM-DD’)),
17 partition p_201902 values less than (TO_DATE(‘2019-03-01’, ‘YYYY-MM-DD’)),
18 partition p_max values less than (maxvalue)
19 )
20 ;

操作已执行

已用时间: 102.731(毫秒). 执行号:12.

SQL> alter table range_part_tab add constraint range_tab_pk primary key (id) ;

alter table range_part_tab add constraint range_tab_pk primary key (id) ;

第1 行附近出现错误[-2683]:局部唯一索引必须包含全部分区列.

已用时间: 0.897(毫秒). 执行号:0.

在达梦数据库里，唯一索引必须被包含在分区列中，所有这个主键就设置失败了。

上述是分区表建完后改id列为主键报错，如果直接建表的时候设置primary，也必然报错

drop table range_part_tab;

create table range_part_tab (id number primary key ,deal_date date,area_code number,nbr number,contents varchar2(4000))
partition by range (deal_date)
(
partition p_201801 values less than (TO_DATE(‘2018-02-01’, ‘YYYY-MM-DD’)),
partition p_201802 values less than (TO_DATE(‘2018-03-01’, ‘YYYY-MM-DD’)),
partition p_201803 values less than (TO_DATE(‘2018-04-01’, ‘YYYY-MM-DD’)),
partition p_201804 values less than (TO_DATE(‘2018-05-01’, ‘YYYY-MM-DD’)),
partition p_201805 values less than (TO_DATE(‘2018-06-01’, ‘YYYY-MM-DD’)),
partition p_201806 values less than (TO_DATE(‘2018-07-01’, ‘YYYY-MM-DD’)),
partition p_201807 values less than (TO_DATE(‘2018-08-01’, ‘YYYY-MM-DD’)),
partition p_201808 values less than (TO_DATE(‘2018-09-01’, ‘YYYY-MM-DD’)),
partition p_201809 values less than (TO_DATE(‘2018-10-01’, ‘YYYY-MM-DD’)),
partition p_201810 values less than (TO_DATE(‘2018-11-01’, ‘YYYY-MM-DD’)),
partition p_201811 values less than (TO_DATE(‘2018-12-01’, ‘YYYY-MM-DD’)),
partition p_201812 values less than (TO_DATE(‘2019-01-01’, ‘YYYY-MM-DD’)),
partition p_201901 values less than (TO_DATE(‘2019-02-01’, ‘YYYY-MM-DD’)),
partition p_201902 values less than (TO_DATE(‘2019-03-01’, ‘YYYY-MM-DD’)),
partition p_max values less than (maxvalue)
)
;

第20 行附近出现错误[-2683]:局部唯一索引必须包含全部分区列.

已用时间: 0.527(毫秒). 执行号:0.

怎么办？

这个时候有两个办法，方法1.建id 和deal_date的联合主键

create table range_part_tab (id number,deal_date date,area_code number,nbr number,contents varchar2(4000))

       partition by range (deal_date)

       (
       partition p_201801 values less than (TO_DATE('2018-02-01', 'YYYY-MM-DD')),
       partition p_201802 values less than (TO_DATE('2018-03-01', 'YYYY-MM-DD')),
       partition p_201803 values less than (TO_DATE('2018-04-01', 'YYYY-MM-DD')),
       partition p_201804 values less than (TO_DATE('2018-05-01', 'YYYY-MM-DD')),
       partition p_201805 values less than (TO_DATE('2018-06-01', 'YYYY-MM-DD')),
       partition p_201806 values less than (TO_DATE('2018-07-01', 'YYYY-MM-DD')),
       partition p_201807 values less than (TO_DATE('2018-08-01', 'YYYY-MM-DD')),
       partition p_201808 values less than (TO_DATE('2018-09-01', 'YYYY-MM-DD')),
       partition p_201809 values less than (TO_DATE('2018-10-01', 'YYYY-MM-DD')),
       partition p_201810 values less than (TO_DATE('2018-11-01', 'YYYY-MM-DD')),
       partition p_201811 values less than (TO_DATE('2018-12-01', 'YYYY-MM-DD')),
       partition p_201812 values less than (TO_DATE('2019-01-01', 'YYYY-MM-DD')),
       partition p_201901 values less than (TO_DATE('2019-02-01', 'YYYY-MM-DD')),
       partition p_201902 values less than (TO_DATE('2019-03-01', 'YYYY-MM-DD')),
       partition p_max values less than (maxvalue)

       )

       ;

这样果然不报错
SQL> alter table range_part_tab add constraint range_tab_pk primary key (id,deal_date) ;
操作已执行
已用时间: 188.197(毫秒). 执行号:4.
SQL>
方法2：将该表改成堆表STORAGE (NOBRANCH);
drop table range_part_tab;

create table range_part_tab (id number primary key ,deal_date date,area_code number,nbr number,contents varchar2(4000))

       partition by range (deal_date)

       (
       partition p_201801 values less than (TO_DATE('2018-02-01', 'YYYY-MM-DD')),
       partition p_201802 values less than (TO_DATE('2018-03-01', 'YYYY-MM-DD')),
       partition p_201803 values less than (TO_DATE('2018-04-01', 'YYYY-MM-DD')),
       partition p_201804 values less than (TO_DATE('2018-05-01', 'YYYY-MM-DD')),
       partition p_201805 values less than (TO_DATE('2018-06-01', 'YYYY-MM-DD')),
       partition p_201806 values less than (TO_DATE('2018-07-01', 'YYYY-MM-DD')),
       partition p_201807 values less than (TO_DATE('2018-08-01', 'YYYY-MM-DD')),
       partition p_201808 values less than (TO_DATE('2018-09-01', 'YYYY-MM-DD')),
       partition p_201809 values less than (TO_DATE('2018-10-01', 'YYYY-MM-DD')),
       partition p_201810 values less than (TO_DATE('2018-11-01', 'YYYY-MM-DD')),
       partition p_201811 values less than (TO_DATE('2018-12-01', 'YYYY-MM-DD')),
       partition p_201812 values less than (TO_DATE('2019-01-01', 'YYYY-MM-DD')),
       partition p_201901 values less than (TO_DATE('2019-02-01', 'YYYY-MM-DD')),
       partition p_201902 values less than (TO_DATE('2019-03-01', 'YYYY-MM-DD')),
       partition p_max values less than (maxvalue)
       )

    STORAGE  (NOBRANCH) ;

该堆表果然OK了

操作已执行

已用时间: 24.119(毫秒). 执行号:6.