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 .. 131 132 133 134 135 136 < 137 > 138 139 140 141 142 143 .. 241 >> Next


)

The statement in this example specifies both a primary index and primary key.

The Primary Key (field_1) is mapped to a unique secondary index (USI). The UNIQUE constraint on field_3 also is mapped to a unique secondary index. These two secondary indexes cannot be null. The Unique Primary Index (field_2) is the hashing index and can be null.

CREATE TABLE good_5

( field_1 INTEGER NOT NULL PRIMARY KEY, field_2 INTEGER,

field_3 INTEGER NOT NULL UNIQUE, field_4 INTEGER

)

UNIQUE PRIMARY INDEX (field_2

)

The statement in this example specifies a primary key only, so field_2 is mapped to a unique primary index (UPI). In this case the PRIMARY index cannot be null.

CREATE TABLE good_6 ( field_1 INTEGER,

field_2 INTEGER NOT NULL PRIMARY KEY

)

The statement in this example specifies neither a primary index nor a primary key. The RDBMS maps the first unique field, field_2, to a unique primary index (UPI), which cannot be null.

CREATE TABLE good_7

( field_1 INTEGER, field_2 INTEGER NOT NULL UNIQUE, field_3 INTEGER NOT NULL UNIQUE

)

8-92

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

CREATE TABLE

Example 11: Non-Valid Use of CHECK Constraint

Example 12: Non-Valid Use of Primary Key ‘Constraint’

Example 13: Non-Valid Use of Constraint Name

The statement in this example is not valid because it contains duplicate CONSTRAINT names (dup_name).

CREATE TABLE bad_1

( field_1 INTEGER CONSTRAINT dup_name CHECK (field_1 > 0), field_2 INTEGER NOT NULL CONSTRAINT dup_name UNIQUE

)

The system returns the error message:

Constraint with the same name "dup_name" already exists in table.

The statement in this example is not valid because it defines more than one PRIMARY KEY constraint for the table.

CREATE TABLE bad_2

( field_1 INTEGER NOT NULL CONSTRAINT primary_1 PRIMARY KEY, field_2 INTEGER NOT NULL,

PRIMARY KEY (field_2)

)

The system returns the error message:

More than one primary index or primary key specified.

The statement in this example is not valid because the CONSTRAINT name exceeds the limit of 30 characters.

CREATE TABLE bad_3 ( field_1 INTEGER NOT NULL

CONSTRAINT ThisIsAVeryLongNamedUniqueConstraint UNIQUE

)

The system returns the error message:

Name is longer than 30 characters.

Teradata RDBMS for UNIX SQL Reference

8-93
Teradata SQL Syntax Guide

CREATE TABLE

As an example, given the following CREATE TABLE statements:

Example 14: Local

´ CREATE TABLE cyk.test_1, NO FALLBACK, AFTER JOURNAL

Journaling (x INTEGER, Y INTEGER) PRIMARY INDEX(X);

CREATE TABLE cyk.test_2, NO FALLBACK,NOT LOCAL AFTER JOURNAL (x INTEGER, Y INTEGER) PRIMARY INDEX(X);

CREATE TABLE cyk.test_3, NO FALLBACK, LOCAL AFTER JOURNAL (x INTEGER, Y INTEGER) PRIMARY INDEX(X);

SHOW TABLE produces the following output:

CREATE TABLE cyk.test_1, NO FALLBACK,

NO BEFORE JOURNAL,

NOT LOCAL AFTER JOURNAL

(x INTEGER, Y INTEGER) PRIMARY INDEX(X);

CREATE TABLE cyk.test_2, NO FALLBACK,

NO BEFORE JOURNAL,

NOT LOCAL AFTER JOURNAL

(x INTEGER, Y INTEGER) PRIMARY INDEX(X);

CREATE TABLE cyk.test_3, NO FALLBACK,

NO BEFORE JOURNAL,

LOCAL AFTER JOURNAL

(x INTEGER, Y INTEGER) PRIMARY INDEX(X);

8-94

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

CREATE USER

CREATE USER

Function

The CREATE USER statement defines a new user of the Teradata RDBMS.

CREATE USER is flagged as non ANSI by the SQL flagger.

Syntax

CREATE USER—name-

I— FROM dbname —I

¦ AS-

PERMANENT ---- PERM ---

I— BYTES —I 1-,-1

A

(^PASSWORD = —T-pwd—r

Lnull-I

ÈÇ

òòòç

-STARTUP = 'string;' — SPOOL = n

I— BYTFS----1

BYTES

-DEFAULT DATABASE = dbname ------COLLATION =

-ASCII -EBCDIC

-MULTINATIONAL---- HOST------

ACCOUNT

¦ 'acctid'-

—(- 'acctid' -——, 'acctid' —¦— ) -

NO

¦ FALLBACK ¦

NO

-DUAL-

Lbefore—I

L PROTECTION —I — JOURNAL----------

NO

-DUAL-

LOCAL-

¦ AFTER JOURNAL-

— NOT LOCAL—DEFAULT JOURNAL TABLE :

tname

FF07R052

=n

Teradata RDBMS for UNIX SQL Reference

8-95
Teradata SQL Syntax Guide

CREATE USER

where:

Syntax Element... Description
name Specifies the name of the new user.
FROM Introduces an optional clause that specifies the owner dbname.
dbname Specifies the name of the immediate owner user/ database. The default is the username associated with the current session.
AS Introduces the first user options. Subsequent options may be entered in any order.
PERMANENT = n BYTES Specifies the total number of bytes (n) to be reserved for permanent storage of the new user database. The space is taken from unallocated space in the database of the immediate owner. n may be entered as an integer, a decimal value, or as a floating point value. This option must be specified. There is no default.
PASSWORD Keyword introducing the clause that specifies pwd or NULL. This option must be specified. There is no default.
Previous << 1 .. 131 132 133 134 135 136 < 137 > 138 139 140 141 142 143 .. 241 >> Next