Vote up!
Vote down!

How to recover from PDOException: SQLSTATE[23000]: Integrity constraint violation:

My fault! I don't think there is a Drupal error. I was trying to implement a new rule based onhttp://www.drupalcommerce.org/user-guide/shopping-carts-orders-and-line-... where I followed these instructions:
"To remove an item from the cart using rules, you must unset the price of the line item during "Calculating the sell price of a product." This is similar to the rule "Unset the price of disabled products in the cart" that is included with core. So you can refer to that as an example. Deleting a line item just involves setting the price to NULL (i.e. no value, not 0)."
I cloned that rule, but could not get it to work. During my various attempts, my cloned rule dissappeared (I don't believe I deleted it, but could have). Now, when ever I "add to cart" I get this error and can't get around it. The original rule looks intact, but I still clicked on revert just in case I screwed that up. That did not change anything. I have worked on this site (locally) for many days and is almost done. I am a novice at Drupal and don't have any SQL skills. Here is the complete error message:

PDOException: SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'commerce_unit_price_amount' cannot be null: INSERT INTO {field_data_commerce_unit_price} (entity_type, entity_id, revision_id, bundle, delta, language, commerce_unit_price_amount, commerce_unit_price_currency_code, commerce_unit_price_data) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6, :db_insert_placeholder_7, :db_insert_placeholder_8); Array ( [:db_insert_placeholder_0] => commerce_line_item [:db_insert_placeholder_1] => 68 [:db_insert_placeholder_2] => 68 [:db_insert_placeholder_3] => product [:db_insert_placeholder_4] => 0 [:db_insert_placeholder_5] => und [:db_insert_placeholder_6] => [:db_insert_placeholder_7] => USD [:db_insert_placeholder_8] => a:1:{s:10:"components";a:1:{i:0;a:3:{s:4:"name";s:10:"base_price";s:5:"price";a:3:{s:6:"amount";s:3:"500";s:13:"currency_code";s:3:"USD";s:4:"data";a:1:{s:10:"components";a:0:{}}}s:8:"included";b:1;}}} ) in field_sql_storage_field_storage_write() (line 448 of C:\Users\Rich\Documents\My Web Sites\Drupal Commerce Kickstart\modules\field\modules\field_sql_storage\field_sql_storage.module).

Asked by: Rich Edwards
on October 19, 2012


OK.. I am rebuilding from scratch. Live and learn. I now incrementally back the DB and files after significant updates. I am using a GUI for the SQL DB and learning about that. I did manage to successfully implement the last change that killed my site. Probably good that this happened with only a relatively few products defined. I now have more confidence in my sites stability. This is a great platform, and I appreciate it.

- Rich Edwards on October 20, 2012

2 Answers

Vote up!
Vote down!


Glad you worked it out.


Josh Miller
Answer by: Josh Miller
Posted: Jan 11, 2013
Vote up!
Vote down!

I am experiencing the same error when trying to delete a line item by setting the price to nothing.

I am using commerce kickstart. Is the only solution to rebuild my entire site? Or are there other options?

Answer by: obs
Posted: Jun 18, 2013


I am experiencing this as well. I am using "Inline entity form - Multiple values" as the widget for my line item. I am doing this through the admin screen at admin/commerce/orders/%/edit.

I try to add a line item, and the price field is disabled. So even if I wanted to override or provide the price for that item, I couldn't. Maybe I will take this up in the issue queue with Inline Entity Form.

- mvanmeter1 on July 26, 2013