Author Topic: Problem adding constraints to exported CiviCRM database (Read 818 times)

Michael McAndrew · October 26, 2009, 10:32:41 am

Hello,

I have a problem when trying to .

This is a 'legacy' civicrm database - one where i did a little fiddling around in the days before I learnt to use to api for any type of mucking around with contact records etc.

Here is the problem. I export the database using phpmyadmin. I try and recreate it. It hits a problem when it gets to adding the constraints. The constraint it wants to add is the following:

"ALTER TABLE `civicrm_address`
ADD CONSTRAINT `FK_civicrm_address_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE,
ADD CONSTRAINT `FK_civicrm_address_country_id` FOREIGN KEY (`country_id`) REFERENCES `civicrm_country` (`id`) ON DELETE SET NULL,
ADD CONSTRAINT `FK_civicrm_address_county_id` FOREIGN KEY (`county_id`) REFERENCES `civicrm_county` (`id`) ON DELETE SET NULL,
ADD CONSTRAINT `FK_civicrm_address_state_province_id` FOREIGN KEY (`state_province_id`) REFERENCES `civicrm_state_province` (`id`) ON DELETE SET NULL;
"

The error produced is the following: "Cannot add or update a child row: a foreign key constraint fails (`vaw_mad`.`#sql-de_16`, CONSTRAINT `FK_civicrm_address_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)
"

I'm presuming that is because there is an address in the address table that doesn't have a counterpart in the contact table. But I presume that is allowed to happen because some addresses (like the ones used as event locations) don't have contact ids and as Lobo said on the IRC, this FK is option.

So I'm confused as to why this is happening. Anyone shed any light?

Thanks a lot,
Michael

Michael McAndrew · October 26, 2009, 10:55:11 am

Paraphrasing the solution that continued on IRC

[17:43] dlobo: so most likely in your legacy db there is a contact id that does not exist
[17:44] dlobo: and hence the FK error
[17:45] michaelmcandrew: ok - i will work out the appropriate join
[17:45] dlobo: the FK rule is: if there is a contact id in that column it better exist
[17:45] michaelmcandrew: i did think that was the case.
[17:45] dlobo: just do a left join and ensure that there is one for every id that is not null
[17:46] michaelmcandrew: i'm pretty sure i did that - will try again...
[17:46] kurund joined the chat room.
[17:46] michaelmcandrew: oh, actually - i didn't quite get the WHERE clause right, I don't think.
[17:47] michaelmcandrew: it should be WHERE "con.id IS NULL and add.contact_id IS NOT NULL"

So the SQL to find these stray addresses is:

"DELETE ca
FROM civicrm_address AS ca
LEFT JOIN civicrm_contact AS cc
ON ca.contact_id=cc.id
WHERE cc.id IS NULL and ca.contact_id IS NOT NULL"

Thanks for the help, Lobo!

CiviCRM Community Forums (archive)

News:

Author Topic: Problem adding constraints to exported CiviCRM database (Read 818 times)

Michael McAndrew

Problem adding constraints to exported CiviCRM database

Michael McAndrew

Re: Problem adding constraints to exported CiviCRM database