本文介绍Trafodion的CQD之allow_nullable_unique_key_constraint,关于此CQD的详细,可参考官方文档:http://trafodion.incubator.apache.org/docs/cqd_reference/index.html#auto-query-retry-warnings
介绍
Trafodion在建表时可以用primary key或store by来指定数据按照哪些字段顺序存储,而默认情况下,primary key或store by中的字段需要设定为非空属性,否则建表会失败,
SQL>create table test_cqd(a int, b int) primary key(a);
*** ERROR[1135] Clustering key column A must be assigned a NOT NULL NOT DROPPABLE constraint. [2017-07-03 13:23:05]
SQL>create table test_cqd(a int, b int) store by (a);
*** ERROR[1135] Clustering key column A must be assigned a NOT NULL NOT DROPPABLE constraint. [2017-07-03 13:23:17]
SQL>create table test_cqd1(a int not null, b int) primary key(a);
--- SQL operation complete.
SQL>create table test_cqd2(a int not null, b int) primary key(a);
--- SQL operation complete.
SQL>showddl test_cqd1;
CREATE TABLE TRAFODION.SEABASE.TEST_CQD1
(
A INT NO DEFAULT NOT NULL NOT DROPPABLE NOT
SERIALIZED
, B INT DEFAULT NULL NOT SERIALIZED
, PRIMARY KEY (A ASC)
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
SQL>showddl test_cqd2;
CREATE TABLE TRAFODION.SEABASE.TEST_CQD2
(
A INT NO DEFAULT NOT NULL NOT DROPPABLE NOT
SERIALIZED
, B INT DEFAULT NULL NOT SERIALIZED
, PRIMARY KEY (A ASC)
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
如果key中确实会存储空值,则需要使用此cqd实现在建表时允许primary key或store by中的字段允许为空。allow_nullable_unique_key_constraint默认值为off,表示默认不允许key中字段可空,设置值为on允许空值,
SQL>cqd allow_nullable_unique_key_constraint 'on';
--- SQL operation complete.
SQL>create table test_cqd1(a int, b int) primary key(a);
--- SQL operation complete.
SQL>create table test_cqd2(a int, b int) store by(a);
--- SQL operation complete.
SQL>showddl test_cqd1;
CREATE TABLE TRAFODION.SEABASE.TEST_CQD1
(
A INT DEFAULT NULL NOT SERIALIZED
, B INT DEFAULT NULL NOT SERIALIZED
, PRIMARY KEY (A ASC)
)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.
SQL>showddl test_cqd2;
CREATE TABLE TRAFODION.SEABASE.TEST_CQD2
(
A INT DEFAULT NULL NOT SERIALIZED
, B INT DEFAULT NULL NOT SERIALIZED
)
STORE BY (A ASC)
ATTRIBUTES ALIGNED FORMAT
;
--- SQL operation complete.