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 .. 107 108 109 110 111 112 < 113 > 114 115 116 117 118 119 .. 241 >> Next

ADD col_name CHECK does not already have a
(search condition) constraint.
MODIFY does have a constraint.

• The following form drops all unnamed column level CHECK constraints on col_name.

alter table tname drop col_name CHECK

• The following form drops all unnamed table level CHECK constraints on the table name.

ALTER TABLE tname DROP CHECK

• Either of the following is legitimate syntax for dropping a

named CHECK constraint:

drop constraint name CHECK drop constraint name

• The following form is valid only if a constraint with name already exists in the table.

modify constraint name check (search condition)

This also applies to the named constraints defined as part of the column definition because those constraints are handled as named table level constraints.

• 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.

• You can add a primary key (using ALTER TABLE) if and only if no primary key is already defined for the table.

There are no exceptions to this rule.

The added primary key always maps to a unique secondary index (USI).

• You can drop a PRIMARY KEY constraint if and only if it is mapped to a unique secondary index (USI).

8-18

Teradata RDBMS for UNIX SQL Reference
Teradata SQL Syntax Guide

ALTER TABLE

Adding or Dropping Referential Integrity Constraints

Maintaining Foreign Key Integrity

ADDING a Foreign Key

• Use the DROP INDEX statement to drop unnamed unique constraints.

• Details of adding and dropping constraints with ALTER TABLE are explained in the following table.

IF you perform this statement . . . THEN you add/drop the following number of unnamed table level CHECK constraints . . .
ALTER TABLE ... DROP CHECK all
anything else one per ALTER TABLE statement, regardless of constraint type.

ALTER TABLE ... ADD UNIQUE/PRIMARY KEY requires the following rights:

• DROP TABLE on the table or its database

• INDEX on the table

Referential integrity provides for specification of columns within a referencing table that are foreign keys for columns in some other referenced table.

The columns in the referenced table must be defined either as the primary key columns for the table or have the unique attribute defined for them.

The foreign key columns in the referencing table must be identical in definition with the keys in the referenced table.

See also “Referential Integrity” in Chapter 9, “Advanced SQL.”

The COMPRESS option is not allowed on either column. Column level constraints are not compared for the columns.

The Teradata RDBMS verifies that the structure of columns defined as foreign keys or referenced by foreign keys, is not changed so as to violate the rules for definition of a foreign key constraint.

An ALTER TABLE (or DROP INDEX) statement attempting to change the structure of such a column causes an error to be returned by the Teradata RDBMS.

The Teradata RDBMS verifies that a table referenced by another is not dropped. Before dropping the table, an ALTER TABLE statement must be executed to drop the foreign key reference.

If an ALTER TABLE statement is executed to ADD a foreign key reference, then the RDBMS validates all the values in the foreign key columns against columns in the referenced table.

Teradata RDBMS for UNIX SQL Reference

8-19
Teradata SQL Syntax Guide

ALTER TABLE

Referential Integrity When a Table is Inconsistent

When the ALTER TABLE statement is executed, an error table with the same columns and PRIMARY KEY as the ALTER TABLE target table is created.

The name of the error table is the target table name suffixed with the reference index number. A reference index number is assigned to each foreign key constraint for a table. This number can be determined by using a new system view.

The error table is created under the same user/databases the target table. If a table already exists with the same name as that generated for the error table, then an error is returned to the ALTER TABLE statement.

The RDBMS does not return an error if rows in the referencing table contain values in the foreign key columns which cannot be found in any row of the referenced table. Instead, a row is inserted into the error table for each such row found in the target table.

Correction of data values in the referenced or referencing tables so that full referential integrity exists between the two tables is the responsibility of the user. The rows in the error table can be used to define which corrections must be made. Maintenance of the error table is also the responsibility of the user.

While a table is marked as inconsistent, no updates inserts, or deletes are permitted. The table is fully usable only when the inconsistencies are resolved.
Previous << 1 .. 107 108 109 110 111 112 < 113 > 114 115 116 117 118 119 .. 241 >> Next