Search code examples
mysqlpostgresqlyii2truncate

Truncating tables in any order in a loop using Yii2 PHP. How avoid FK errors?

I have about 50-60 tables which are somehow connected and have foreign keys. I have found in what order I should truncate them to escape any errors with FK and PK keys.

All commands to truncate I want to call in PHP file using Yii2 framework.

It would be all fine, but new links and tables can appear in this schema, and it would be needed to explore all this link and relation rules again and order tables to avoid errors.

And I dont want to explore 50+ tables after adding or changing any table

I have some idea but dont know is it possible or not. Idea:

  1. make an array of table names
  2. go through this array and try to truncate current table
  3. repeat untill all tables are truncated

Please note that I wonder how to do it for BOTH DBs: MySQL and PostgreSQL cause in production any of them can be used...

Hope I could explain my problem so that it looks quite clear for you

I tried to search it in Internet but without success

Solution

  • For PostgreSQL, the solution is to truncate all tables in a single statement:

    TRUNCATE tab1, tab2, tab3, ...;