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


To facilitate re-creating a table according to Step 1, you may display the CREATE TABLE statement for the table by executing the SHOW TABLE statement.

For example, the following statement displays a reconstruction of the CREATE TABLE statement for table dept_em.

SHOW TABLE dept_em ;

If using BTEQ, when the CREATE TABLE statement is displayed, you can change the statement (that is, change the table name and index) using BTEQ edit commands and submit the new table definition using the BTEQ SUBMIT command.

Grant the same access rights to the employee table again, as they were lost when the table was dropped. Use the same access rights cataloged in step 3.

Note: Increasing the length of VARCHAR or VARBYTE does not change the current format. Consider including a new FORMAT phrase in the statement.

Note: The Teradata RDBMS ALTER TABLE statement offers many extensions not offered by ANSI SQL. For example, changing column attributes in ANSI SQL is restricted to setting or dropping a default clause, which is not the case in Teradata RDBMS SQL.

6

7

Teradata RDBMS for UNIX SQL Reference

8-23
Teradata SQL Syntax Guide

ALTER TABLE

Redefining the Primary Index

To redefine the primary index for a table, use a procedure similar to that described earlier in the section titled “Procedure to Change Column Data Types” on page 8-22:

Step Action
1 Using a different name, create a new table that specifies the new index.
2 Populate the new table using an INSERT statement with a subselect.
3 Catalog the access rights on the old table. Refer to step 3 in the section above.
4 Drop the old table.
5 Rename the new table with that of the old table.
6 Grant access rights on the new table.

The following examples illustrate the use of ALTER TABLE:

Examples8

Use the following to add the columns Street and City to the Example 1 8 Employee table:

ALTER TABLE Employee ADD Street VARCHAR(30), ADD City VARCHAR(20) ;

Use the following to implement fallback protection for the Example 2 8 Employee table:

ALTER TABLE Employee, FALLBACK;

If a table named NewEmp is set for no journaling, but the database ExampIs 3 has a defined journal, the following statement could be used to add

a single before-image journal and a dual after-image journal:

ALTER TABLE NewEmp, BEFORE JOURNAL, DUAL AFTER JOURNAL ;

The following request changes the single before-image journal to a Example 4 dual-image journal and also drops two columns:

ALTER TABLE NewEmp, DUAL BEFORE JOURNAL DROP Phone, DROP PRef ;

8-24

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

ALTER TABLE

Example 5

Example 6: Adding the Null Attribute to Columns

Example 7: Composite Changes: NULL to NOT NULL

Example 8: Composite Changes: NOT NULL to NULL)

Example 9: DATABLOCKSIZE

Example 10: FREESPACE

The following example changes the attributes of the DeptNo column in the Department table to include a TITLE phrase:

ALTER TABLE Personnel.Department ADD DeptNo TITLE 'Depart';

The following statements add the NULL qualifier to columns trainingskill and col2:

ALTER TABLE personnel add trainingskill null;

ALTER TABLE abc add col2 null;

A composite change to a column implies changing more than one attribute of that column in one statement. NULL columns can be changed to NOT NULL , along with other attribute changes, in one statement. For example:

ALTER TABLE abc add col2 casespecific not null;

changes both the case and NULL attribute of col2. An error is returned if abc contains any rows with null values in col2.

Composite changes to columns that involve changing the NOT NULL attribute to NULL cannot be done. For example, the following cannot be done

ALTER TABLE abc add col2 title 'newname' null;

To make these changes use two ALTER TABLE statements:

ALTER TABLE abc add col2 title 'newname';

ALTER TABLE abc add col2 null;

The following alter table statement could be used to switch to a maximum data block size of 32256 bytes (63 sectors) and, by using the IMMEDIATE option, repack existing data into blocks of that size:

ALTER TABLE Employee, DATABLOCKSIZE = 32256 BYTES IMMEDIATE ;

The following alter table statement could be used to switch to 5% freespace:

ALTER TABLE Employee, FREESPACE = 5 PERCENT ;

Teradata RDBMS for UNIX SQL Reference

8-25
Teradata SQL Syntax Guide

ALTER TABLE

The following set of examples deals with adding or dropping Adding/Dropping named constraints

Named Constraints 8

Table name table_1 must not already have a defined PRIMARY KEY Example 11 constraint. Constraint name primary_1 must not duplicate an

existing constraint name.

ALTER TABLE table_1

ADD CONSTRAINT primary_1 PRIMARY KEY (field_1, field_2)

Field_1 and field_2 must be NOT NULL or an error is reported.

Constraint name check_1 must not be an existing name in table_1.

Example 12 8

ALTER TABLE table_1 ADD CONSTRAINT check_1 CHECK (field 1 > field 2)
Previous << 1 .. 109 110 111 112 113 114 < 115 > 116 117 118 119 120 121 .. 241 >> Next