CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site
This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM (Moderator: Dave Greenberg) »
  • Foriegn Key Restraint Error when deleting a record
Pages: [1]

Author Topic: Foriegn Key Restraint Error when deleting a record  (Read 2682 times)

ryanpitt

  • I post occasionally
  • **
  • Posts: 42
  • Karma: 0
Foriegn Key Restraint Error when deleting a record
November 05, 2008, 07:19:20 pm
Hi All,
Anyone have any ideas what is causing this error and how to fix the problem?
Error message is pasted below.
I am using Drupal 6.6 and CiviCRM 2.1.1.
Thanks
Ryan

Delete Contact

unrecoverable error
    Sorry. A non-recoverable error has occurred.

    DB Error: constraint violation

    Database Error Code: Cannot delete or update a parent row: a foreign key constraint fails (`civicrmtest/civicrm_note`, CONSTRAINT `FK_civicrm_note_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`)), 1451

    Return to home page.

Error Details:

Array
(
    [callback] => Array
        (
           
  • => CRM_Core_Error
    • [1] => handle
            )

       
Code: [Select]
=> -3
    [message] => DB Error: constraint violation

    [mode] => 16
    [debug_info] => DELETE FROM civicrm_contact  WHERE (  civicrm_contact.id = 9537 )  [nativecode=1451 ** Cannot delete or update a parent row: a foreign key constraint fails (`civicr

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Foriegn Key Restraint Error when deleting a record
November 05, 2008, 07:57:21 pm

1. is this a new install or an upgrade

2. what version of mysql are you using. can you check your civicrm_note table schema definition. The 2.1 (and 2.0) version has a cascading constraint built in to set contact id to null

Code: [Select]
CONSTRAINT FK_civicrm_note_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact(id) ON DELETE SET NULL

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

ryanpitt

  • I post occasionally
  • **
  • Posts: 42
  • Karma: 0
Re: Foriegn Key Restraint Error when deleting a record
November 13, 2008, 11:33:50 am
Hi Donald,

Sorry it took so long to get back to you!

1. This is an upgrade from 2.0.

2. MySQL is Version 5.0.22.  Not sure what you are asking for on the rest of #2.  Could you clarify this for me?

Thanks
Ryan

ryanpitt

  • I post occasionally
  • **
  • Posts: 42
  • Karma: 0
Re: Foriegn Key Restraint Error when deleting a record
November 13, 2008, 11:44:57 am
Could this be what you are looking for?

Code: [Select]
-- Host: localhost
-- Generation Time: Nov 13, 2008 at 02:42 PM
-- Server version: 5.0.22
-- PHP Version: 5.2.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `civicrmtest`
--

-- --------------------------------------------------------

--
-- Table structure for table `civicrm_note`
--

CREATE TABLE IF NOT EXISTS `civicrm_note` (
  `id` int(10) unsigned NOT NULL auto_increment COMMENT 'Note ID',
  `entity_table` varchar(64) collate utf8_unicode_ci NOT NULL COMMENT 'Name of table where item being referenced is stored.',
  `entity_id` int(10) unsigned NOT NULL COMMENT 'Foreign key to the referenced item.',
  `note` text collate utf8_unicode_ci COMMENT 'Note and/or Comment.',
  `contact_id` int(10) unsigned default NULL,
  `modified_date` date default NULL COMMENT 'When was this note last modified/edited',
  `subject` varchar(255) collate utf8_unicode_ci default NULL COMMENT 'subject of note description',
  PRIMARY KEY  (`id`),
  KEY `index_entity` (`entity_table`,`entity_id`),
  KEY `FK_civicrm_note_contact_id` (`contact_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=17983 ;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `civicrm_note`
--
ALTER TABLE `civicrm_note`
  ADD CONSTRAINT `FK_civicrm_note_contact_id` FOREIGN KEY (`contact_id`) REFERENCES `civicrm_contact` (`id`);

Donald Lobo

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: Foriegn Key Restraint Error when deleting a record
November 13, 2008, 12:15:18 pm

can u do the following in your DB:

Code: [Select]
ALTER TABLE civicrm_note DROP FOREIGN KEY FK_civicrm_note_contact_id;
ALTER TABLE civicrm_note ADD CONSTRAINT FK_civicrm_note_contact_id FOREIGN KEY (contact_id) REFERENCES civicrm_contact (id) ON DELETE SET NULL;

thanx

lobo
A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

ryanpitt

  • I post occasionally
  • **
  • Posts: 42
  • Karma: 0
Re: Foriegn Key Restraint Error when deleting a record
November 13, 2008, 01:13:12 pm
Thanks!

We had to add the FOREIGN_KEY_CHECKS to get the DROP to WORK
SET FOREIGN_KEY_CHECKS=0; in the beginning and ...
SET FOREIGN_KEY_CHECKS=1; when done

Deleting records now works!
Ryan
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM (Moderator: Dave Greenberg) »
  • Foriegn Key Restraint Error when deleting a record

This forum was archived on 2017-11-26.