15.10 - IndexConstraintsV[X] - Teradata Database

Teradata Database Data Dictionary

Product
Teradata Database
Release Number
15.10
Content Type
Administration
User Guide
Publication ID
B035-1092-151K
Language
English (United States)

Integrity

DBC

 

View Column

Data Type

Format

Source Table.Column

DatabaseName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

NOT NULL

X(128)

Dbase.DatabaseName

TableName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

NOT NULL

X(128)

TVM.TVMName

IndexName

VARCHAR(128)

UNICODE

X(128)

TableConstraints.Name

IndexNumber

SMALLINT

---,--9 (explicit)

TableConstraints.IndexNumber

ConstraintType

CHAR(1) LATIN

UPPERCASE

NOT CASESPECIFIC

NOT NULL

X(1)

TableConstraints.ConstraintType

ConstraintText

VARCHAR(16000)

UNICODE

CASESPECIFIC

X(255)

TableConstraints.TableCheck

ConstraintCollation

CHAR(1) LATIN

UPPERCASE

NOT CASESPECIFIC

NOT NULL

X(1)

TableConstraints.ConstraintCollation

CollationName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

X(128)

TableConstraints.CollName

CreatorName

VARCHAR(128)

UNICODE

NOT CASESPECIFIC

NOT NULL

X(128)

Dbase.DatabaseName

CreateTimeStamp

TIMESTAMP(0)

YYYY-MM-DDBHH:MI:SS

TableConstraints.CreateTimeStamp

CharSetID

BYTEINT

ZZ9

TableConstraints.CharSetID

SessionMode

CHAR(1) LATIN

UPPERCASE

NOT CASESPECIFIC

X(1)

TableConstraints.SessionMode

ResolvedCurrent_Date

DATE

YY/MM/DD

TableConstraints.ResolvedCurrent_Date

ResolvedCurrent_TimeStamp

TIMESTAMP(6) WITH TIME ZONE

YYYY-MM-DDBHH:MI:SS.S(F)Z

TableConstraints.ResolvedCurrent_
TimeStamp

The X view references these additional tables:

  • DBC.AccessRights
  • DBC.Owners
  • DBC.RoleGrants
  • DBC.Roles
  • This view is deprecated. Use the PartitioningConstraintsV[X] system view instead.

    For information about the possible values for the ConstraintType column, see Appendix A: “View Column Values.”

    CharSetID

    If the character set is user-defined, the ID in the CharSetID column should also exist in DBC.CharTranslations.CharSetID.

    IndexName

    For a partitioning constraint of a partitioned table or join index with neither a primary index nor a primary AMP index, the IndexName is NULL. For a partitioning constraint of a partitioned table with a primary index or a primary AMP index, the IndexName is NULL or not NULL depending on whether the index was given a name.

    IndexNumber

    This is an internal number assigned to the associated index, if any. This field is set to 1 for a primary index or primary AMP index. Otherwise, it is NULL.

    Possible Values for ResolvedCurrent_TimeStamp and ResolvedCurrent_Date

  • This is the last reconciled timestamp or date if the object is a join index or a table that is defined using:
  • CURRENT_TIMESTAMP
  • CURRENT_DATE or DATE
  • Either the partition, JI definition, or temporal table has a system-defined join index.

  • NULL in all other cases.
  • Possible Values for SessionMode

     

    Value

    Description

    A

    ANSI

    T

    Teradata

    NULL

    For constraints not involving comparison of character data.

    Example  

    The following query retrieves a list of tables and join indexes that have partitioning, including their index constraint text:

      SELECT DatabaseName, TableName (TITLE 'Table/Join Index Name'), 
          ConstraintText
      FROM DBC.IndexConstraints
      WHERE ConstraintType = 'Q'
      ORDER BY DatabaseName, TableName;

    Example  

    The following query retrieves a list of tables and join indexes that have single-level partitioning:

       SELECT DatabaseName, TableName (TITLE 'Table/Join Index Name')
       FROM DBC.IndexConstraints
       WHERE ConstraintType = 'Q'
       AND (  SUBSTRING(ConstraintText FROM 1 FOR 13) < 'CHECK (/*02*/'
         OR SUBSTRING(ConstraintText FROM 1 FOR 13) > 'CHECK (/*15*/')
       ORDER BY DatabaseName, TableName;