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 Database Administration - NCR

NCR Teradata RDBMS Database Administration - NCR , 2004. - 616 p.
Download (direct link): teradatadatabaseadmin2004.pdf
Previous << 1 .. 97 98 99 100 101 102 < 103 > 104 105 106 107 108 109 .. 218 >> Next

If you plan to bypass references checking, you may prefer to use a procedural constraint, such as a set of triggers, to handle inserts, updates, and deletions. If you do not enforce some type of reliable integrity constraint, NCR recommends you establish a validation procedure that includes:

A query to test for and report referential integrity violations

A transaction to correct the reported violations

The query should be run every time data is changed. A good validation procedure could be based on the following:

Step

Action

Run one DML operation, such as an update, on RI/NO CHECK tables.

Before performing another operation on those tables, run the validation query against each of them to find corrupt rows.

The following query structure reports every row in the Child table with a foreign key value that does not match a primary key value in the Parent table. (Foreign key nulls are specifically excluded because it is not possible to determine what values they represent.)

SELECT DISTINCT childtablename.* FROM childtablename, parenttablename WHERE childtablename.fkcol NOT IN (SELECT pkcol FROM parenttablename) AND childtablename.fkcol IS NOT NULL;

7 - 10

Teradata RDBMS Database Administration

1

2 Chapter 7: Protecting Data

Using Referential Integrity

Step Action
3 Delete from the Child table any reported rows as soon as possible in order to maintain the integrity of your database.
4 Run the validation query again immediately after performing any updates or deletes on RI tables using WITH NO CHECK OPTION.

For detailed examples and a thorough discussion of your responsibilities in maintaining data integrity when you use REFERENCES WITH NO CHECK OPTION, see the following:

Under "CREATE TABLE (Column Definition Clause)" in Teradata RDBMS SQL Reference, Volume 4:

"Validating the Integrity of Tables in an SRI Relationship"

"Scenario for Data Corruption With SRI"

Differences in using declarative constraints versus procedural constraints (such as triggers), under "Designing for Database Integrity" in Teradata RDBMS Database Design

Table Header Referential Indexes (RIs)

When you define REFERENCES for a table, information about referential integrity is maintained as index fields in the table headers of both the Child and the Parent tables.

The RI in a Child table is made up of the following fields:

Child RI Fields Contents |

IndexID Reference index number. |

IndexType The AMP index type (AMPIT), which for this index is REFERENCE. |

State Describes the current state of the RI as one of the following: |

This value . Indicates a state that is .
validRI normal
invalidRI the result of a rollforward or rollback.
inconsistentRI the consequence of a RESTORE operation. (Use the ARC REVALIDATE REFERENCES FOR command to revalidate the index.)
unresolvedRI the result of a forward reference, where a Child table references a non-existent Parent table. Once the Parent table is created, the unresolvedRI value changes to validRI.

7 - 10 Teradata RDBMS Database Administration

Chapter 7: Protecting Data

Using Referential Integrity

Child RI Fields Contents
DBID Identifies the database in which the Parent table resides (the Parent owner database). The value depends on the state of this Child table:
IF the state of this table is . THEN the value of DBID is .
valid, invalid, or inconsistent ID of the database in which the Parent table resides.
unresolved Name of the Parent table, until the Parent table is created.

ChildEntry The value TRUE.
TableID Identifies the Parent table, based on the state of this Child table:
IF the state of the table is . THEN the value of TableID is .
valid, invalid, or inconsistent ID of the Parent table.
unresolved AMPNullUsrTblID (null), until the Parent table is created. At that time, the state of this table becomes valid.

SecIdxId The SI number of the corresponding PK or alternate key column, based on the definition of the key columns and the state of this table:
IF . THEN the value of SecIdxID is .
the PK or alternate key column is not the PI, or is a non-unique PI (NUPI or NUPPI) the SI number of the corresponding PK or alternate key column.
the PK or alternate key column is defined as the PI and unique (a UPI or UPPI) 0 (zero).
this table is in an unresolved state 0 (zero), until the Parent table is created.

FKFields Describes the FK columns.

7 - 10 Teradata RDBMS Database Administration Chapter 7: Protecting Data

Using Referential Integrity

The RI in a Parent table is made up of the following fields:

Parent RI Fields Contents
IndexID Reference index number.
IndexType The AMPIT, which for this index is REFERENCE.

State

Describes the current state of the referential index (RI) as one of:

This value . Indicates a state that is .
ValidRI normal
InvalidRI the consequence of a rollforward or rollback operation.
Previous << 1 .. 97 98 99 100 101 102 < 103 > 104 105 106 107 108 109 .. 218 >> Next