CiviCRM Community Forums (archive)

Just tried to upgrade and got this error

Database Error Code: Cannot add or update a child row: a foreign key constraint fails (`CiviCRM`.`civicrm_uf_field`, CONSTRAINT `FK_civicrm_uf_field_location_type_id` FOREIGN KEY (`location_type_id`) REFERENCES `civicrm_location_type` (`id`) ON DELETE SET NULL), 1452
Additional Details:

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

    [code] => -3
    [message] => DB Error: constraint violation
    [mode] => 16
    [debug_info] => INSERT INTO civicrm_uf_field
   ( uf_group_id, field_name, is_required, is_reserved, weight, visibility, in_selector, is_searchable, location_type_id, label, field_type, help_post, phone_type_id )

VALUES
   ( @uf_group_id_onBehalfOrganization,   'organization_name',  1, 0, 1, 'User and User Admin Only',  0, 0, NULL,
            'Organization Name', 'Organization', NULL,  NULL ),
   ( @uf_group_id_onBehalfOrganization,   'phone',              1, 0, 2, 'User and User Admin Only',  0, 0, 3,
            'Phone (Main) ',     'Contact',      NULL,  1 ),
   ( @uf_group_id_onBehalfOrganization,   'email',              1, 0, 3, 'User and User Admin Only',  0, 0, 3, 
            'Email (Main) ',     'Contact',      NULL,  NULL ),
   ( @uf_group_id_onBehalfOrganization,   'street_address',     1, 0, 4, 'User and User Admin Only',  0, 0, 3, 
            'Street Address',    'Contact',      NULL,  NULL ),
   ( @uf_group_id_onBehalfOrganization,   'city',               1, 0, 5, 'User and User Admin Only',  0, 0, 3, 
            'City',              'Contact',      NULL,  NULL ),
   ( @uf_group_id_onBehalfOrganization,   'postal_code',        1, 0, 6, 'User and User Admin Only',  0, 0, 3,
            'Postal Code',       'Contact',      NULL,  NULL ),
   ( @uf_group_id_onBehalfOrganization,   'country',            1, 0, 7, 'User and User Admin Only',  0, 0, 3,
            'Country',           'Contact',      NULL,  NULL ),
   ( @uf_group_id_onBehalfOrganization,   'state_province',     1, 0, 8, 'User and User Admin Only',  0, 0, 3,   
            'State / Province',  'Contact',      NULL,  NULL ) [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`CiviCRM`.`civicrm_uf_field`, CONSTRAINT `FK_civicrm_uf_field_location_type_id` FOREIGN KEY (`location_type_id`) REFERENCES `civicrm_location_type` (`id`) ON DELETE SET NULL)]
    [type] => DB_Error
    [user_info] => INSERT INTO civicrm_uf_field
   ( uf_group_id, field_name, is_required, is_reserved, weight, visibility, in_selector, is_searchable, location_type_id, label, field_type, help_post, phone_type_id )

VALUES
   ( @uf_group_id_onBehalfOrganization,   'organization_name',  1, 0, 1, 'User and User Admin Only',  0, 0, NULL,
            'Organization Name', 'Organization', NULL,  NULL ),
   ( @uf_group_id_onBehalfOrganization,   'phone',              1, 0, 2, 'User and User Admin Only',  0, 0, 3,
            'Phone (Main) ',     'Contact',      NULL,  1 ),
   ( @uf_group_id_onBehalfOrganization,   'email',              1, 0, 3, 'User and User Admin Only',  0, 0, 3, 
            'Email (Main) ',     'Contact',      NULL,  NULL ),
   ( @uf_group_id_onBehalfOrganization,   'street_address',     1, 0, 4, 'User and User Admin Only',  0, 0, 3, 
            'Street Address',    'Contact',      NULL,  NULL ),
   ( @uf_group_id_onBehalfOrganization,   'city',               1, 0, 5, 'User and User Admin Only',  0, 0, 3, 
            'City',              'Contact',      NULL,  NULL ),
   ( @uf_group_id_onBehalfOrganization,   'postal_code',        1, 0, 6, 'User and User Admin Only',  0, 0, 3,
            'Postal Code',       'Contact',      NULL,  NULL ),
   ( @uf_group_id_onBehalfOrganization,   'country',            1, 0, 7, 'User and User Admin Only',  0, 0, 3,
            'Country',           'Contact',      NULL,  NULL ),
   ( @uf_group_id_onBehalfOrganization,   'state_province',     1, 0, 8, 'User and User Admin Only',  0, 0, 3,   
            'State / Province',  'Contact',      NULL,  NULL ) [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`CiviCRM`.`civicrm_uf_field`, CONSTRAINT `FK_civicrm_uf_field_location_type_id` FOREIGN KEY (`location_type_id`) REFERENCES `civicrm_location_type` (`id`) ON DELETE SET NULL)]
    [to_string] => [db_error: message="DB Error: constraint violation" code=-3 mode=callback callback=CRM_Core_Error::handle prefix="" info="INSERT INTO civicrm_uf_field
   ( uf_group_id, field_name, is_required, is_reserved, weight, visibility, in_selector, is_searchable, location_type_id, label, field_type, help_post, phone_type_id )

VALUES
   ( @uf_group_id_onBehalfOrganization,   'organization_name',  1, 0, 1, 'User and User Admin Only',  0, 0, NULL,
            'Organization Name', 'Organization', NULL,  NULL ),
   ( @uf_group_id_onBehalfOrganization,   'phone',              1, 0, 2, 'User and User Admin Only',  0, 0, 3,
            'Phone (Main) ',     'Contact',      NULL,  1 ),
   ( @uf_group_id_onBehalfOrganization,   'email',              1, 0, 3, 'User and User Admin Only',  0, 0, 3, 
            'Email (Main) ',     'Contact',      NULL,  NULL ),
   ( @uf_group_id_onBehalfOrganization,   'street_address',     1, 0, 4, 'User and User Admin Only',  0, 0, 3, 
            'Street Address',    'Contact',      NULL,  NULL ),
   ( @uf_group_id_onBehalfOrganization,   'city',               1, 0, 5, 'User and User Admin Only',  0, 0, 3, 
            'City',              'Contact',      NULL,  NULL ),
   ( @uf_group_id_onBehalfOrganization,   'postal_code',        1, 0, 6, 'User and User Admin Only',  0, 0, 3,
            'Postal Code',       'Contact',      NULL,  NULL ),
   ( @uf_group_id_onBehalfOrganization,   'country',            1, 0, 7, 'User and User Admin Only',  0, 0, 3,
            'Country',           'Contact',      NULL,  NULL ),
   ( @uf_group_id_onBehalfOrganization,   'state_province',     1, 0, 8, 'User and User Admin Only',  0, 0, 3,   
            'State / Province',  'Contact',      NULL,  NULL ) [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`CiviCRM`.`civicrm_uf_field`, CONSTRAINT `FK_civicrm_uf_field_location_type_id` FOREIGN KEY (`location_type_id`) REFERENCES `civicrm_location_type` (`id`) ON DELETE SET NULL)]"]
)

Hellllllp! please

chelcone

I think during upgrade we might want to handle this case when location type is deleted. May be make "Main" as reserved location type.

Kurund

after saying that it does seem that Main is gone from my locaion type list.

I tried that and it didnt seem to work.

I ran into this error when upgrading form 3.4 to 4 as well.  To fix it we temporary turned off the autoincrement setting on the id column of the civicrm_location_type table and manually entered the missing locations to match the id of the original table defaults.

(1, 'Home', 'HOME', 'Place of residence', 0, 1, 1),
(2, 'Work', 'WORK', 'Work location', 0, 1, NULL),
(3, 'Main', NULL, 'Main office location', 0, 1, NULL),
(4, 'Other', NULL, 'Other location', 0, 1, NULL),
(5, 'Billing', NULL, 'Billing Address location', 1, 1, NULL);

On our case Main and Other were missing.  Once those were restored, the update ran without error.

Thanks to each of you for identifying the problem and providing enough information for the fix -- even for a beginner like me.