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


Teradata RDBMS for UNIX SQL Reference

8-81
Teradata SQL Syntax Guide

CREATE TABLE

If neither SET or MULTISET is specified explicitly, then assignment SET and MULTISET is made according to the rules in the following table.

IF the session creating the table is in this mode . . . THEN this keyword is implied . . .
ANSI MULTISET
Teradata SET

Constraint Names

Constraint name must conform to the rules for a Teradata name (30 characters maximum) and be unique among all other constraint names specified in the table.

The characters used can be:

• uppercase and lowercase alphabetic characters

• integer characters

• - , #, or $

Unnamed constraints will not be assigned system-generated names.

CHECK Constraints

The Teradata RDBMS supports the CHECK constraint both at the column and at the table level. It allows constraints that reference one or more fields in a data row.

The following rules apply to CHECK constraints.

• If a CHECK constraint is added as part of the column specification then the “boolean” (search) condition cannot reference any columns other than the one being defined.

• If more than one unnamed CHECK constraint is defined for a column in the column specification, they are combined into a single column constraint.

However, each named column-level CHECK constraint is handled as though it is a table-level named CHECK constraint.

The only difference between defining a named CHECK constraint in the column definition and table definition is that when a named CHECK constraint is defined in the column definition, it must satisfy the rule for column constraints described under the first bullet in this list.

• To ensure maximum system performance, there is a limit of 100 table-level constraints that can be defined for any table.

A combination of table-level, column-level, and WITH CHECK on view constraints may create a constraint expression that is too large to be parsed for INSERT and UPDATE statements.

8-82

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

CREATE TABLE

• Unnamed CHECK constraints with identical wording and case are considered to be duplicates, and the system returns an error stating ‘Check already exists’ in those cases.

For example, the CREATE TABLE statement

CREATE TABLE t1 (f1 int, check (f1 > 0, check (F1 >0));

is valid because the case of f1 and F1 is different, but the CREATE TABLE statement

CREATE TABLE t1 (f1 int, check (f1 > 0), check (f1 > 0));

is not valid and returns the error message.

• The Boolean (search) condition for a table level CHECK constraint can not reference columns from other tables. Also, aggregate functions are not allowed.

BETWEEN ... AND

The Teradata RDBMS construct of: between number and number

is still supported, and treated as equivalent to: check (field-name between number and number)

New applications should use the (ANSI compliant) CHECK syntax.

FOREIGN KEYS

If a FOREIGN KEY is specified in a table-constraint, the FOREIGN KEY column-name-list must refer to columns defined within the CREATE TABLE statement for the target table, and the same column name should not be included in the FOREIGN KEY definition more than once.

The column-name-list should be identical to a list of columns in the referenced table which are defined either as the primary key columns or as a set of columns with the unique attribute. Table-name refers to the referenced table which must be a base table, and not a view.

The following rules apply to foreign keys.

• A maximum of 64 FOREIGN KEYS can be defined on a single table. This includes all references to and from the table. If 10 tables (t1, t2, ... t10) reference table tx, only 64 - 10 = 54 additional FOREIGN KEYS can be defined on table tx.

• Each FOREIGN KEY can consist of up to 16 columns.

Each column in the REFERENCES clause should identify a column of the referenced table, and the same column name should not be identified more than once.

Teradata RDBMS for UNIX SQL Reference

8-83
Teradata SQL Syntax Guide

CREATE TABLE

The following rules apply to column- and table-level rules for REFERENCES.

• The referencing column list should contain the same number of column names as the referenced column list. The i-th column of the referencing list corresponds to the i-th column identified in the referenced list. The data type of each referencing column should be the same as the data type of the corresponding referenced column.

• The user issuing the CREATE TABLE statement containing a REFERENCES option must have the REFERENCE right on the referenced table or on all specified columns of the referenced table.

• If REFERENCES is specified in a column-constraint, table-name defines the referenced table. It must refer to a base table, and not a view.

The following rules apply to column-level rules for REFERENCES.

• If the col_name is omitted then the referenced table must be defined with a single column, primary key and the foreign key column references the primary key column of the referenced table.
Previous << 1 .. 127 128 129 130 131 132 < 133 > 134 135 136 137 138 139 .. 241 >> Next