How to create table with Foreign Keys in Laravel?

I have a users table

Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('email')->unique();
    $table->timestamp('email_verified_at')->nullable();
    $table->string('password');
    $table->rememberToken();
    $table->timestamps();
});

and a roles table

Schema::create('roles', function (Blueprint $table) {
    $table->id('role_id');
    $table->string('role_name');
});

Now I want to create a user_role table which have two foreign keys

Schema::create('user_role', function (Blueprint $table) {
    $table->foreign('user_id')->references('id')->on('users');
    $table->foreign('role_id')->references('role_id')->on('roles');
});

But I’m getting an error:

IlluminateDatabaseQueryException

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘) default character set utf8mb4 collate ‘utf8mb4_unicode_ci” at line 1 (SQL: create table user_role () default character set utf8mb4 collate ‘utf8mb4_unicode_ci’)

Answer

You can create the field and add a foreign key to it directly by using foreignIdFor(). This creates a column UNSIGNED BIGINT and adds a foreign key to the parent table.

Schema::create('user_role', function (Blueprint $table) {
    $table->foreignIdFor(AppModelsUser::class);
    $table->foreignIdFor(AppModelsRole::class);
});

Under the hood, this is how the foreginIdFor and foreginId methods looks like. As you can see, it attaches the foreign key by the primary key type of the model you’re using.

/**
 * Create a new unsigned big integer (8-byte) column on the table.
 *
 * @param  string  $column
 * @return IlluminateDatabaseSchemaForeignIdColumnDefinition
 */
public function foreignId($column)
{
    $this->columns[] = $column = new ForeignIdColumnDefinition($this, [
        'type' => 'bigInteger',
        'name' => $column,
        'autoIncrement' => false,
        'unsigned' => true,
    ]);

    return $column;
}

/**
 * Create a foreign ID column for the given model.
 *
 * @param  IlluminateDatabaseEloquentModel|string  $model
 * @param  string|null  $column
 * @return IlluminateDatabaseSchemaForeignIdColumnDefinition
 */
public function foreignIdFor($model, $column = null)
{
    if (is_string($model)) {
        $model = new $model;
    }

    return $model->getKeyType() === 'int' && $model->getIncrementing()
                ? $this->foreignId($column ?: $model->getForeignKey())
                : $this->foreignUuid($column ?: $model->getForeignKey());
}