Author Topic: Constraint violation/Foreign Key error on custom data (Read 3548 times)

websynapse · July 25, 2011, 07:03:04 pm

I'm getting this error on any custom data fields, whenever I go to save the data in a contact's record.

Sorry. A non-recoverable error has occurred.

DB Error: constraint violation

Database Error Code: Cannot add or update a child row: a foreign key constraint fails (`civicrm/civicrm_value_volunteer_5`, CONSTRAINT `FK_civicrm_value_volunteer_5_entity_id` FOREIGN KEY (`entity_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE), 1452

Return to home page.

Code: [Select]

Error Details:

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

    [code] => -3
    [message] => DB Error: constraint violation
    [mode] => 16
    [debug_info] => INSERT INTO civicrm_value_volunteer_5  ( volunteer_notes_9,entity_id ) VALUES ( 'bssd',772 ) ON DUPLICATE KEY UPDATE volunteer_notes_9 = 'bssd' [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civicrm/civicrm_value_volunteer_5`, CONSTRAINT `FK_civicrm_value_volunteer_5_entity_id` FOREIGN KEY (`entity_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]
    [type] => DB_Error
    [user_info] => INSERT INTO civicrm_value_volunteer_5  ( volunteer_notes_9,entity_id ) VALUES ( 'bssd',772 ) ON DUPLICATE KEY UPDATE volunteer_notes_9 = 'bssd' [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civicrm/civicrm_value_volunteer_5`, CONSTRAINT `FK_civicrm_value_volunteer_5_entity_id` FOREIGN KEY (`entity_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]
    [to_string] => [db_error: message="DB Error: constraint violation" code=-3 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_value_volunteer_5  ( volunteer_notes_9,entity_id ) VALUES ( 'bssd',772 ) ON DUPLICATE KEY UPDATE volunteer_notes_9 = 'bssd' [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`civicrm/civicrm_value_volunteer_5`, CONSTRAINT `FK_civicrm_value_volunteer_5_entity_id` FOREIGN KEY (`entity_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)]"]
)

I have read many threads on this and not found a solution. I have logged out and logged in again, and cleared the drupal sessions table.

This is on a fresh install of 3.1.4. I cannot replicate on the demo site.

thanks for any help - our database is quite useless until this gets sorted.

websynapse · July 25, 2011, 07:18:35 pm

I've noticed that I'm unable to Browse the contents of the civicrm_value_volunteer_5 table in phpmyadmin - would this be the problem? See attachment.

Hershel · July 26, 2011, 06:02:24 am

You can't browse because there's no data there to browse--that's fine.

Do you have a contact with id 772 ?

davesage · July 26, 2011, 11:37:02 am

Hi,

When you created the custom data you didn't by chance change the entity the set was used for before adding fields. I came across this FK violation issue with that?

http://forum.civicrm.org/index.php/topic,20705.msg86642.html#msg86642

This would explain the issue if it trying to link to contact with id 722 (which does exist) but the FK is pointing to another table which doesn;t have that number of records.

You could check the tables FK constraints in phpmyadmin and ensure that it is pointing to the correct table.

Sorry (and ignore me) if it unrelated but thought I'd check.

Cheers,

Dave

websynapse · July 26, 2011, 02:38:09 pm

Thanks for replies. Contact id 772 is the one I am trying to save the data against.

I don't know how to change the entity the set was using and didn't do that. I created a new custom data field via the interface, went to save data in my own record (772) and got this error.

How do I check the table's Foreign Key constraints? Is this something I'm going to have to do every time I create custom data through the interface? I don't understand why I would need to do that.

This is a very urgent problem for us.

davesage · July 26, 2011, 02:51:51 pm

Sounds like it is unrelated then.

I use mysql workbench. You can open your database and look at and change the FK constraints, I think you can also do it in phpmyadmin (go to the table and then click on 'structure' tab and then 'relation view' this then shows you what FK there are and here the entity_id field should have a FK pointing at your contact table. If it points to another table then the custom data was setup against another entity initially.

I've only experienced the problem when I changed the entity as in my post, all other custom data sets have worked fine without any FK violations for me, so it certainly shouldn't be something needed to be done every time. As you say you shouldn't need to do it, so this instance is either related to the bug I found or another bug that I've not experienced yet.

Can you also confirm what the custom data set is used for when you edit the settings?

Hope that helps.

Dave

websynapse · July 26, 2011, 03:11:01 pm

Thanks Dave. I've posted the structure/relation view for this table - what is it supposed to look like? I'm comparing with another civicrm database and trying different options - no luck yet.

I'm using the custom data to record how people are volunteering for our campaign, so it will be Notes and Select field types.

Dave Greenberg · July 26, 2011, 05:37:54 pm

The structure relationship looks correct. Foreign key points to the id column in civicrm_contact table as it should. If indeed a row exists in civicrm_contact with id = 772, then I have no idea why you would be getting that error. You could try running that query in phpmyadmin to see if you get an clues:

Code: [Select]

INSERT INTO civicrm_value_volunteer_5  ( volunteer_notes_9,entity_id ) VALUES ( 'bssd',772 ) ON DUPLICATE KEY UPDATE volunteer_notes_9 = 'bssd';

Also, not clear on the history of this. Is this a newly added custom data set? If so, have you tried deleting and recreating it (obviously not a good idea of you've already collected lots of data). If it's NOT new - what may have changed between when it was working and now?

websynapse · July 26, 2011, 06:15:42 pm

This is a newly added custom data set, via the interface. I did nothing other than add it as normal via the interface and go to my record to add some data and save it.

I have also tried deleting the custom data and recreating it. I get the same error each time I go to save any data.

The results of that code were:

Code: [Select]

#1452 - Cannot add or update a child row: a foreign key constraint fails (`civicrm/civicrm_value_volunteer_5`, CONSTRAINT `civicrm_value_volunteer_5_ibfk_1` FOREIGN KEY (`entity_id`) REFERENCES `civicrm_contact` (`id`) ON DELETE CASCADE)

Dave Greenberg · July 26, 2011, 09:42:09 pm

No idea what's going on :-(. Maybe try jumping on IRC and seeing if other folks have some clue (I assume other existing custom data sets are working ok when u delete this one AND that you've tried editing and saving different contacts with this data set enabled - and get the same error).

websynapse · July 26, 2011, 10:02:30 pm

No, there aren't any other existing data sets, as I said this is a fresh install. Any custom data set I create has this error.

I have tried on several contacts.

I'm on IRC but no one is replying.

erogers · October 01, 2011, 06:37:19 am

Bump. Did you ever resolve this? I have the same problem after upgrading from 3.3. to 3.4

xavier · October 01, 2011, 06:50:28 am

Hi,

Don't know if this is the same issue, but I had a custom data for activities or participants (not 100% sure anymore) and the foreign key was (wrongly) set up on the contact table. ie. to be able to create a custom data for activity 42 you had to have a contact with the id 42 (and if you deleted the contact 42 mayhem to the custom data of the activity would ensure probably).

Anyway, I removed manually the foreign key and added it to the right table and it worked fine.

Was not on the latest version of civicrm and was an old db (that has been upgraded several time). Was the first time I've had this issue.

X+

CiviCRM Community Forums (archive)

News:

Author Topic: Constraint violation/Foreign Key error on custom data (Read 3548 times)

websynapse

Constraint violation/Foreign Key error on custom data

websynapse

Re: Constraint violation/Foreign Key error on custom data

Hershel

Re: Constraint violation/Foreign Key error on custom data

davesage

Re: Constraint violation/Foreign Key error on custom data

websynapse

Re: Constraint violation/Foreign Key error on custom data

davesage

Re: Constraint violation/Foreign Key error on custom data

websynapse

Re: Constraint violation/Foreign Key error on custom data

Dave Greenberg

Re: Constraint violation/Foreign Key error on custom data

websynapse

Re: Constraint violation/Foreign Key error on custom data

Dave Greenberg

Re: Constraint violation/Foreign Key error on custom data

websynapse

Re: Constraint violation/Foreign Key error on custom data

erogers

Re: Constraint violation/Foreign Key error on custom data

xavier

Re: Constraint violation/Foreign Key error on custom data