17.10 - Exclusive AS … WITH DATA Clause Rules - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)
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.