CODEWITHSUNDEEP
phpmysqllaraveleloquentforeign-keys
Emil Jamel Mahmuda
Emil Jamel Mahmuda

Reputation: 21

errno: 150 "Foreign key constraint is incorrectly formed")

Can anyone give me a solution from the following error message "errno: 150" Foreign key constraint is incorrectly formed ")" ?. The problem occurs when I want to create a foreign key from the user table to the profile table using the email attribute, where the primary key in the profile table is the email itself.

Table users

Schema::create('users', function (Blueprint $table) {
        $table->bigInteger('id', 20)->autoIncrement();
        $table->string('id_provider', 20)->nullable();
        $table->string('provider_name', 255);
        $table->string('email', 255)->unique();
        $table->foreign('email', 255)->references('email')->on('profile')->onDelete('cascade');
        $table->timestamp('email_verified_at')->nullable();
        $table->string('password', 255)->nullable();
        $table->string('role', 12);
        $table->rememberToken();
        $table->timestamps();
        $table->primary('id');
    });

Model User

/**
 * Get the profile record associated with user.
 */
public function profile()
{
    return $this->hasOne('App\Profile');
}

Tabel Profile

Schema::create('profile', function (Blueprint $table) {
        $table->string('email', 255)->unique();
        $table->string('name', 255);
        $table->integer('phone_number', 12)->nullable();
        $table->string('gender', 6)->nullable();
        $table->integer('date', 2)->nullable();
        $table->string('month', 9)->nullable();
        $table->integer('year', 4)->nullable();
        $table->bigInteger('id_address', 20)->nullable();
        $table->string('avatar', 100);
        $table->primary('email');
    });

Model Profile

 /**
 * Get the user that owns the profile.
 */
public function user()
{
    return $this->belongsTo('App\User');
}

Thank you for all the solutions.

Upvotes: 1

Views: 254

Answers (2)

emekamba
emekamba

Reputation: 188

The foreign key column is STRING unique() and the referenced column is of type PRIMARY instead of type STRING. In your profile table you have two columns with the same name but of different type.

Table Profile

Schema::create('profile', function (Blueprint $table) {
    $table->string('email', 255)->unique();
    $table->string('name', 255);
    $table->integer('phone_number', 12)->nullable();
    $table->string('gender', 6)->nullable();
    $table->integer('date', 2)->nullable();
    $table->string('month', 9)->nullable();
    $table->integer('year', 4)->nullable();
    $table->bigInteger('id_address', 20)->nullable();
    $table->string('avatar', 100);
    $table->primary('email'); <--------You have a mix up here. The column should be $table->primary('id')
});

Upvotes: 1

Mikayel
Mikayel

Reputation: 25

$table->foreign('email')->references('email')->on('profile');

Your User Model: 

public function profile()
{
  return $this->hasOne(Profile::class, 'email', 'email');
}

Your Profile Model:

public function user()
{
    return $this->belongsTo(User::class);
}

Upvotes: 1

Related Questions