SQL Codes › SQL Return Codes -1 through -999 › -249 - REFERENCED TABLE aaa.ttt PLUS n OTHERS IN CHECK STATUS

-249 - REFERENCED TABLE aaa.ttt PLUS n OTHERS IN CHECK STATUS

Explanation

An attempt to access a table in CHECK-RELATED state with a foreign key reference to table aaa.ttt (authid.tblname) has been rejected because the table aaa.ttt is in a check state. There are n additional referenced tables in a check state. Access is denied to the referencing table because foreign key values may have been deleted from the referenced tables in a check state.

The SQLSTATE that equates to this SQL return code is 55S01.

User Response

Use one of the following methods to take the table out of CHECK-RELATED state:

• It is possible that the table being opened has no invalid foreign key references. For example, a referenced table may have been backed up and reloaded with the same rows. If this is the case, run the DBUTLTY CONFIRM function on the table. This DBUTLTY function checks each foreign key value referencing a table in a check state, and if no invalid values are found (and, if defined, there are no CHECK constraint violations), the table is taken out of CHECK-RELATED state. If violations are found, the table is placed in CHECK-VIOLATION state, and further attempts to open the table receives SQLCODE -166, indicating the table is known to violate constraint(s).

  To avoid constraint validation, use the FORCE=YES option.

• If the CHECK-RELATED table has invalid foreign key values, you may run DBUTLTY CONFIRM,DELETE=YES to remove those rows with invalid foreign key values (and, if defined, invalid CHECK constraints), and take the table out of CHECK state.

  You may define an exception table identical to this table and use the EXCEPT=tblname option to insert rows with invalid foreign key references into the exception table for further processing.

  If the table you are confirming is also a referenced table, rows are deleted even if they are referenced. Therefore, you may need to confirm referencing tables with the DELETE=YES option to cascade the deletion to referencing tables.

• If the table being opened has valid foreign key values that do not appear in the referenced table(s), you may insert these rows into the exception table using the following procedure:

1. Execute the DBUTLTY CONFIRM,EXCEPT=tblname function.
2. Query this table to determine the foreign key values.
3. Insert rows with these values into the referenced table(s).
4. Execute the DBUTLTY CONFIRM function. If DBUTLTY does
   not find constraint violations, it takes the table out of
   CHECK-RELATED status. To avoid constraint validation,
   specify the FORCE=YES option.
   • If the referenced table is in a CHECK state because of an invalid constraint, use the SQL statement "ALTER TABLE tblname DROP CONSTRAINT constraintname" to drop the invalid constraint. When the last invalid constraint is dropped, the table is taken out of CHECK state and the referencing table is taken out of CHECK-RELATED state, unless there are other foreign constraints in the CHECK-RELATED state.

   If the table being confirmed references a table in a check state, the confirm process is aborted because subsequent confirmation of the referenced tables may delete foreign key references. Therefore, confirm a chain of related tables in "top down" sequence.