Trafodion CQD-allow_nullable_unique_key_constraint_--allow-nullable-unique-key-优快云博客

本文介绍了Trafodion数据库系统中的CQD（客户端查询定义）allow_nullable_unique_key_constraint的功能。此CQD允许用户在创建表时指定主键或聚簇键字段可以为空，解决了实际应用中对空值字段的需求。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

本文介绍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.