Partitioning CHECK Constraints - Teradata Database

Partitioning CHECK Constraints

A partitioned table or join index has the following partitioning CHECK constraint.

     CHECK (/*nn bb cc*/ partitioning_constraint_1 

            [AND partitioning_constraint_2] … )

where:

   partitioning_expression_i /*i d+a*/ IS NOT NULL

   PARTITION#Li /*i d+a*/ =1

Assume you create the following table:

     CREATE TABLE orders (

       o_orderkey      INTEGER NOT NULL,

       o_custkey       INTEGER,

       o_orderstatus   CHARACTER(1) CASESPECIFIC,

       o_totalprice    DECIMAL(13,2) NOT NULL,

       o_orderdate     DATE FORMAT 'yyyy-mm-dd' NOT NULL,

       o_comment       VARCHAR(79))

     NO PRIMARY INDEX

     PARTITION BY (RANGE_N(o_custkey BETWEEN  0

                                     AND 100000

                                     EACH     1),

                   COLUMN)

     UNIQUE INDEX (o_orderkey);

The table‑level partitioning CHECK constraint SQL text for this 8‑byte partitioning is as follows:

     CHECK (/*02 08 02*/ RANGE_N(o_custkey BETWEEN  0 

                                           AND 100000

                                           EACH     1

            /*1 100001+485440633518572409*/

            IS NOT NULL AND PARTITION#L2 /*2 9+10*/ =1)

The maximum size of this table‑level CHECK constraint is 16,000 characters.

You can use the following request to retrieve the level for the column partitioning for each of the objects that have column partitioning in the system.

     SELECT DBaseId, TVMId, ColumnPartitioningLevel 

            (TITLE ‘Column Partitioning Level’)

     FROM DBC.TableConstraints

     WHERE ConstraintType = 'Q' 

     AND   ColumnPartitioningLevel >= 1

     ORDER BY DBaseId, TVMId;

See “Partitioning CHECK Constraints for Single‑Level Partitioning” on page 309 and “Partitioning CHECK Constraint for Multilevel Partitioning” on page 321 for information about the table‑level CHECK constraints that Teradata Database creates for single‑level and multilevel partitioned primary index tables and join indexes.