CiviCRM Community Forums (archive)

We do an annual billing to member clubs.  Each club has different numbers of members and different issues so each amount is unique. I created a file and imported it into civicrm_pledge. I believe i  did the import last year without issue. This time I am being hit with Foreign key issues.

It looks like existing records have NULL for both contribution_page_id and honor_contact_id. I forced a 1 into contribution_page_id and then hit the same problem on honor_contact_id.  The Foreign keys seem to allow null but it is not accepting a blank

I am on civi 4.4.5 and Drupal 7

ERROR 1452: 1452: Cannot add or update a child row: a foreign key constraint fails (`fjmcprod_crm`.`civicrm_pledge`, CONSTRAINT `FK_civicrm_pledge_honor_contact_id` FOREIGN KEY (`honor_contact_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE SET NULL)
SQL Statement:
INSERT INTO `fjmcprod_crm`.`civicrm_pledge` (`id`, `contact_id`, `financial_type_id`, `contribution_page_id`, `amount`, `original_installment_amount`, `currency`, `frequency_unit`, `frequency_interval`, `frequency_day`, `installments`, `start_date`, `create_date`, `acknowledge_date`, `modified_date`, `cancel_date`, `end_date`, `honor_contact_id`, `honor_type_id`, `max_reminders`, `initial_reminder_day`, `additional_reminder_day`, `status_id`, `is_test`, `campaign_id`) VALUES ('1945', '32', '5', '1', '550.00', '550.00', 'USD', 'year', '1', '1', '1', '14/09/01', '14/09/01', '', '', '', '', '', '', '1', '5', '5', '2', '0', '')

The API importer may be able to help you get around this problem (no guarantees but the mechanism is a little different although it looks pretty similar so it may not be affected)

https://civicrm.org/extensions/api-csv-import-gui

I was able to resolve my problem. The workbench import function was adding quotes around all constants. Importing 'NULL' is not the same as NULL.  My workaround was to create a full SQL  statement on each line and listing the data in the value clause. Exporting form the Excel file so the statement exports as a csv file got the job done. I wish I remembered about Eileen's API importer, would of made this job much easier.