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 .. 126 127 128 129 130 131 < 132 > 133 134 135 136 137 138 .. 241 >> Next


UNIQUE Definition

Table level definition.

CNSTRAINT name Constraint names are a Teradata RDBMS extension to ANSI SQL and are flagged as nonentry level ANSI SQL if the SQL flagger is enabled.
UNIQUE specifies that no two rows in the table can have the same value in the field(s).
PRIMARY KEY designates that the values of one or more columns are to be used as the basis of a unique primary key.
col_name If more than one col_name is specified, the unique columns or primary key is based on the combined values of each column named. A maximum of 16 column names may be specified. Columns listed in a UNIQUE or PRIMARY KEY table constraint must all be NOT NULL.

8-78

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

CREATE TABLE

Syntax Element...

Description

REFERENCES Definition

Table level definition

CONSTRAINT name Constraint names are flagged as non entry level ANSI SQL, when the SQL flagger is enabled.
FOREIGN KEY The FOREIGN KEY and its use is described in Usage Notes. It is a table-level constraint. A maximum of 64 foreign keys can be defined for a single table.
col_name col_name refers to column(s) defined in the CREATE TABLE statement.
REFERENCES REFERENCES is described in the notes section.
name the name of the referenced Parent table used in the referential constraint definition. If not in the same database as the Child table, it must be qualified.
col_nam Designates one or more columns which comprise the Parent table's PRIMARY KEY or UNIQUE columns. Optional column name in tname, used in the referential constraint definition.
CHECK Definition Table level definition.
CONSTRAINT name Constraint names are flagged as non entry level ANSI SQL when the SQL flagger is enabled

Teradata RDBMS for UNIX SQL Reference

8-79
Teradata SQL Syntax Guide

CREATE TABLE

Syntax Element... Description
CHECK (boolean condition) boolean condition is a conditional expression, including scalar comparison predicates using, for example, any of the following: • = • <> • > • >= See also Chapter 6, “SQL Expressions.” When CHECK is part of the column definition, then a search condition cannot reference any columns other than the one being defined, nor are set specifications allowed. See also “Check Constraints” below. A table level CHECK can compare any columns defined for the table, against each other and against constants.

INDEX Definition

INDEX Keyword used to define the table’s indexes. An INDEX list is an extension to ANSI SQL. Unlike the indexes created by the UNIQUE and PRIMARY KEY constraint definitions, indexes defined by the index list may include nullable fields.
UNIQUE UNIQUE may be defined for any or all secondary indexes, and the PRIMARY index.
PRIMARY Each table must have a PRIMARY index. The primary index uses a hashing algorithm to determine where rows are stored on the AMPs of the Teradata RDBMS. If not specified, a default PRIMARY index is chosen

8-80

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

CREATE TABLE

Locks

Privileges Required and Acquired

Table Size and Maximum Columns Per Table

Compressing Columns

Assigning a Journal to the Table

Syntax Element... Description
name Designates an optional name for the index.
col_name list Designates one or more columns whose values are to be used as the basis for a primary or secondary index. If more than one name is specified, the index is based on the combined values of each column named. A maximum of 16 columns may be named for one index, and a maximum of 32 secondary indexes may be created for one table.

Column storage attributes are a Teradata RDBMS extension to ANSI SQL.

Constraint names are intermediate ANSI SQL and are flagged by the SQL flagger as non Entry Level ANSI SQL.

When a CREATE TABLE statement is entered, an EXCLUSIVE lock is placed on the table being created.

To create a table, the user must have the CREATE TABLE privilege on the database in which the table is created.

The creator receives DROP TABLE, INDEX, INSERT, UPDATE, DELETE, REFERENCE, SELECT, and DUMP and RESTORE privileges on the newly created table, WITH GRANT OPTION.

If the JOURNAL option is specified, the user also must have INSERT privilege on the journal table.

If a CREATE TABLE statement defines more than 256 columns, the Teradata RDBMS returns an error message.

The first column defined in the CREATE TABLE statement 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.

To assign a journal to a new data table, the named journal table must exist in the database being used. If it does not, a journal table can be added or created by using MODIFY DATABASE or MODIFY USER. Then CREATE TABLE can be resubmitted, or ALTER TABLE can be used to assign the new journal table to the existing data table.
Previous << 1 .. 126 127 128 129 130 131 < 132 > 133 134 135 136 137 138 .. 241 >> Next