MAgento 2.3.5-p1 upgrade error on setup:upgrade --> Schema creation

[donweb]

Hi,

I just upgraded my magento 2.3.2 to 2.3.5-p1 using CLI update method. 

However my upgrade fails when I need to do a setup:upgrade.

This error is thrown and the upgrade stops

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`m2_caddy_new4`.`#sql-7d3_8bda0`, CONSTRAINT `SALES_ORDER_CUSTOMER_ID_CUSTOMER_ENTITY_ENTITY_ID` FOREIGN KEY (`customer_id`) REFERENCES `customer_entity` (`entity_id`) ON DELETE SET NULL), query was: ALTER TABLE `sales_order` MODIFY COLUMN `entity_id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT "Entity ID", MODIFY COLUMN `store_id` smallint(5) UNSIGNED NULL COMMENT "Store ID", MODIFY COLUMN `customer_id` int(10) UNSIGNED NULL COMMENT "Customer ID", MODIFY COLUMN `billing_address_id` int(11) NULL COMMENT "Billing Address ID", MODIFY COLUMN `quote_address_id` int(11) NULL COMMENT "Quote Address ID", MODIFY COLUMN `quote_id` int(11) NULL COMMENT "Quote ID", MODIFY COLUMN `shipping_address_id` int(11) NULL COMMENT "Shipping Address ID", MODIFY COLUMN `increment_id` varchar(32) NULL COMMENT "Increment ID", MODIFY COLUMN `ext_customer_id` varchar(32) NULL COMMENT "Ext Customer ID", MODIFY COLUMN `ext_order_id` varchar(32) NULL COMMENT "Ext Order ID", MODIFY COLUMN `original_increment_id` varchar(32) NULL COMMENT "Original Increment ID", MODIFY COLUMN `relation_child_id` varchar(32) NULL COMMENT "Relation Child ID", MODIFY COLUMN `relation_child_real_id` varchar(32) NULL COMMENT "Relation Child Real ID", MODIFY COLUMN `relation_parent_id` varchar(32) NULL COMMENT "Relation Parent ID", MODIFY COLUMN `relation_parent_real_id` varchar(32) NULL COMMENT "Relation Parent Real ID", MODIFY COLUMN `store_name` varchar(255) NULL COMMENT "Store Name", MODIFY COLUMN `gift_message_id` int(11) NULL COMMENT "Gift Message ID", ADD CONSTRAINT `SALES_ORDER_CUSTOMER_ID_CUSTOMER_ENTITY_ENTITY_ID` FOREIGN KEY (`customer_id`) REFERENCES `customer_entity` (`entity_id`) ON DELETE SET NULL, ADD CONSTRAINT `SALES_ORDER_STORE_ID_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `store` (`store_id`) ON DELETE SET NULL

Any idea on how I can fix this?

[donweb]

By running

grep -r -l "SALES_ORDER_CUSTOMER_ID_CUSTOMER_ENTITY_ENTITY_ID"

I was able to narrow down the file (db_schema.xml ) file responsible in the db update script when running

"bin/magento setup:upgrade"

As I was not able to find any relevant information and cross checking a similar error comment I found in researching the issue, I commented the following code in var/magento/module_sales/etc/db_schema.xml

comment out constraints from 253 to 268

<!--
        <constraint xsi:type="foreign" referenceId="SALES_ORDER_CUSTOMER_ID_CUSTOMER_ENTITY_ENTITY_ID" table="sales_order"
                    column="customer_id" referenceTable="customer_entity" referenceColumn="entity_id"
                    onDelete="SET NULL"/>
        <constraint xsi:type="foreign" referenceId="SALES_ORDER_STORE_ID_STORE_STORE_ID" table="sales_order" column="store_id"
                    referenceTable="store" referenceColumn="store_id" onDelete="SET NULL"/>
-->

Then I restarted server/ httpd

Finally

php bin/magento setup:upgrade

No more error. However I believe this is a workaround fix until I am able to get better guidance from magento team.

[GalantnostS]

I found that this can happen if I have deleted some customers before, and their customer ids remain in the customer_id column of their orders. Setting those to null and the error went away.