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 .. 42 43 44 45 46 47 < 48 > 49 50 51 52 53 54 .. 241 >> Next


• UNIQUE

• CHECK (boolean expression)

• REFERENCES tablename [(column name list)]

Table Level Constraints

Table level constraints include:

• FOREIGN KEY (column name list)

• PRIMARY KEY

• UNIQUE

• CHECK (boolean expression)

• REFERENCES tablename [(column name list)]

Teradata RDBMS for UNIX SQL Reference

5-47
Data Definition

Constraints

Introduction

Constraints

Constraints may be categorized as any of the following

• Uniqueness

• CHECK

• Referential Integrity

UNIQUE means the table can not include two or more rows where the values for the column or set of columns are identical.

PRIMARY KEY

A column or set of columns defined as PRIMARY KEY, must also be NOT NULL. The Teradata RDBMS instantiates a PRIMARY KEY as a UNIQUE [PRIMARY] INDEX. Only one PRIMARY KEY can be defined for a table. Refer to “CREATE TABLE” on page 8-69 for more details.

UNIQUE

A column or set of columns defined as UNIQUE and which cannot be NULL. Queries are flagged as not Entry Level ANSI when the SQL flagger is enabled if any listed column is not also defined as NOT NULL. The Teradata RDBMS instantiates UNIQUE as a UNIQUE [PRIMARY or SECONDARY] INDEX.

CHECK CONSTRAINTS

CHECK CONSTRAINTS compare values of a field or fields in the same row with constants or other fields. CHECK CONSTRAINTS are applied to rows generated as candidates for INSERT and UPDATE operations.

If the condition is met or the proposed INSERT or UPDATE contains NULLs, the operation is permitted.

If the condition is not met, an error is reported as ‘constraint violation’. Refer to “CREATE TABLE” on page 8-69 for details on the syntax.

Examples:

CHECK (Sex = 'F' OR Sex = 'M')

CHECK (EdLev BETWEEN 0 AND 22 AND YrsExp BETWEEN 0 and 70)

REFERENTIAL

CONSTRAINTS

REFERENTIAL CONSTRAINTS are used to indicate relationships between columns of different tables. They are used to enforce referential integrity on related tables. Refer to “CREATE TABLE” on page 8-69 and “ALTER TABLE” on page 8-7 and also Chapter 9, “Advanced SQL” in the section “Referential Integrity”

5-48

Teradata RDBMS for UNIX SQL Reference
Introduction

Truncation of Japanese Character Fields

Data Definition

FORMAT Phrase

FORMAT Phrase

The FORMAT phrase is flagged as non-ANSI when the SQL flagger is enabled.

The FORMAT phrase controls the display of expressions and column data. Formats can be specified for columns that have character, graphic, numeric, byte, or date data types.

The FORMAT phrase is also used in controlling conversions between data types (character to numeric, numeric to character,

e.t.c.)

Note: FORMAT pertains to data exported in report form, as is the case in BTEQ. FORMAT does not control internal storage representation of data, or data returned in record or indicator variable mode.

It is possible to truncate the output of character fields within multibyte characters when you use any of the following character sets:

• KanjiEBCDIC

• KanjiEUC

• KanjiShift-JIS

In Teradata mode, the system does not tell you this has happened. returns the number of bytes specified. The table below shows the hexadecimal returns. The actual return display depends on which system is used.

This character . . . With this format . . . Returns this values in hexadecimal . . . With this character set . . .
B<AA> ‘xx’ C20E KanjiEBCDIC
BAA ‘xx’ 42A3 KanjiEUC

In ANSI mode, an error occurs if a non-blank character is truncated.

Graphic formats are discussed in the following section.

A FORMAT specification cannot contain more than 30 characters; the specification must be enclosed by apostrophes.

A FORMAT phrase can describe up to 18 digit positions (17 if the FORMAT contains an E). IEEE 64-bit floating numbers are accurate to about 15 digits.

Teradata RDBMS for UNIX SQL Reference

5-49
Data Definition

FORMAT Phrase

Example 1

Example 2 Without FORMAT Clause

Example 3: With FORMAT Clause

Example 4: Using FORMAT to Change the Format of Returned Data

The FORMAT phrase can be used in a CREATE TABLE statement or ALTER TABLE statement to define the display format for a column. It can also be used in a retrieval statement to override the default format of a column or to define the display format of an expression. See also the following sections “Graphic Formats”, “Numeric Formats”, “DATE Formats”, and “Data Default Formats”, as well as the earlier descriptions under each data type, for default format examples.

The following are simple examples of the FORMAT phrase:

FORMAT '9(5)', FORMAT 'Z999', FORMAT 'XX'

If the CREATE TABLE statement defined the format for the Salary column as ’ZZ9999.99’, the result of the statement:

SELECT SUM(Salary) FROM Employee;

is:

SUM(Salary)

851100.00

The result of the following statement:

SELECT SUM(Salary) (FORMAT '$$99,999.99')

FROM Employee;

is:

SUM(Salary)

$851,100.00

FORMAT phrases, by themselves, cannot cause conversion of character to numeric data, or numeric to character data. An error message is returned if a FORMAT phrase implies data conversion.
Previous << 1 .. 42 43 44 45 46 47 < 48 > 49 50 51 52 53 54 .. 241 >> Next