0

I am trying to create a foreign key constraint between my two tables on Laravel 4, User and GP. A user can be associated with many GPs. The foreign key is 'Practice_ID' in the users table, which associates with the ID in the GP table.

    public function up()
{

    // Creates GP table
    Schema::create('gp', function($ta)
    {
        $ta->bigIncrements('id');
        $ta->string('address_1');
        $ta->string('address_2');
        $ta->string('address_3');
        $ta->string('post_code');
        $ta->timestamps();
    });

    // Creates the users table
    Schema::create('users', function($table)
    {
        $table->increments('id');
        $table->string('username');
        $table->string('email');
        $table->string('password');
        $table->string('confirmation_code');
        $table->boolean('confirmed')->default(false);
        $table->bigInteger('practice_id');
        $table->foreign('practice_id')->references('id')->on('gp');
        $table->timestamps();
    });

    // Creates password reminders table
    Schema::create('password_reminders', function($t)
    {
        $t->string('email');
        $t->string('token');
        $t->timestamp('created_at');
    });

}

The error I am getting is:

[Exception]
SQLSTATE[HY000]: General error: 1005 Can't create table 'doctor.#sql-3ac_a4' (errno: 150) (SQL: alter table users add constraint users_practice_id_foreign foreign key (practice_id) references gp (id)) (Bindings: array (
))

1 Answers1

0

err 150 is most likely a data type mismatch.

delcare the foregin key as an unsigned int in the users table:

$table->integer('practice_id')->unsigned();

Also verify your db engine is INNODB that supports FK constraints

A.O.
  • 3,733
  • 6
  • 30
  • 49
  • Hi A.O, I've made the adjustments (see amended code above), however, on php artisan migrate:refresh, the same error still appears. –  Nov 07 '13 at 22:17
  • change it back to just regular `increments` in the gp table and try the above code in the users table – A.O. Nov 07 '13 at 22:20