Updated on 2023-04-03 GMT+08:00

Index

Unique Index

Netezza Syntax

Syntax After Migration

CREATE TABLE  prod
(
     prod_no              number(6)      not null    unique,
     prod_name         national character varying(32)      not null,
     prod_desc          clob
)
DISTRIBUTE ON (prod_no)
ORGANIZE   ON (prod_no, prod_name)
;
----------
CREATE TABLE  prod
(
     prod_no              number(6)      not null     
     CONSTRAINT UQ_prod unique,
     prod_name         national character varying(32)      not null,
     prod_desc          clob
)
DISTRIBUTE ON (prod_no)
ORGANIZE   ON (prod_no, prod_name)
;
----------
CREATE TABLE  prod
(
     prod_no              number(6)      not null    PRIMARY KEY,
     prod_name         national character varying(32)      not null,
     prod_desc          clob
)
DISTRIBUTE ON (prod_no)
ORGANIZE   ON (prod_no, prod_name)
;
----------
CREATE TABLE  prod
(
     prod_no              number(6)      not null,
     prod_name         national character varying(32)      not null,
     prod_desc          clob,
     constraint          uq_prod    UNIQUE (prod_no)
)
DISTRIBUTE ON (prod_no)
ORGANIZE   ON (prod_no, prod_name)
;
----------
CREATE TABLE  prod
(
     prod_no              number(6)      not null,
     prod_name         national character varying(32)      not null,
     prod_desc          clob
)
DISTRIBUTE ON (prod_no)
ORGANIZE   ON (prod_no, prod_name)
;
ALTER TABLE prod
    ADD constraint          uq_prod    UNIQUE (prod_no);
CREATE TABLE  prod
(
     prod_no              number(6)      not null   /* unique */,
     prod_name         national character varying(32)      not null,
     prod_desc          clob
) WITH(ORIENTATION=COLUMN)
DISTRIBUTE BY HASH (prod_no)
/* ORGANIZE   ON (prod_no, prod_name) */
;
----------
CREATE TABLE  prod
(
     prod_no              number(6)      not null   
  /* CONSTRAINT UQ_prod unique */,
     prod_name         national character varying(32)      not null,
     prod_desc          clob
) WITH(ORIENTATION=COLUMN)
DISTRIBUTE BY HASH (prod_no)
/* ORGANIZE   ON (prod_no, prod_name) */
;
----------
CREATE TABLE  prod
(
     prod_no              number(6)      not null   /* PRIMARY KEY */,
     prod_name         national character varying(32)      not null,
     prod_desc          clob
) WITH(ORIENTATION=COLUMN)
DISTRIBUTE BY HASH (prod_no)
/* ORGANIZE   ON (prod_no, prod_name) */
;
----------
CREATE TABLE  prod
(
     prod_no              number(6)      not null,
     prod_name         national character varying(32)      not null,
     prod_desc          clob  /*,
    constraint          uq_prod    UNIQUE (prod_no)  */
) WITH(ORIENTATION=COLUMN)
DISTRIBUTE BY HASH (prod_no)
/* ORGANIZE   ON (prod_no, prod_name)*/
;
----------
CREATE TABLE  prod
(
     prod_no              number(6)      not null,
     prod_name         national character varying(32)      not null,
     prod_desc          clob
)
DISTRIBUTE BY HASH (prod_no)
/*ORGANIZE   ON (prod_no, prod_name)*/
;
/*
ALTER TABLE prod
    ADD constraint          uq_prod    UNIQUE (prod_no);
*/

This feature is applicable only for COLUMN store. For ROW store, Unique Index should not be commented.