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 .. 103 104 105 106 107 108 < 109 > 110 111 112 113 114 115 .. 241 >> Next


CREATE MACRO NewEmp

( number INTEGER, name VARCHAR(12), dept INTEGER 100 TO 900, position VARCHAR(12), sex CHAR, edlev BYTEINT )

AS ( ABORT 'Department number 300 not valid'

WHERE :dept = 3 00 ;

INSERT INTO Employee (EmpNo, Name, DeptNo,

JobTitle, Sex, EdLev)

VALUES (:number, :name, :dept, :position,

:sex, :edlev) ;

SELECT * FROM Employee

WHERE EmpNo = :number ; ) ;

8-6

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

ALTER TABLE

ALTER TABLE

The ALTER TABLE statement can be used to perform all the Function following functions:

• Add new attributes for one or more columns of a table

• Add one or more new columns to an existing table

• Drop one or more columns from a table

• Modify column-level and table-level constraints

• Modify referential constraints

• Request or remove the FALLBACK option for a table

• Request or modify the JOURNAL option for a table

• Change the DATABLOCKSIZE or percent FREESPACE

The FALLBACK and JOURNAL defaults for a data table are originally determined by the statement that created or modified the object in which the table resides (see “CREATE TABLE”, “CREATE USER”, “CREATE DATABASE”, and “MODIFY USER” statements). Either or both of these defaults can be overridden by the statement that created the data table (see “CREATE TABLE” statement) or by the ALTER TABLE statement.

ALTER TABLE statement is flagged as non entry level ANSI when the SQL flagger is enabled.

Teradata RDBMS for UNIX SQL Reference

8-7
Teradata SQL Syntax Guide

ALTER TABLE

Syntax

ALTER TABLE

Column Changes”

— ADD — col name -

— ADD — col_name — NULL -

— DROP — col name-----------

Constraint

Changes

Column Storage -Attributes

Column Constraint Attributes

—data type attributes -- COMPRESS -

-data type declaration -

data type attributes

Ã

NULL constant

-UNIQUE-

CONSTRAINT name

J

— PRIMARY KEY---------------------------

----CHECK — (—boolean_condition — )-----

I—REFERENCE—tname-

L ( _L co_name — ) —I_

ADD DROP C

-FOREIGN KEY—(--col_name-—)—REFERENCES tname-

DROP CONSTRAINT name DROP INCONSISTENT REFERENCES

L( -I- co_name — )-1

ADD

-col name-

CONSTRAINT name

-DROP-

col_name

CONSTRAINT name

-CHECK—(—boolean_condition — ) -

CHECK

MODIFY

col_name

- CHECK—(—boolean_condition—) -

CONSTRAINT name — DROP—CONSTRAINT name----

-ADD-,------------

CONSTRAINT name PRIMARY KEY

P

UNIQUE

( col_name )

-H

Table Column Definition

Reference

Definition

Unique

Definition

FF07A043

8-8

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

ALTER TABLE
where:
Syntax Element... Description
dbname.tname Name of the table to be altered. If dbname is not specified, the default database for the current session is assumed.

Create Table Options

The Create Table options are a Teradata RDBMS extension to ANSI SQL The options can be listed in any order

NO FALLBACK PROTECTION Specification to add or remove duplicate copy protection for the table. If fallback is being added, a duplicate copy of the table is created and stored. If fallback is being removed, the existing duplicate copy is deleted. The FALLBACK keyword used alone implies PROTECTION.
WITH JOURNAL TABLE = dbname.tname Specification to define or redefine the journal table for the data table being altered. tname need not reside in the same database as the data table. If a database is not specified, the default database for the current session is assumed. If a database is specified, it must exist and tname must have been defined as its default journal table.
NO BEFORE JOURNAL DUAL BEFORE JOURNAL Alters the number of before-change images to be maintained for the table. If the JOURNAL keyword is specified without NO or DUAL, then a single copy of the image is maintained unless FALLBACK is in effect or is also specified. If journaling is requested for a table that uses fallback protection, DUAL images are maintained automatically.
NO AFTER JOURNAL DUAL AFTER JOURNAL LOCAL AFTER JOURNAL NOT LOCAL AFTER JOURNAL Alters the type of image to be maintained for the table; any existing images are not affected until the table is updated. NO, DUAL, LOCAL, or NOT LOCAL: the NO and DUAL options specify the number of after-change images to be maintained for the table. NOT LOCAL and LOCAL specify whether single after-image journal rows for non-fallback data tables are written on the same virtual AMP (LOCAL) as the changed data rows, or on another virtual AMP in the cluster (NOT LOCAL).

Teradata RDBMS for UNIX SQL Reference

8-9
Teradata SQL Syntax Guide

ALTER TABLE

Syntax Element... Description
BEFORE JOURNAL AFTER JOURNAL Specifies the type of image to be maintained for the table. The default for this option is established by a CREATE DATABASE, CREATE USER, MODIFY USER, or MODIFY USER statement for the database in which the table is to be created. Refer to the “CREATE TABLE” statement description for a listing of the journaling that results from the available journal and image type specifications.
Previous << 1 .. 103 104 105 106 107 108 < 109 > 110 111 112 113 114 115 .. 241 >> Next