Restrictions on Queue Tables - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

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
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™
There are a number of restrictions on queue tables and their use, as detailed in the following list.
  • Queue tables cannot be temporal tables.
  • The table definition DDL for a queue table must always specify the keyword QUEUE immediately following the specification for SET or MULTISET (see QUEUE Keyword).
  • The first column defined for a queue table must be a user-defined QITS column (see QITS Column).
  • You cannot drop the QITS column from a queue table (see ALTER TABLE (Basic Table Parameters)).
  • You cannot define the QITS column to be any of the following.
  • You cannot define a queue table as either an nonpartitioned NoPI table or as a column-partitioned table. See Nonpartitioned NoPI Tables and Column-Partitioned Tables.
  • You cannot define a queue table with either of the following referential integrity clauses.
    • FOREIGN KEY … REFERENCES
    • REFERENCES
  • You cannot reference queue table columns from a FOREIGN KEY … REFERENCES or REFERENCES clause in the definition of a different, non-queue, table.
  • All other column- and table-level constraint clauses are valid within a queue table definition with the exception of UNIQUE and PRIMARY KEY constraints not being valid for the QITS column.
  • You cannot define any column of a queue table with a BLOB, CLOB, Geospatial, JSON, XML, ARRAY, or VARRAY data type. For information about BLOB, CLOB, ARRAY, and VARRAY data types, see Teradata Vantage™ - Data Types and Literals, B035-1143. For information about Geospatial data types, see Teradata Vantage™ - Geospatial Data Types, B035-1181. For information about the JSON data type, see Teradata Vantage™ - JSON Data Type, B035-1150. For information about the XML data type, see Teradata Vantage™ - XML Data Type, B035-1140.
  • Queue tables cannot also be global temporary tables.
  • Queue tables cannot also be volatile tables.
  • You cannot specify permanent journals for queue tables.
  • Because queue tables cannot be global temporary or volatile tables, the following restrictions also apply to them.
    • You cannot log their change images to a transaction journal.

      You cannot specify the LOG option for queue tables. See LOG and NO LOG.

    • You cannot preserve their contents after a transaction completes.

      You cannot specify an ON COMMIT clause for queue tables. See ON COMMIT DELETE/PRESERVE ROWS.

  • You cannot use the copy table syntax to copy the definition of a queue table. Neither the source, including a table referenced in a subquery, nor the target table of a CREATE TABLE AS clause can be a queue table. See CREATE TABLE (AS Clause).
  • You cannot define queue tables with a partitioned primary index.
  • Queue tables are not supported for the following load and export utilities.
    • MultiLoad
    • FastLoad
    • FastExport
  • If you change the tdlocaledef.txt file and issue a tpareset command, the new format string settings affect only those tables that are created after the reset. Existing table columns continue to use the extant format string in DBC.TVFields unless you submit an ALTER TABLE statement to change it. See ALTER TABLE (Basic Table Parameters).