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 .. 186 187 188 189 190 191 < 192 > 193 194 195 196 197 198 .. 241 >> Next


The FOREIGN KEY columns in the referencing table must be identical in definition with the keys in the referenced table. Corresponding columns must have the same data type and case sensitivity only.

• The Teradata RDBMS COMPRESS option is not allowed on either the referenced or referencing column(s).

• Column level constraints will not be compared.

9-20

Teradata RDBMS for UNIX SQL Reference
Advanced SQL

Referential Integrity

Circular References Are Allowed

References Can Be to the Table Itself

CREATE and ALTER TABLE Syntax

Maintaining Foreign Keys

• A one column FOREIGN KEY cannot reference a single column in a multi-column primary or unique key; the foreign and primary/unique key must contain the same number of columns.

References can be defined as circular in that TableA can reference TableB which can reference TableA. In this case, at least one set of FOREIGN KEYS must be defined on columns which are nullable.

If the FOREIGN KEYS in TableA are on columns defined as nullable, then rows could be inserted into TableA with null values for the FOREIGN KEY columns. Once the appropriate rows exist in TableB, the null values of the FOREIGN KEY columns in TableA could then be updated to contain non-null values which match the TableB values.

FOREIGN KEY references can also be to the same table that contains the FOREIGN KEY. The referenced columns must be different columns than the FOREIGN KEY, and both the referenced and referencing columns must subscribe to the referential integrity rules.

The addition of Referential Integrity affects the syntax and semantics of CREATE TABLE and ALTER TABLE. Refer to these sections in Chapter 8, “Teradata SQL Syntax Guide” for more details.

Definition of a FOREIGN KEY requires that the Teradata RDBMS maintain the integrity defined between the referenced and referencing table.

Teradata RDBMS verifies that if:

• A Row is Inserted into Referencing Table & FOREIGN KEY Columns are Not NULL:

• that a row exists in the referenced table with the same values as those in the FOREIGN KEY columns.

• if such a row does not exist, then the Teradata RDBMS returns an error to the attempt to insert the row.

Note: If the FOREIGN KEY contains multiple columns, and if any one column value of the FOREIGN KEY is null, then none of the FOREIGN KEY values are validated.

• The Value(s) in FOREIGN KEY Column(s) are Updated to NonNULL:

• then a row must exist in the referenced table which contains value(s) equal to the modified value(s) of all of the foreign key column(s).

Teradata RDBMS for UNIX SQL Reference

9-21
Advanced SQL

Referential Integrity

• If such a row does not exist then the Teradata RDBMS will return an error to the attempt to update the row.

• Before a Row is Deleted from a Referenced Table:

• that no rows exist in referencing tables with foreign key values equal to those of the row to be deleted.

• If such rows exist then an error will be returned to the attempt to delete the row.

• Before a Referenced Column in a Referenced Table is Updated:

• that no rows exist in a referencing table with foreign key values equal to those of the referenced columns.

• If such rows exist then an error will be returned to the attempt to update the row.

• The Structure of Column 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 such a columns structure will cause an error to be returned by the Teradata RDBMS.

• A table which is referenced by another is not dropped.

Before dropping the referenced table, an ALTER TABLE statement must be executed against the referencing table, to drop the FOREIGN KEY reference.

• If an ALTER TABLE Statement is Executed to Add a FOREIGN KEY Reference:

All of the values in the foreign key columns are validated against columns in the referenced table.

• An error table is generated when ALTER TABLE is parsed.

• The error table is defined with the same columns and PRIMARY KEY as the target table of the ALTER TABLE statement.

• 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. The number can be determined through use of one of the following system views:

• RI_Child_Tables

• RI_Distinct_Children

• RI_Distinct_Parents

• RI_Parent_Tables

9-22

Teradata RDBMS for UNIX SQL Reference
Advanced SQL

Referential Integrity

Archive and Restore of Tables

• The error table is created under the same user/database as the table being altered. 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 Teradata 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.
Previous << 1 .. 186 187 188 189 190 191 < 192 > 193 194 195 196 197 198 .. 241 >> Next