Rules and Restrictions for Large Object Data Types - Advanced SQL Engine

Rules and Restrictions for Large Object Data Types - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product

Advanced SQL Engine

Teradata Database

Release Number

17.10

Published

July 2021

Language

English (United States)

Last Update

2021-07-27

dita:mapPath

imq1591724555718.ditamap

dita:ditavalPath

imq1591724555718.ditaval

dita:id

B035-1184

lifecycle

Product Category

Teradata Vantage™

Vantage 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 statement 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 Vantage 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);
```
Vantage 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.