list of all table referencing by foreign key ps_product.id_product

[longy]

I need to change all id_product value in ps_product table, and adjust accordingly all fields that refer to it.

I need all tables and foreign keys that refer to ps_product.id_product.

Could someone list me every relation with this field?
 

 

 

[Tung at RockPOS.com]

Good questions, @longy!

You will find the answer by studying Prestashop database schema.

Here you go:

http://doc.prestashop.com/display/PS15/Fundamentals#Fundamentals-Databaseschema

 

There are 2 formats: PDF or MySQL Workbench.

 

Give it a try!

 

And that's great if you can share the script to get it done so we all learn together.

[longy]

Thanks for reply tungCEO, I'll explain all that I tried.

Initially I checked for constraints using following query:

 

select TABLE_NAME t, COLUMN_NAME c from information_schema.KEY_COLUMN_USAGE

where TABLE_SCHEMA = "dbname"

and REFERENCED_TABLE_NAME = "ps_product"

and REFERENCED_COLUMN_NAME = "id_product"
group by t, c

It returns 0 results. Because for some reason prestashop, at least in my case, did not define any constraints. 

Then I downloaded the er scheme, open on mysql workbench and I can see a lot of arrow representing relations. So I exported model to sql and import in a new empty database. This operation generate some errors but database is created and there are some constraints but I don't think that they are all.

 

Launching the previous query I get just 29 results and I can't see some  obvious foreign key and table name such as id_product_1 and id_product_2 for ps_accessory table.

I don't think that it's possible to get all information needed in less then a week reading manually all tables from database schema.

And from my last try I don't think that database schema is free from errors or omissions

[Tung at RockPOS.com]

That's true.

When working with MySQL, we don't usually use CONSTRANTS, but we control the contraints in the code.

 

Follow the rule of Prestashop schema, the name of a table which contains a foreign key agains prouduct table, has to include "product" keyword.

Here is the sql script to get the list of those tables:

SELECT
   table_name          
FROM
   INFORMATION_SCHEMA.TABLES         
WHERE
   table_schema in (
      select
         database()
   )          
   AND (
      table_name LIKE '%product%'            
   )          
   ;

My suggestion:

- Firstly, based on those tables lised above.

- Secondly and manually, review all other tables and see if they contains "id_product" or not.

There are around +/- 200 tables, so it's totally possible.

 

Hope this helps!

[longy]

I've also tried a similar approach. Selecting all tables and columns having column names like %product%.

In this way I'll get also table like order_details and accessory that doesn't present substring 'product' in their name, but contain columns with that substring.

 

I had momentarily set aside this approach in the hope of finding some specific resource to accomplish at this task. 

 

However thanks to your information, now I know it's almost the best approach.

[bellini13]

download the version of Prestashop you are using, and extract it.  Then open \prestashop\install\data\db_structure.sql

 

Then search for id_product in this file.  That will tell you every table that references

[Tung at RockPOS.com]

That's a really good alternative approach, @bellini13.

[ishko]

Hello. Here is the list of tables with an id_product column for Prestashop 1.6.0.5.

Thanks @Bellini13 for the idea.

 

 

PREFIX_product_attachment

PREFIX_attribute_impact

PREFIX_cart_product

PREFIX_category_product

PREFIX_compare_product

PREFIX_customer_thread

PREFIX_customization

PREFIX_customization_field

PREFIX_feature_product

PREFIX_product_group_reduction_cache

PREFIX_product_carrier

PREFIX_image

PREFIX_product

PREFIX_product_shop

PREFIX_product_attribute

PREFIX_product_download

PREFIX_product_lang

PREFIX_product_sale

PREFIX_product_tag

PREFIX_scene_products

PREFIX_search_index

PREFIX_specific_price

PREFIX_product_country_tax

PREFIX_specific_price_priority

PREFIX_stock

PREFIX_warehouse_product_location

PREFIX_stock_available

PREFIX_supply_order_detail

PREFIX_product_supplier

 

And 2 specials :

 

PREFIX_accessory

column : id_product_1

column : id_product_2

 

PREFIX_specific_price_rule

just specified as KEY (NON primary)

 

 

I had to delete remaining traces of products already erased in a wrong way (=>in my case the ones with IDs > 27)

The following SQL query in phpmyadmin worked nice :

 

DELETE FROM `ps_product_attachment` WHERE id_product>27;

DELETE FROM `ps_attribute_impact` WHERE id_product>27;

DELETE FROM `ps_cart_product` WHERE id_product>27;

DELETE FROM `ps_category_product` WHERE id_product>27;

DELETE FROM `ps_compare_product` WHERE id_product>27;

DELETE FROM `ps_customer_thread` WHERE id_product>27;

DELETE FROM `ps_customization` WHERE id_product>27;

DELETE FROM `ps_customization_field` WHERE id_product>27;

DELETE FROM `ps_feature_product` WHERE id_product>27;

DELETE FROM `ps_product_group_reduction_cache` WHERE id_product>27;

DELETE FROM `ps_product_carrier` WHERE id_product>27;

DELETE FROM `ps_image` WHERE id_product>27;

DELETE FROM `ps_product` WHERE id_product>27;

DELETE FROM `ps_product_shop` WHERE id_product>27;

DELETE FROM `ps_product_attribute` WHERE id_product>27;

DELETE FROM `ps_product_download` WHERE id_product>27;

DELETE FROM `ps_product_lang` WHERE id_product>27;

DELETE FROM `ps_product_sale` WHERE id_product>27;

DELETE FROM `ps_product_tag` WHERE id_product>27;

DELETE FROM `ps_scene_products` WHERE id_product>27;

DELETE FROM `ps_search_index` WHERE id_product>27;

DELETE FROM `ps_specific_price` WHERE id_product>27;

DELETE FROM `ps_product_country_tax` WHERE id_product>27;

DELETE FROM `ps_specific_price_priority` WHERE id_product>27;

DELETE FROM `ps_stock` WHERE id_product>27;

DELETE FROM `ps_warehouse_product_location` WHERE id_product>27;

DELETE FROM `ps_stock_available` WHERE id_product>27;

DELETE FROM `ps_product_supplier` WHERE id_product>27;

 

 

ps_supply_order_detail is not included in this query. Neither the 2 "specials" tables.

[Tung at RockPOS.com]

Good to know, thanks!