Help Center> Data Warehouse Service> Tool Guide> DSC - SQL Syntax Migration Tool> SQL Syntax Migration Reference> Netezza Syntax Migration> Indexes
View PDF

Indexes

Unique Index

The row-store table supports the unique index, but the column-store table does not.

Netezza Syntax

Syntac 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.

Parent topic: Netezza Syntax Migration