Example: CREATE Request with Presence or Absence of PRIMARY KEY or UNIQUE Constraint and Setting of PrimaryIndexDefault - Advanced SQL Engine - Teradata Database

This example specifies neither an explicit PRIMARY INDEX clause nor an explicit NO PRIMARY INDEX clause, but does specify a UNIQUE constraint on column_2.

     CREATE TABLE test_1 (
       column_1 INTEGER NOT NULL,
       column_2 INTEGER NOT NULL 
     CONSTRAINT UNIQUE (column_2));

Regardless of the setting for the PrimaryIndexDefault parameter, Vantage creates this table with a UPI on column_2 because you neither explicitly specified a primary index nor NO PRIMARY INDEX. Because of this, the system follows the rules for creating a default primary index and creates a UPI on the first column with a UNIQUE constraint, which is column_2.

As a result, a SHOW TABLE request on test_1 returns the following SQL text.

     CREATE SET TABLE test_1 ,NO FALLBACK ,
       NO BEFORE JOURNAL,
       NO AFTER JOURNAL,
       CHECKSUM = DEFAULT (
       (
       column_1 INTEGER NOT NULL,
       column_2 INTEGER NOT NULL, CONSTRAINT UNIQUE (column_2);

Because no INDEX clause was specified, Vantage creates the table with a unique primary index on column_2 (marked as being for a PRIMARY KEY constraint in the data dictionary), regardless of the setting of the PrimaryIndexDefault parameter in DBS Control.

The following example also does not specify either an explicit PRIMARY INDEX clause or an explicit NO PRIMARY INDEX clause, but does specify a PRIMARY KEY constraint on column_2.

     CREATE TABLE test_2 AS (
       column_1 INTEGER NOT NULL
       column_2 INTEGER NOT NULL 
     CONSTRAINT PRIMARY KEY (column_2));

Regardless of the setting for the PrimaryIndexDefault parameter, Vantage creates this table with a UPI on column_2 (marked as being for a PRIMARY KEY constraint in the data dictionary), because column_2 is defined as the primary key for the table, so a SHOW TABLE request on test_2 returns the following SQL text.

     CREATE SET TABLE test_2 ,NO FALLBACK ,
       NO BEFORE JOURNAL,
       NO AFTER JOURNAL,
       CHECKSUM = DEFAULT
       (
       column_1 INTEGER NOT NULL,
       column_2 INTEGER NOT NULL, CONSTRAINT PRIMARY KEY (column_2);

Because no INDEX clause was specified, Vantage creates the table with a unique primary index on column_2 regardless of the setting of the PrimaryIndexDefault parameter in DBS Control.

This example does not specify either an explicit PRIMARY INDEX clause or an explicit NO PRIMARY INDEX clause. The request also specifies a UNIQUE constraint on column_1 and a PRIMARY KEY constraint on column_2.

     CREATE TABLE test_3 AS (
       column_1 INTEGER NOT NULL
       column_2 INTEGER NOT NULL 
     CONSTRAINT UNIQUE (column_1)
     CONSTRAINT PRIMARY KEY (column_2));

In this case, Vantage follows the default hierarchy for converting non-primary index columns into a primary index and converts the PRIMARY KEY constraint to the unique primary index for the table because it has precedence over UNIQUE constraints in the default primary index rules hierarchy.

Regardless of the setting for the PrimaryIndexDefault parameter, Vantage creates this table with a UPI on column_2 and converts the UNIQUE constraint on column_1 to a unique secondary index, so a SHOW TABLE request on test_3 returns the following create text.

     CREATE SET TABLE test_3 ,NO FALLBACK ,
       NO BEFORE JOURNAL,
       NO AFTER JOURNAL,
       CHECKSUM = DEFAULT
       (column_1 INTEGER NOT NULL,
       column_2 INTEGER NOT NULL, 
							CONSTRAINT UNIQUE (column_1)
       CONSTRAINT PRIMARY KEY (column_2));