In weird state with foreign key after migrations bug

Question

I'm working on my first project using laravel 5. All my migration scripts create tables except for one that adds a foreign key constraint afterward (so both tables are in place before it runs). I found a bug in one of my migration scripts (missing parens made field non-nullable instead of nullable), so after fixing that, I tried doing php artisan migrate:refresh

I hit several errors, so I (mistakenly) decided to get to a fresh state by dropping the tables (using PostgreSQL Studio, via Heroku), but now I'm stuck in a weird state:

When I try php artisan migrate or php artisan migrate --force, it says "Nothing to migrate".
but when I try to rollback or reset or refresh, I get an error: Undefined table: relation 'users' does not exist (SQL:alter table "users" drop constraint person_id)

That error is from that one migration script that adds the foreign key- I got the overall syntax from here- (http://laravel.com/docs/5.0/schema#foreign-keys)- scream out if wrong:

class AddConstraintToUsers extends Migration
{
    public function up()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->foreign('person_id')
                ->references('id')
                ->on('people');
        });
    }

    public function down()
    {
        Schema::table('users', function (Blueprint $table) {
            $table->dropForeign('person_id');
        });
    }

}

More info: based on comment below I deleted the rows from the migrations table so they won't be listed as having already run (in hopes of helping the "Nothing to migrate"). After doing that and trying php artisan migrate I get the same 'relation "users" does not exist" error mentioned above, and now the migrations table shows a 'batch' value of 1 for create_users_table, and 2 for all the others.

Even crazier: since artisan runs migrations in order and thought it had nothing to run (but had been blocked from creating the users table), I created a new migration script to execute last, where the contents were creating that users table. At least it stopped telling me 'Nothing to migrate', BUT it gives a 'duplicate table' error for the OTHER table (called 'updates'), not the one I've actually tried to create twice :p

How can I get back to a normal place-(where either artisan finds my migration scripts to run fresh, or stops trying to rollback the change for the table that's no longer there)- I promise I've learned my lesson to not drop tables. Unfortunately it looks like this problem is less-google-able than usual (or I'm not using the right terms)- any help is greatly appreciated!!

There should be a table called migrations, in the worst case you can manually delete migrations there until you are at the point where you want to be (or the state in which your tables are now) — Francesco de Guytenaere
Peyman- yes- (and I'll add rollback to the second bullet above)- unfortunately it gives me that 'table does not exist' error. Ciccio- good to know! It does sound like 'worst case' so I'm going to keep hunting a little longer before deleting rows from that table — Diane Kaplan
Ciccio: I tried your suggestion now- thanks for your help. Unfortunately it didn't artisan into thinking the migrations weren't run; now the 'batch' value is 2 for all migrations except for the first one (to create users table)- does that give us any new info? Updated in notes above... — Diane Kaplan
Did you (if there is no sensitive data obv.) drop all tables prior to deleting all the migration rows? For a real clean "(re)start". Each time you run the migrate, the batch n.o. will increase, so it seems like you executed migrate twice. If you did start clean, then there might be an error in the last migration. — Francesco de Guytenaere

Diane Kaplan Diane Kaplan · Accepted Answer · 2015-09-12T11:18:39

In the end, the way out was to delete the migrations table too (not sure why deleting all the rows in it hadn't been sufficient, but at least I'm back in business now):

I dropped all the tables in my database, including the migrations table
I recreated the migrations table with artisan like so: php artisan migrate:install
Then migrations ran successfully, no problem: php artisan:migrate

Everything working again- hooray!

In weird state with foreign key after migrations bug

1 Answers