达梦数据库分区表注意事项

达梦数据库分区表创建时必须包括全部分区列，否则创建失败，且分区表不支持禁用主键约束。测试如下：

CREATE TABLE "SYSDBA"."DMTEST1"
(
	"C1" INT NOT NULL ,
	"C2" INTEGER NOT NULL ,
	"C3" INTEGER NOT NULL ,
	"C4" VARCHAR2(200) NOT NULL ,
	"C5" INTEGER,
	"C6" INTEGER,
	"C7" CHAR(10),
	"C8" INTEGER,
	CONSTRAINT "PK_DMTEST1_TEST" PRIMARY KEY("C1", "C2", "C3", "C4")
)PARTITION BY RANGE(C3)
SUBPARTITION BY LIST (C4)
(PARTITION "P01" VALUES LESS THAN ('100')
(SUBPARTITION "P011"  VALUES ('A'),SUBPARTITION "P012"  VALUES ('B')),
PARTITION "P02" VALUES LESS THAN ('200')
(SUBPARTITION "P021"  VALUES ('C'),SUBPARTITION "P022"  VALUES ('D')),
PARTITION "P03" VALUES LESS THAN (MAXVALUE)
(SUBPARTITION "P031"  VALUES ('E'),SUBPARTITION "P032"  VALUES ('F'))
)

以上语句可以创建成功。注意，其中主键包含了分区列C3,C4。如果没有将创建失败，示例如下：

--错误语句
CREATE TABLE "SYSDBA"."DMTEST1"
(
	"C1" INT NOT NULL ,
	"C2" INTEGER NOT NULL ,
	"C3" INTEGER NOT NULL ,
	"C4" VARCHAR2(200) NOT NULL ,
	"C5" INTEGER,
	"C6" INTEGER,
	"C7" CHAR(10),
	"C8" INTEGER,
	CONSTRAINT "PK_DMTEST1_TEST" PRIMARY KEY("C1", "C2")
)PARTITION BY RANGE(C3)
SUBPARTITION BY LIST (C4)
(PARTITION "P01" VALUES LESS THAN ('100')
(SUBPARTITION "P011"  VALUES ('A'),SUBPARTITION "P012"  VALUES ('B')),
PARTITION "P02" VALUES LESS THAN ('200')
(SUBPARTITION "P021"  VALUES ('C'),SUBPARTITION "P022"  VALUES ('D')),
PARTITION "P03" VALUES LESS THAN (MAXVALUE)
(SUBPARTITION "P031"  VALUES ('E'),SUBPARTITION "P032"  VALUES ('F'))
)

以上语句创建表将会报错，报错信息为“局部唯一索引必须包含全部分区列”

在第一条创建成功的语句中，表创建成功后，DM是不支持主键约束禁用的，禁用时会报错，示例如下：

同理，如果有表结构在创建时就禁用了主键约束，表也是创建失败的，示例如下：

--错误语句
CREATE TABLE "SYSDBA"."DMTEST2"
(
	"C1" INT NOT NULL ,
	"C2" INTEGER NOT NULL ,
	"C3" INTEGER NOT NULL ,
	"C4" VARCHAR2(200) NOT NULL ,
	"C5" INTEGER,
	"C6" INTEGER,
	"C7" CHAR(10),
	"C8" INTEGER,
	CONSTRAINT "PK_DMTEST2_TEST" PRIMARY KEY("C1", "C2", "C3", "C4") DISABLE
)PARTITION BY RANGE(C3)
SUBPARTITION BY LIST (C4)
(PARTITION "P01" VALUES LESS THAN ('100')
(SUBPARTITION "P011"  VALUES ('A'),SUBPARTITION "P012"  VALUES ('B')),
PARTITION "P02" VALUES LESS THAN ('200')
(SUBPARTITION "P021"  VALUES ('C'),SUBPARTITION "P022"  VALUES ('D')),
PARTITION "P03" VALUES LESS THAN (MAXVALUE)
(SUBPARTITION "P031"  VALUES ('E'),SUBPARTITION "P032"  VALUES ('F'))
)

注意以上语句与第一条SQL相比 多了DISABLE，即禁用主键约束，会创建失败，报错为：RANGE水平分区表[DMTEST2]不支持此修改操作