Author Topic: Cannot delete tables - a foreign key constraint fails (Read 1647 times)

nuMedia · May 03, 2008, 01:12:09 am

I'm trying to completely uninstall CiviCRM and re-install v2.0.3 (third time I've done this due to changes of hosting companies for a new site and great Civi upgrades!).

My details are:

PHP Version 5.2.5
Apache/2.2.8 (Unix) web server
Joomla! 1.0.15 Stable

All the CiviCRM-referenced menu items and content have been deleted and the component un-installed. Now, when I use phpMyAdmin to delete just the CiviCRM tables, I'm getting the following error:

#1217 - Cannot delete or update a parent row: a foreign key constraint fails

I searched the forum and found the following thread although it was for Drupal:
http://forum.civicrm.org/index.php/topic,1257.0.html

But, when I run the query (the last method in the thread, customizing the table names to my setup below), I get an error message "You have to choose at least one column to display." Even when I do that and run the Query again, that one column I chose shows up in the Query text field. Still hoping that the 0s would have been changed to 1s and I could drop the tables, no luck.

I am NOT savvy in the phpMyAdmin environment so I don't know what to try, or look for, next. Nor am I sure I'm even doing the query correctly (scary!). Any help is greatly appreciated. I'm posting my tables below in the Query statement I have used — maybe it will provide some clues.

Thank you in advance for your help!
nuMedia

Code: [Select]

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE
civicrm_address,
civicrm_contact,
civicrm_contribution_type,
civicrm_country,
civicrm_county,
civicrm_currency,
civicrm_domain,
civicrm_email,
civicrm_event,
civicrm_group,
civicrm_household,
civicrm_im,
civicrm_location_type,
civicrm_loc_block,
civicrm_mailing,
civicrm_mailing_component,
civicrm_mailing_event_queue,
civicrm_mailing_job,
civicrm_mapping,
civicrm_membership,
civicrm_membership_status,
civicrm_membership_type,
civicrm_msg_template,
civicrm_option_group,
civicrm_participant,
civicrm_payment_processor,
civicrm_phone,
civicrm_premiums,
civicrm_relationship_type,
civicrm_saved_search,
civicrm_state_province
civicrm_worldregion;
SET FOREIGN_KEY_CHECKS = 1;

Deepak Srivastava · May 03, 2008, 04:19:22 am

I have a similar script ready, which when used removes all the civicrm tables in one go.

The script is taken from phpMyAdmin itself (except key_checks part).

Code: [Select]

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE IF EXISTS `civicrm_acl`, `civicrm_acl_cache`, `civicrm_acl_entity_role`, `civicrm_activity`, `civicrm_activity_assignment`, `civicrm_activity_target`, `civicrm_address`, `civicrm_case`, `civicrm_case_activity`, `civicrm_component`, `civicrm_contact`, `civicrm_contribution`, `civicrm_contribution_page`, `civicrm_contribution_product`, `civicrm_contribution_recur`, `civicrm_contribution_type`, `civicrm_contribution_widget`, `civicrm_country`, `civicrm_county`, `civicrm_currency`, `civicrm_custom_field`, `civicrm_custom_group`, `civicrm_dedupe_rule`, `civicrm_dedupe_rule_group`, `civicrm_domain`, `civicrm_dupe_match`, `civicrm_email`, `civicrm_entity_file`, `civicrm_entity_tag`, `civicrm_event`, `civicrm_event_page`, `civicrm_file`, `civicrm_financial_trxn`, `civicrm_grant`, `civicrm_group`, `civicrm_group_contact`, `civicrm_group_nesting`, `civicrm_group_organization`, `civicrm_im`, `civicrm_line_item`, `civicrm_location_type`, `civicrm_loc_block`, `civicrm_log`, `civicrm_mailing`, `civicrm_mailing_bounce_pattern`, `civicrm_mailing_bounce_type`, `civicrm_mailing_component`, `civicrm_mailing_event_bounce`, `civicrm_mailing_event_confirm`, `civicrm_mailing_event_delivered`, `civicrm_mailing_event_forward`, `civicrm_mailing_event_opened`, `civicrm_mailing_event_queue`, `civicrm_mailing_event_reply`, `civicrm_mailing_event_subscribe`, `civicrm_mailing_event_trackable_url_open`, `civicrm_mailing_event_unsubscribe`, `civicrm_mailing_group`, `civicrm_mailing_job`, `civicrm_mailing_spool`, `civicrm_mailing_trackable_url`, `civicrm_mapping`, `civicrm_mapping_field`, `civicrm_membership`, `civicrm_membership_block`, `civicrm_membership_log`, `civicrm_membership_payment`, `civicrm_membership_status`, `civicrm_membership_type`, `civicrm_msg_template`, `civicrm_note`, `civicrm_openid`, `civicrm_openid_associations`, `civicrm_openid_nonces`, `civicrm_option_group`, `civicrm_option_value`, `civicrm_participant`, `civicrm_participant_payment`, `civicrm_payment_processor`, `civicrm_payment_processor_type`, `civicrm_phone`, `civicrm_preferences`, `civicrm_preferences_date`, `civicrm_premiums`, `civicrm_premiums_product`, `civicrm_price_field`, `civicrm_price_set`, `civicrm_price_set_entity`, `civicrm_product`, `civicrm_project`, `civicrm_relationship`, `civicrm_relationship_type`, `civicrm_saved_search`, `civicrm_state_province`, `civicrm_subscription_history`, `civicrm_tag`, `civicrm_task`, `civicrm_task_status`, `civicrm_tell_friend`, `civicrm_timezone`, `civicrm_uf_field`, `civicrm_uf_group`, `civicrm_uf_join`, `civicrm_uf_match`, `civicrm_worldregion`;
SET FOREIGN_KEY_CHECKS = 1;

nuMedia · May 03, 2008, 09:52:21 am

Eureka! Thank you, Deepak!

I copied your script and tried again but received the same "you have to choose at least one column to display" error. Then, I started looking around and found that the SQL tab provided a "Run SQL query/queries on database (name):" and so I pasted your code in there and "GO" and DONE -- CiviCRM tables all gone.

I was using the Query tab before so I don't know if that other code didn't work because I was in the wrong place or what...? Yours was somewhat different with the continuous comma-delimited format and the 'table_name' enclosure. So... I'm keeping your code for the future because I'm sure it will be helpful again.

Thank you again! I'm good-to-go now.
nuMedia

CiviCRM Community Forums (archive)

News:

Author Topic: Cannot delete tables - a foreign key constraint fails (Read 1647 times)

nuMedia

Cannot delete tables - a foreign key constraint fails

Deepak Srivastava

Re: Cannot delete tables - a foreign key constraint fails

nuMedia

Re: Cannot delete tables - a foreign key constraint fails