0

I exported the sales_flat_order table and uploaded it to phpmyadmin where I wanted to create a FK with customers table as sales_flat_order does have a customer_id column, although some values show as null but the less. The linking to FK failed with the typical error "cannot add or delete foreign key etc". So I wanted to see what the customer_id values looked like and on doing this:

select customer_id from sales_flat_order 

as it shows a lot of NULLS it fails in linking to customers table, but I need that link in order to be able to relate customers to their orders. Unless there is other way.

  • customer_id column is null if the order is placed by guest and not registered customer. That is why magento does not have foreign key on that column. Check cudtomer group id column. In default settings it should be set to not logged in group for guest orders. – Zefiryn Jun 4 '17 at 12:56
  • thank you, you are right! the thing is that the word NULL appears first and there were so many not registered that it blotted the rest out. I went after 500 and then started the id numbers. How can I solve the problem of linking to customers table? deleting all rows with customer_id = null? but then I would lose a lot of valuable data for reporting purposes – Arminius Jun 4 '17 at 12:59
0

Well, the answer is that you cant; not without modifications. The reason being that there are NULLS as customer_id in the sales table and that won't match with any primary key in the parent table.

The only solution is to delete all rows where customer_id is null and this should not matter much because magento wrongly does not include as customers those who purchased without registering. I have checked that their emails do not appear in the customers table. So as I move away from magento database I create my own database where that will not happen.

  • I don't know what kind of reporting you are preparing but customer_email will always be filled. You can left join on that column and fetch additional customer data for the registered ones. – Zefiryn Jun 4 '17 at 20:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.