Exclusive AS … WITH DATA Clause Rules - 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™
The following list of rules applies only to an AS … WITH DATA clause. These rules do not affect an AS … WITH NO DATA clause.
  • You cannot create global temporary tables using the AS … WITH DATA clause.
  • You cannot specify referential integrity constraints for an AS  … WITH DATA clause because a forward reference on a table that does not exist might be defined, and such a definition blocks any inserts until you create the referenced table.

    Instead, use the ALTER TABLE statement (see ALTER TABLE (Basic Table Parameters)) to add referential integrity constraints after you create the new table.

  • An error that prevents a subquery result from being stored in the new table aborts the creation of the new table.

    For example, you code a CREATE TABLE … AS … WITH DATA statement and specify a column constraint such as CHECK or NOT NULL. If a constraint causes a row in the subquery result to be rejected, then the table creation aborts and the system returns a constraint violation error to the requestor.