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 .. 108 109 110 111 112 113 < 114 > 115 116 117 118 119 120 .. 241 >> Next


It is possible that the user either intends to or must revert to a definition of a table which results in an inconsistent reference on that table.

The Archive and Restore operations most commonly cause the inconsistencies.

While the Child table is archived, the Parent table can be dropped.

When the Child table is restored, the Parent table no longer exists. The normal ALTER TABLE DROP FOREIGN KEY statement does not work, because the Parent table references cannot be resolved.

A special option of the ALTER TABLE statement which can be used to remove these inconsistent references from a table.

The form is:

alter table dbname.tname drop inconsistent references

The user executing the statement must have DROP rights on the target table of the statement.

This statement removes all inconsistent internal indexes used to establish references. The base data of the table is not modified.

8-20

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

ALTER TABLE

The COMPRESS attribute cannot be changed, and the Data Type ComPress cannot be changed if that would require a change to existing data

(see ). In both cases, the user must first create a new table defining the proper attributes, then insert the values from the existing table into the new table.

If the data type of any column in the new table is not compatible with the value of the corresponding field in the existing table, individual INSERT statements must be used to load each row.

If all the new data types are compatible with all the existing values (for example, only the COMPRESS attribute is being changed), the INSERT statement may be used with a SELECT subquery to insert all the rows in a single statement (see “INSERT” statement).

The Data Type defined for a column may be changed only if the new Data Type 8 type does not require a change to the existing data. The rules for

changing the Data Type of a column are listed in . See also the following section, “Changing A Data Type Attribute”.

The display attributes FORMAT and TITLE may be altered at any

Changing A Data Type time and a new DEFAULT declared.

Attribute8

COMPRESS may not be declared.

CASESPECIFIC or NOT CASESPECIFIC can be declared for character columns.

The following table illustrates the rules for changing a data type

Rules for Changing a Data from the existing type to a new type.

Type8

Old Data Type New Data Type Restrictions on Changes
All All Cannot change for a column used in a primary or secondary index.
CHAR(n),UC or CHAR(n) CHAR(n) or CHAR(n),CS Lengths must remain identical, can change only CS (case specific) setting.
CHAR CHAR(n) Not allowed if n > 1.
VARCHAR(m),UC VARCHAR(n) Cannot decrease maximum length; cannot change from no UC to UC. *
GRAPHIC GRAPHIC(n) Not allowed if n > 1.
VARGRAPHIC(m) VARGRAPHIC(n) Cannot decrease maxlength. *
BYTE BYTE(n) Not allowed if n > 1.
VARBYTE(m) VARBYTE(n) Cannot decrease maximum length. *

Teradata RDBMS for UNIX SQL Reference

8-21
Teradata SQL Syntax Guide

ALTER TABLE

Old Data Type New Data Type Restrictions on Changes
DATE INTEGER No restrictions.
INTEGER DATE Cannot change (integer column could include invalid date values).
DECIMAL(n,0) INTEGER Can change only if n = 5 to 9.
DECIMAL(n,0) SMALLINT Can change only if n = 3 or 4.
DECIMAL(n,0) BYTEINT Can change only if n = 1 or 2.
DECIMAL(n,f) DECIMAL(m,f) Can change only as follows: m >= n, no change in f; n = 1 or 2, m < 3; n = 3 or 4, m < 5; n = 5 to 9, m < 10; n = 10 to 15, m <= 18; may not change storage size.

Procedure to Change Column Data Types

To make a change to a column data type that affects existing column data, use the following procedure:

Step

Action

Create a new table with a different name that contains the changed data type attributes.

Populate the new table using an INSERT statement with a subselect.

Catalog the access rights of the old table before step 4. Use the following syntax:

SELECT username,accessright, grantauthority,columnname,addressflag

FROM dbc.allrights WHERE tname = '_____' AND

databasename = ' ';

Drop the old table.

Rename the new table with that of the old table.

As an example, use the following sequence of statements to expand the data type attribute for the name column from 12 to

14 characters via a temporary table:

CREATE TABLE temp, FALLBACK

(EmpNo INTEGER NOT NULL FORMAT 'ZZZZ9',

Name CHAR(14) NOT NULL,

HCap BYTEINT FORMAT 'Z9') UNIQUE PRIMARY INDEX (name) ;

1

2

3

4

5

8-22

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

ALTER TABLE

Step

Action

Once the table is created, populate it, drop the old employee table, and rename the temporary table. The following example shows how this is done:

INSERT INTO temp SELECT * FROM employee ;

DROP TABLE employee ;

RENAME TABLE temp TO employee ;

A different name (temp) is used in recreating the employee table because the employee table already exists. If you enter a CREATE TABLE statement for an existing table, you receive an error.
Previous << 1 .. 108 109 110 111 112 113 < 114 > 115 116 117 118 119 120 .. 241 >> Next