CiviCRM Community Forums (archive)

a) I have some addresses which do not have a state/province

How can I add to the list of states/provinces (I want to add 'None')?

b) Users have reported this error :

Database Error Code: Cannot add or update a child row: a foreign key constraint fails, 1216

Error Details:
Array(    [callback] => Array        (           

• => CRM_Core_Error            [1] => handle        )     
  Code: [Select]
  => -3    [message] => DB Error: constraint violation    [mode] => 16    [debug_info] => INSERT INTO civicrm_address (location_id , street_address , city , state_province_id , postal_code , country_id ) VALUES ( 4131 , 'P.O. Box 1046' , 'Kingston 8' ,  01 , '8' ,  1108 )  [nativecode=1216 ** Cannot add or update a child row: a foreign key constraint fails]    [type] => DB_Error   [/li]



happens when I select city as 'kingston' and state as 'kingston'

the actual address does not have a state in it so its default behavior for users to select 'kingston' as the state

Array
(
    [callback] => Array
        (
           

• => CRM_Core_Error
[1] => handle
        )

   

=> -3
    [message] => DB Error: constraint violation
    [mode] => 16
    [debug_info] => UPDATE  civicrm_address  SET location_id = 92 , street_address = 'P.O. Box 1046' , city = 'kingston' , state_province_id = 01 , postal_code = '8' , country_id = 1108   WHERE (  civicrm_address.id = 91 )   [nativecode=1216 ** Cannot add or update a child row: a foreign key constraint fails]
    [type] => DB_Error
    [user_info] => UPDATE  civicrm_address  SET location_id = 92 , street_address = 'P.O. Box 1046' , city = 'kingston' , state_province_id = 01 , postal_code = '8' , country_id = 1108   WHERE (  civicrm_address.id = 91 )   [nativecode=1216 ** Cannot add or update a child row: a foreign key constraint fails]
    [to_string] => [db_error: message="DB Error: constraint violation" code=-3 mode=callback callback=CRM_Core_Error::handle prefix="" info="UPDATE  civicrm_address  SET location_id = 92 , street_address = 'P.O. Box 1046' , city = 'kingston' , state_province_id = 01 , postal_code = '8' , country_id = 1108   WHERE (  civicrm_address.id = 91 )   [nativecode=1216 ** Cannot add or update a child row: a foreign key constraint fails]"]
)

can I remove the state option completely? if so, where?

Hi,

It looks like any state with a digit in its abbreviation will result in a foreign key error. I replaced them all with dummy values and I have had no problems since.