15.10 - Example: CREATE … AS Requests That Produce a NoPI Table - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

Assume the following primary-indexed source table definition for the following example set.

     CREATE TABLE source_pi (
       column_1 INTEGER
       column_2 INTEGER)
     UNIQUE PRIMARY INDEX (column_1);

Also assume you have created the following NoPI table definition for the example set:

     CREATE TABLE source_nopi (
       column_1 INTEGER
       column_2 INTEGER)
     NO PRIMARY INDEX;

The following CREATE TABLE … AS requests all produce a NoPI table because in each case you have explicitly specified NO PRIMARY INDEX:

     CREATE TABLE target_nopi AS
       source_pi
     WITH DATA 
     NO PRIMARY INDEX;
     CREATE TABLE target_nopi AS
       source_nopi
     WITH DATA
     NO PRIMARY INDEX;
     CREATE TABLE target_nopi AS (
       SELECT column_1, column_2
       FROM source_pi) 
     WITH DATA 
     NO PRIMARY INDEX;
     CREATE TABLE target_nopi AS (
       SELECT column_1, column_2
       FROM source_nopi) 
     WITH DATA 
     NO PRIMARY INDEX;

Unlike the preceding examples, the outcome of the following CREATE TABLE request depends on the setting of the DBS Control parameter PrimaryIndexDefault.

     CREATE TABLE target AS (
       SELECT column_1, column_2
       FROM source_nopi) 
     WITH DATA;
IF the value for PrimaryIndexDefault is … THEN the resulting table has …
N no primary index.

Even though you have not explicitly specified NO PRIMARY INDEX, you also have not specified any PRIMARY KEY or UNIQUE constraints on the columns of the table, so Teradata Database defines target as a NoPI table by default.

D or P a nonunique primary index.

The NUPI is defined by default, following the rules for selecting the primary index for a table when none is specified.

In this case, the primary index is defined on the first column defined for target , which is column_1.

The primary index is defined as a NUPI because no PRIMARY KEY or UNIQUE constraints are defined on any of the columns of target.