1171 Object <DATABASE_NAME>.<OBJECT_NAME> is not archived because of unresolved RI

Problem:

Backup showing Error

"1171" : Object DBTgtT.PHONE_NUMBER_B is not archived because of unresolved RI.

 

Cause:

Unresolved reference constraints occur when the FK exists, but the PK does not. The DBC.Databases2 view provides a count of unresolved reference constraints for any tables within the database. Situations when an unresolved reference constraint occurs are:
• Creating a table with a Foreign Key before creating the table with the Parent Key.
• Restoring a table with a Foreign Key and the Parent Key table does not exist or hasn’t been restored.

 

 Solution:

In my case parent table was not exist after creating the missing parent table the problem solved.

 

Using the following query, we can count the total number of unresolved RI.

 

select databasename, unresolvedricount

from dbc.dbase

where databasename='DBTgtT'

and unresolvedricount <> 0;

 

Databasename

UnresolvedRICount

DBTgtT

3

                                                                       

Using the following query we can find out the parent table, child table and the child index

SELECT D.DatabaseName, T.TVMName AS childTable,

R.ParentTblName, R.ReferenceIdxName AS ChildIndexName

FROM DBC.UnresolvedReferences R

INNER JOIN DBC.Dbase D

ON R.DatabaseID = D.DatabaseID

INNER JOIN DBC.TVM T

ON T.TVMID = R.ChildTblID

where d.databasename='DBTgtT'

GROUP BY 1,2,3,4;

 

DatabaseName

ChildTable

ParentTablName       

ChildIndexName

DBTgtT         

PHONE_NUMBER_B

PHONE_NUMBER_TYPE

FK2PHONE_NUMBER_B

DBTgtT         

PURCHASE_ORDER

CANCELATION_REASON  

FK5PURCHASE_ORDER

DBTgtT         

PURCHASE_ORDER

ORDER_STATUS_RECORD

FK12PURCHASE_ORDER

 

Parent table ELEPHONE_NUMBER_TYPE, CANCELATION_REASON and ORDER_STATUS_RECORD does not exist in database.

Comments