Data Consistency Check
Article ID: 87676
Updated On:
Products
CA Automic Workload Automation - Automation Engine
Issue/Introduction
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++):
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:
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'.
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++):
- OWNER_NAME='UC4'
- Parent_Table_Name='%'
- Child_Table_Name='%'
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:
<Please see attached file for image>
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);
<Please see attached file for image>
Environment
Release: AOATAM99000-9.0-Automic-One Automation Tools-Application Manager
Component:
Component:
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.
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
Fix Status: No Fix
Additional Information
Workaround :
N/A
N/A
Attachments
Feedback
Yes
No
Powered by
