Foreign constraint violation on APIv3 contribution create if financial_type_id is numeric
Overview
We have a lot of numeric financial types. On the new ESR (and also the dmaster site), attempts to create contributions via APIv3 are failing.
Reproduction steps
- Go to /civicrm/api3/#explorer
- Select "FinancialType" for entity and "create" for action.
- Fill in a numeric value (e.g. 1234) for Financial Type Value and click Execute.
- Reload the page.
- Select "Contribution" for entity and "create" for action.
- Select your newly created Financial Type ID from the list and fill in anything for other values.
- Click Execute.
Current behaviour
The financial_type_id parameter accepts the financial type name (e.g. "Event Fee"). However, if the provided value is a number, it appears that the resulting SQL query attempts to use the number directly instead of looking up the associated key.
On the dmaster website the following query (via APIv3 explorer):
$result = civicrm_api3('Contribution', 'create', [
'financial_type_id' => 1234,
'receive_date' => "2022-02-08",
'total_amount' => 50,
'contact_id' => "user_contact_id",
]);
produces the following result:
{
"code": -3,
"error_message": "DB Error: constraint violation",
"mode": 16,
"debug_info": "INSERT INTO `civicrm_contribution` (`contact_id` , `financial_type_id` , `payment_instrument_id` , `receive_date` , `total_amount` , `fee_amount` , `net_amount` , `currency` , `contribution_status_id` , `tax_amount` ) VALUES ( 204 , 1234 , 4 , 20220208000000 , 50 , 0 , 50 , 'USD' , 1 , 0 ) [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`dmastercivi_g5lis`.`civicrm_contribution`, CONSTRAINT `FK_civicrm_contribution_financial_type_id` FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type` (`id`))]",
"type": "DB_Error",
"user_info": "INSERT INTO `civicrm_contribution` (`contact_id` , `financial_type_id` , `payment_instrument_id` , `receive_date` , `total_amount` , `fee_amount` , `net_amount` , `currency` , `contribution_status_id` , `tax_amount` ) VALUES ( 204 , 1234 , 4 , 20220208000000 , 50 , 0 , 50 , 'USD' , 1 , 0 ) [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`dmastercivi_g5lis`.`civicrm_contribution`, CONSTRAINT `FK_civicrm_contribution_financial_type_id` FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type` (`id`))]",
"to_string": "[db_error: message=\"DB Error: constraint violation\" code=-3 mode=callback callback=CRM_Utils_REST::fatal prefix=\"\" info=\"INSERT INTO `civicrm_contribution` (`contact_id` , `financial_type_id` , `payment_instrument_id` , `receive_date` , `total_amount` , `fee_amount` , `net_amount` , `currency` , `contribution_status_id` , `tax_amount` ) VALUES ( 204 , 1234 , 4 , 20220208000000 , 50 , 0 , 50 , 'USD' , 1 , 0 ) [nativecode=1452 ** Cannot add or update a child row: a foreign key constraint fails (`dmastercivi_g5lis`.`civicrm_contribution`, CONSTRAINT `FK_civicrm_contribution_financial_type_id` FOREIGN KEY (`financial_type_id`) REFERENCES `civicrm_financial_type` (`id`))]\"]",
"is_error": 1
}
Using a text value produces a similar example query:
$result = civicrm_api3('Contribution', 'create', [
'financial_type_id' => "Event Fee",
'receive_date' => "2022-02-08",
'total_amount' => 50,
'contact_id' => "user_contact_id",
]);
and the result is successful:
{
"is_error": 0,
"version": 3,
"count": 1,
"id": 116,
"values": {
"116": {
"id": "116",
"contact_id": "204",
"financial_type_id": "4",
"contribution_page_id": "",
"payment_instrument_id": "4",
"receive_date": "20220208000000",
"non_deductible_amount": "",
"total_amount": "50",
"fee_amount": "0",
"net_amount": "50",
"trxn_id": "",
"invoice_id": "",
"invoice_number": "",
"currency": "USD",
"cancel_date": "",
"cancel_reason": "",
"receipt_date": "",
"thankyou_date": "",
"source": "",
"amount_level": "",
"contribution_recur_id": "",
"is_test": "",
"is_pay_later": "",
"contribution_status_id": "1",
"address_id": "",
"check_number": "",
"campaign_id": "",
"creditnote_id": "",
"tax_amount": "0",
"revenue_recognition_date": "",
"is_template": "",
"contribution_type_id": "4"
}
}
}
Environment information
- CiviCRM: _Master/5.45.3