15.00 - Exclusive AS WITH DATA Clause Rules - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Teradata Database
Programming Reference

Exclusive AS … WITH DATA Clause Rules

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)” on page 31) 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 request 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.