ALTER TABLE MODIFY NO PRIMARY Examples - Advanced SQL Engine - Teradata Database

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)

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);

ALTER TABLE Orders MODIFY NO PRIMARY INDEX;

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);