DB Migration with Foreign Keys

From my google searching I get the feeling this has been asked a number of times, but for one reason or another the conversation seems to be taken to other forms of communication and not in the public domain... so i'm still at a loss...

I've got a app running with plenty of data in it, and I want to use the devtools migration to assist with moving it up and helping with the autodeployment. As this is the first migration i have an empty migrations folder. I generate the migrations with no issue and also add the flag for data too.

When i try and run the migration it errors out on ERROR: SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint which when i check the verbose log makes perfect sense. The migrations tool has decided to run the table migration files in a seemingly random order, and as such gets to the first one with a FK to a table that has yet to be created and errors out.

Now, as far as I can tell, there is a way to do it that involves logging into the mysql engine and manually disabling FK checks, then running the migrations and then enabling the FK checks again.

However, is there a more elegant way of doing this? Is there a flag i'm missing? I'd prefer not to write additional commands into my pipeline to disable/enable FK from within mysql if i can help it, as if i want to move to a new db engine, then i'm going to have to change the commands in multiple places.

Open to all and any suggestions.

Many thanks in advance.

edited Feb '18

You can control order by --table option

migration run --table firstTable,secondTable

edited Feb '18

You can control order by --table option

migration run --table firstTable,secondTable

Thanks @olegatro. Whilst this would be a fairly easy solution with minimal tables, I have a lot of table.

In addition, as the idea is to make this an autodeployment script, having all the tables listed will be a pain when i add new tables.

Thanks again though. Do you have any other suggestions?

unfortunately not, I had the same problem, and the only thing that I found it to use --table option. I understand that you can have many tables, perhaps you will be able to use what I wrote.

You can control order by --table option

migration run --table firstTable,secondTable

Thanks @olegatro. Whilst this would be a fairly easy solution with minimal tables, I have a lot of table.

In addition, as the idea is to make this an autodeployment script, having all the tables listed will be a pain when i add new tables.

Thanks again though. Do you have any other suggestions?

After reviewing the various options, I decieded to build a command into the deployment script that would disable and enable the global key check of the db. This in turn means that the order of the table migration is irrelevant.

Thanks again @olegatro for your suggestion regards the --table option.