16.20 - MODIFY NO PRIMARY - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-24
dita:mapPath
wkf1512081455740.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval

Remove the primary index or primary AMP index from the table definition. The table can contain rows when dropping or adding ranges under certain conditions. Otherwise, the table must be empty. If you specify NO PRIMARY INDEX and a PARTITION BY clause, the partitioning expression must specify a COLUMN partitioning level.

INDEX
Optional keyword.

Example: Modifying a Table to Not Have a Primary Index

The following example deletes all of the rows in the orders table, alters the table to not have a primary index, adds both column partitioning and row partitioning, and adds a secondary index.

     DELETE orders ALL;
     ALTER TABLE orders 
     MODIFY NO PRIMARY INDEX 
     PARTITION BY (COLUMN ADD 5,
                   RANGE_N(o_ordertsz 
                    BETWEEN TIMESTAMP '2003-01-01 00:00:00.000000+00:00'
                    AND     TIMESTAMP '2009-12-31 23:59:59.999999+00:00' 
                    EACH INTERVAL '1' MONTH) );
     CREATE UNIQUE INDEX(o_orderkey) on orders;

This produces the following table definition for orders.

     CREATE TABLE orders (
       o_orderkey    INTEGER NOT NULL,
       o_custkey     INTEGER,
       o_orderstatus CHARACTER(1) CASESPECIFIC,
       o_totalprice  DECIMAL(13,2) NOT NULL,
       o_ordertsz    TIMESTAMP(6) WITH TIME ZONE NOT NULL,
       o_comment     VARCHAR(79) )
     PARTITION BY (COLUMN ADD 5,
                   RANGE_N(o_ordertsz 
                    BETWEEN TIMESTAMP '2003-01-01 00:00:00.000000+00:00'
                    AND     TIMESTAMP '2009-12-31 23:59:59.999999+00:00'
                    EACH INTERVAL '1' MONTH) ),
     UNIQUE INDEX(o_orderkey);

Example: Modifying a Primary AMP Index Table to Not Have a Primary Index (NoPI)

This example uses the following table definition:
CREATE TABLE Orders
    ( o_orderkey INTEGER NOT NULL,
      o_custkey INTEGER,
      o_orderstatus CHAR(1) CASESPECIFIC,
      o_totalprice DECIMAL(13,2) NOT NULL,
      o_ordertsz TIMESTAMP(6) WITH TIME ZONE NOT NULL,
      o_comment VARCHAR(79) )
  PRIMARY AMP INDEX (o_orderkey) PARTITION BY COLUMN
  UNIQUE INDEX (o_orderkey);
Execute the following ALTER TABLE statement:
ALTER TABLE Orders MODIFY NO PRIMARY INDEX;
The following table results:
CREATE TABLE Orders
    ( o_orderkey INTEGER NOT NULL,
      o_custkey INTEGER,
      o_orderstatus CHAR(1) CASESPECIFIC,
      o_totalprice DECIMAL(13,2) NOT NULL,
      o_ordertsz TIMESTAMP(6) WITH TIME ZONE NOT NULL,
      o_comment VARCHAR(79) )
  NO PRIMARY INDEX PARTITION BY COLUMN
  UNIQUE INDEX (o_orderkey);