15.00 - Rules and Restrictions for Large Object Data Types - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Rules and Restrictions for Large Object Data Types

Teradata Database supports the following large object (LOB) data types.

  • Binary large object (BLOB)
  • Character large object (CLOB)
  • The rules for specifying these types in an ALTER TABLE request are different from the rules that apply to other data types. Rules for specifying LOB data types in an ALTER TABLE request are as follows.

  • You can increase the maximum size for a BLOB or CLOB column definition, but you cannot decrease that size.
  • To increase the maximum size for a BLOB or CLOB column definition, use the ADD column_name option, specify the name of the column whose LOB data type you want to increase, and Teradata Database modifies the size of the type to the value you specify. For example, suppose you want to double the maximum size of a CLOB column named standard_error in the table named performance_data from 524,288,000 characters to 1,048,576,000 characters. You can use the following ALTER TABLE request to increase the size of the CLOB data type for standard_error to 1,048,576,000 characters.

         ALTER TABLE performance_data
         ADD standard_error CLOB(1048576000);

    Note: Teradata Database knows that column standard_error already exists in performance_data, so it interprets the ADD option to mean that standard_error is to be modified, not added.

  • You can only alter the following column attributes for a BLOB or CLOB column.
  • NULL
  • NOT NULL
  • You cannot change the attribute from NULL to NOT NULL if there are nulls in any rows in the table for that column.

  • TITLE
  • FORMAT
  • 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 existing format string in DBC.TVFields unless you submit an ALTER TABLE request to change it.

  • You can add BLOB and CLOB columns to a maximum of 32 per base table definition.
  • If a table already has 32 BLOB or CLOB columns or a mixture of both, you cannot add another BLOB or CLOB column to it using the same ALTER TABLE request that drops one of the 32 existing BLOB or CLOB columns.
  • Instead, you must drop an existing BLOB or CLOB column with one ALTER TABLE request and then add the new BLOB or CLOB column using a separate ALTER TABLE request.

  • You can drop BLOB or CLOB columns from a base table.
  • If you drop all BLOB and CLOB columns from a base table, that table is no longer bound to any BLOB or CLOB restrictions.

  • A constraint definition can neither be defined for nor reference a BLOB or CLOB column.
  • You cannot specify expressions or referenced columns with BLOB or CLOB data types in a start or end expression for a primary index range.