Data Consistency Check

Document ID : KB000087676
Last Modified Date : 14/04/2018
Show Technical Document Details
Issue:
Error Message :
ORA-02270: no matching unique or primary key for this column-list

The data model of the Automation Engine (AE) database includes some foreign key constraints.
A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables. You can create a foreign key by defining a FOREIGN KEY constraint when you create or modify a table.

In a foreign key reference, a link is created between two tables when the column or columns that hold the primary key value for one table are referenced by the column or columns in another table. This column becomes a foreign key in the second table.
 
Sometime it is necessary to disable a constraint on a table (e.g. for testing purposes).  However, once testing is complete the constraint must be enabled again.  Sometimes this step is forgotten, so it is good to confirm that the database is in a consistent state which means that there are no records that exist in a child table without a parent record.
 
Solution

To check if the data is consistent the attached script was developed (DataConsistencyCheck.sql).

There are 3 variables where the following default values are defined (this values can be changed with a text editor like notepad++):
  1. OWNER_NAME='UC4'
  2. Parent_Table_Name='%'
  3. Child_Table_Name='%'
You should change at least the variable 'OWNER_NAME' to the name of your AE schema.

The variables 'Partent_Table_Name' and 'Child_Table_Name' can be empty because the default value is '%'.

Execute the script DataConsistencyCheck.sql in SQL*Plus from the command line:
 
0EMb0000001QrG5.png

The result is a script (data_cons_check.sql) created in the same directory where the script DataConsistencyCheck.sql is executed.

You can execute the generated command script in data_cons_check.sql to check if there is any data in a child table with a non-existant parent record.

e.g.: select ABLOB_AH_IDNR from ABLOB MINUS select AH_IDNR from AH;

If the result of the command is 'no rows selected' everthing is fine.

If any other data is returned,  you will want to correct those records.  In order to do that, you have to execute the generated query in column 'delete_command'.

 
e.g.: delete from RH where RH_AH_IDNR in (select RH_AH_IDNR from RH MINUS select AH_IDNR from AH);

0EMb0000001QrGe.png
 
Cause:
Cause type:
Configuration
Root Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement gives a column-list for which there is no matching unique or primary key constraint in the referenced table.
Resolution:
This field was added on 30/03/2017. This article has not been updated yet. Refer to the "Description" or "Workaround" sections for solution information.

Fix Status: No Fix

Additional Information:
Workaround :
N/A
File Attachments:
DataConsistencyCheck.sql