Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS forUNIX SQL Reference - NCR

NCR Teradata RDBMS forUNIX SQL Reference - NCR, 1997. - 913 p.
Download (direct link): teradataforunix1997.pdf
Previous << 1 .. 106 107 108 109 110 111 < 112 > 113 114 115 116 117 118 .. 241 >> Next


The user issuing the ALTER TABLE statement containing a REFERENCES option must have the REFERENCE right on the referenced table or on all specified columns of the referenced table.

At any give time, the FREESPACE value returned in response to a SHOW TABLE command is the value specified in the most recently entered ALTER TABLE (or CREATE TABLE) command. Refer to the descriptions of the CREATE TABLE and SHOW TABLE statements.

• Specifying the DEFAULT FREESPACE option when the percent freespace has been previously set to a value other than the default value, resets the size to the system default value.

• If the FREESPACE option is not included in the ALTER TABLE statement, then the percent freespace attribute value is not changed.

Repacking the data blocks of large tables is a time-consuming process, so specifying the IMMEDIATE option for large tables will substantially increase the amount of time required for the ALTER TABLE statement to complete. Also, in the event that an ALTER TABLE statement with the IMMEDIATE option aborts or is aborted by the user, the repacking may be incomplete—that is, some data blocks are of their original size, while others are of the new, specified size.

Teradata RDBMS for UNIX SQL Reference

8-15
Teradata SQL Syntax Guide

ALTER TABLE

Journaling: NO or DUAL

Journaling: BEFORE or AFTER

Journaling: LOCAL

• At any given time, the DATABLOCKSIZE value returned in response to a SHOW TABLE command is the value specified in the most recently entered ALTER TABLE (or CREATE TABLE) command. See the descriptions of the “CREATE TABLE” and “SHOW MACRO SHOW TABLE SHOW VIEW” statements.)

• If the DATABLOCKSIZE specification option is not included in the ALTER TABLE statement, then the data block size attribute value is not changed and no data blocks are repacked.

IF . . . THEN . . .
the JOURNAL keyword is a single copy of the image is
specified without NO or maintained unless FALLBACK is in
DUAL effect or is also specified.
journaling is requested for a DUAL images are maintained
database that uses fallback automatically.
protection

IF . . . THEN . . .
the JOURNAL keyword is specified without AFTER or BEFORE both types of images are maintained. This option may appear twice in the same statement: once to specify a BEFORE or AFTER image, and again to specify the alternate type.
one type is specified only the default for that type is overridden. For example, if AFTER is specified, before-image journaling remains at the default setting.
both types are specified the two specifications must not conflict.

Refer to “CREATE DATABASE” under “Local Journaling” on page 8-56 and “CREATE TABLE”. For further detail refer to the Teradata RDBMS for UNIX Database Design and Administration, Chapter 12, “Permanent Journaling”.

8-16

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

ALTER TABLE

A table may contain a maximum of 256 columns, and a total of 512 columns can be defined for a table over its lifetime. Dropping columns does not affect this rule. Therefore, if a table has 256 columns, the ALTER TABLE statement may be used to drop and replace up to 256 columns.

If new columns need to be added that would exceed this number, a new table must be created. The SHOW TABLE statement and the INSERT statement with a SELECT subquery can be used to create and load the spinoff table.

When new columns are being added, the first column defined is assigned the lowest column id. Column ids may be important if you specify any column for COMPRESS. As a general rule, the compressed column that will have the highest rate of access and the lowest rate of compressible values should be defined before other compressed columns.

Changing column attributes in ANSI SQL is restricted to setting or dropping a default clause.

To change the attributes of an existing column, the ALTER TABLE statement must include the ADD keyword. The following table summarizes the allowable cases for converting or adding NULL to NOT NULL, or NOT NULL to NULL columns.

WHEN you add this qualifier to a column . . . Composite changes are . . .
NULL never permitted.
NOT NULL permitted.

For columns not contained in indexes:

Column type . . . When defined as . . . Can be altered to this column type . . .
Indexed NULL NULL only
NOT NULL NOT NULL only
Non- indexed NULL NOT NULL If and only if the column does not contain nulls.
NOT NULL NULL

Changing Column Attributes: NOT NULL/NULL

Column IDs and COMPRESS

Maximum Number of Columns

Teradata RDBMS for UNIX SQL Reference

8-17
Teradata SQL Syntax Guide

ALTER TABLE

Adding and Dropping CHECK Constraint

The following section describes the rules for adding and dropping CHECK constraints:

The following list names the rules for using constraints with the ALTER TABLE statement.

• The following table explains rules for ADD and MODIFY.

THIS form . . . IS allowed only if col_name . . .
Previous << 1 .. 106 107 108 109 110 111 < 112 > 113 114 115 116 117 118 .. 241 >> Next