Foreign Key Constraints

Expand / Collapse
 

Foreign Key Constraints


 

PROBLEM

Configuration data is not in sync in SQL which can cause failures to occur in the Contact Center Management website, the Contact Center Client, and Backup/Restore operations.
 
Example:  An agent group to queue association can exist in one table, but the queue has been deleted. 
This would throw a foreign key constraint error.

CAUSE
 
This is caused by SQL cascades not being triggered, which then puts our data in a bad state. 


SYMPTOMS

The associated log file will show an error like the following:

tblConfig_HuntGroupMembers : ForeignKeyConstraint tblConfig_Extension_tblConfig_HuntGroupMembers_fk_constraint requires the child key values (71cb119d-cae0-46c9-b13d-992e3b287ba0) to exist in the parent table.

  
RESOLUTION
 
Foreign Key Constraints can occur in many different tables, so undertsanding the error and scripting an answer in SQL Management Studio is usually the best solution.

Your error will have the following format: 

tblConfig_[A] : ForeignKeyConstraint tblConfig_[B]_tblConfig[A]_fk_constraint requires the child key values (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx) to exist in the parent table. 

From this you can determine which tables to search in.  Your scripted search will look like the following:

use CCMData
select * from tblConfig_[A]
where FK[B] Not in (select pkey from tblConfig_[B])
 

This will show you the records that contain Foreign Key Constraints.  You use the SELECT * option first to ensure that you don't accidentally get more records, or the wrong records.  Once you have verified this you change the SELECT * to DELETE as shown below.

use CCMData
DELETE from tblConfig_[A]
where FK[B] Not in (select pkey from tblConfig_[B])

When executed, this SQL script will delete the Foreign key Constraints for you.

EXAMPLE

Given the following error...

tblConfig_HuntGroupMembers : ForeignKeyConstraint tblConfig_Extension_tblConfig_HuntGroupMembers_fk_constraint requires the child key values (71cb119d-cae0-46c9-b13d-992e3b287ba0) to exist in the parent table.


You would get the following search:

use CCMData
SELECT * FROM tblConfig_
HuntGroupMembers
WHERE FKExtension NOT IN (SELECT PKey FROM tblConfig_Extension)


Rate this Article:
     

Add Your Comments


Name: *
Email Address:
Web Address:
Verification Code:
*
 

Details
Last Modified:Thursday, September 03, 2015
Last Modified By: dfountaine
Type: INFO
Rated 3 stars based on 3 votes.
Article has been viewed 10,964 times.
Options