Scenario for Data Corruption With Referential Constraints - Advanced SQL Engine - Teradata Database

Always exercise caution when manipulating data within an unenforced Referential Constraints environment. If you define referential relationships using unenforced Referential Constraints, then INSERT, DELETE, and UPDATE statements can corrupt data in the parent tables of those relationships if the DML request contains a redundant RI join and the PK-FK row pairs for the eliminated join do not match. The RI join is eliminated based on the assumption that PK=FK, and non-valid results are returned in SELECT results or incorrect updates are made to the database.

For example, suppose you have the following 2 tables related by means of a Referential Constraint between col_2 in child table table_2 and col_1 in parent table table_1.

    CREATE TABLE table_1 (
     col_1 INTEGER NOT NULL,
     col_2 INTEGER
    UNIQUE PRIMARY INDEX (col_1));

    CREATE TABLE table_2 (
     col_1 INTEGER,
     col_2 INTEGER,
    FOREIGN KEY (col_2) REFERENCES WITH NO CHECK OPTION table_1(col_1));

Insert some rows into table_1 and table_2. Assume that the populated tables now look like this.

The defined referential relationship between the tables specifies that each value for col_2 in table_2 should correspond to an identical value in col_1 of table_1.

Because the system is instructed not to enforce referential integrity for this relationship, the unpaired table_2.col_2 values are accepted for insertion even though they violate the defined constraint on the relationship. At this point, referential integrity errors have been introduced, but equally importantly, the preconditions for further data corruption have been set.

Suppose you now want to update table_2 using the following UPDATE statement.

    UPDATE table_2
    SET col_1=1
    WHERE table_1.col_1=table_2.col_2;

Because table_2.col_2 references table_1.col_1 WITH NO CHECK OPTION, this update eliminates the table_1.col_1 = table_2.col_2 join, thus ignoring the specified WHERE clause condition filter, and then incorrectly updates all rows in table table_2 with a col_1 value of 1, corrupting the database.