16.20 - Exclusive AS … WITH DATA Clause Rules - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Teradata Database
Teradata Vantage NewSQL Engine
Release Number
March 2019
Content Type
Programming Reference
Publication ID
English (United States)
Last Update
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.