ALTER TABLE MODIFY NO PRIMARY Examples - Analytics Database - Teradata Vantage

SQL Data Definition Language Syntax and Examples

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
jco1628111346878.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
mdr1472255012272
lifecycle
latest
Product Category
Teradata Vantage™

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