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

Teradata RDBMS forUNIX SQL Reference - NCR

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

MODIFY col_name CHECK (boolean condition), or MODIFY CONSTRAINT name CHECK (boolean condition) MODIFY CONSTRAINT name CHECK (boolean search condition) is allowed only if a constraint with name already exists in the table. This also includes the named constraints defined as part of the column definition since those constraints are handled as named table-level constraints i.e. MODIFY col_name is allowed only if col_name already possesses a constraint. Existing rows of the table are accessed to validate that the current values conform to the specified search condition. If not, an error is returned and no change is made to the table definition.


Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide


Syntax Element... Description
DROP CONSTRAINT name Drops table-level named constraint on the stated table.
ADD unique definition Adds a uniqueness constraint UNIQUE or PRIMARY KEY on one or more columns of the table. The internal implementation is to add a unique secondary index. Existing rows of the table are accessed to validate that the proposed uniqueness constraint is not violated. If the uniqueness is violated, an error is reported and no change is made. The columns listed for uniqueness must all be NOT NULL.
For an existing column, the data type and attribute in general may: specify FORMAT, TITLE, and/or DEFAULT or WITH DEFAULT phrases. not specify the COMPRESS phrase. specify changes in data type attributes or in data types in cases where the change will not require rewriting existing rows.
Examples: Changing casespecific or NUl CASESPECIFIC is valid, Dut changing to UPPERCASE is not. Changing from VARCHAR(6) to VARCHAR(12) is valid, but changing from VARCHAR(12) to VARCHAR(6) is not
Privileges To alter a table, the user must have DROP TABLE privilege on the table.
Locks When an ALTER TABLE statement is executed, an exclusive lock is placed on the table being altered.

Because of the overhead required to alter a table for dropped or added columns, you should establish a final form for a table before it contains many rows. When a very large table is altered, it may be unavailable to other users for a long time.


When an ALTER TABLE is executed to add FALLBACK, a full file scan is generated. To add the FALLBACK option to a table, all AMPs must be on-line and operational.

Dropping Columns

When a column is dropped, the Teradata RDBMS must delete the field corresponding to the dropped column in every row in the table.

Teradata RDBMS for UNIX SQL Reference

Teradata SQL Syntax Guide


Indexed columns cannot be dropped. If an attempt is made to drop a column on which a primary or secondary index is defined, an error message is returned.

To drop a column on which this type of index is defined . . .

Follow this procedure . .


Step Action
1 Create a new table with the correct primary index.
2 Copy the data into the new table. A simple way to do this is to use an INSERT statement with a SELECT subquery.
3 Drop the original table.
4 Rename the new table.


Step Action
1 Drop the index.
2 Drop the index columns
3 Define a new index on the correct columns.

When a column is added to a table that contains rows, the Teradata Adding Columns RDBMS generally needs to access each row of the table to add a field

for the new column. The following principles apply when adding a column to a table that contains rows:

IF this phrase . . . IS . . . THEN all rows are initially . . .
DEFAULT specified for the new column contain the specified constant values in the field.
WITH DEFAULT specified for the new column contain the system default in the field.
DEFAULT WITH DEFAULT not specified for the new column are null for the field, and the NOT NULL phrase cannot be specified for the column.

A column that is specified for NOT NULL but has no DEFAULT or WITH DEFAULT phrase can only be added to an empty table.


Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide


Adding the UNIQUE or PRIMARY KEY Definition

Adding or Dropping REFERENCES or FOREIGN KEY



A PRIMARY KEY can be added via the ALTER TABLE statement if and only if there is no PRIMARY KEY specification currently in the table, regardless of the type of index (Unique Primary Index/Unique Secondary Index) this PRIMARY KEY is currently mapped to.

Also, the PRIMARY KEY being added will always be mapped to a Unique Secondary Index. The PRIMARY KEY constraint can be dropped if and only if it is mapped to an Unique Secondary Index.

Unnamed unique constraints must be dropped via the DROP INDEX statement.

ALTER TABLE...ADD/DROP UNIQUE/PRIMARY KEY will require INDEX rights on the table, or, for compatibility with the past, DROP TABLE rights.

An ALTER TABLE can only add or drop one foreign key reference and it cannot contain any options other than to add or drop the foreign key.
Previous << 1 .. 105 106 107 108 109 110 < 111 > 112 113 114 115 116 117 .. 241 >> Next