Home > database >  How to declare a foreign key in laravel
How to declare a foreign key in laravel

Time:01-02

So im trying to connect 2 tables (users,drawing) in laravel,all of the other columns are being created,the only thing the compiler has problems with is the foreign key, this is the code for both of the tables

Schema::create('users', function (Blueprint $table) {
    $table->id('users_id');
    $table->string('name');
    $table->string('email')->unique();
    $table->string('password');
    $table->timestamp('email_verified_at')->nullable();
    $table->rememberToken();
    $table->timestamps();
});
    Schema::create('drawings', function (Blueprint $table) {
        $table->id('drawings_id');
        $table->string('name');
        $table->integer('canvas_size');
        $table->foreign('users_id')
        ->references('users_id')->on('users')->onDelete('cascade');
        $table->timestamps();
    });

and this is the error I get when trying the command(php artisan migrate)

SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'users_id' doesn't exist in table (SQL: alter table `drawings` add constraint `drawings_users_id_foreign` foreign key (`users_id`) references `users` (`users_id`) on delete cascade)

I tried different methods, the old one and new one,neither is working for me,can you please help, also I'm using OpenServer for the database

Solution:My problem was that I was using $table->unsignedInteger('users_id')->nullable(); instead of $table->unsignedBigInteger('users_id')->nullable(); for the users_id,sorry guys my bad

CodePudding user response:

The problem is that you haven't added the user_id column in drawings table.

I will rewrite the two migration files according to the best practice:

Schema::create('users', function (Blueprint $table) {
    $table->id(); // You have to leave the primary key "id" so leave this empty
    $table->string('name');
    $table->string('email')->unique();
    $table->string('password');
    $table->timestamp('email_verified_at')->nullable();
    $table->rememberToken();
    $table->timestamps();
});

    Schema::create('drawings', function (Blueprint $table) {
        $table->id();// You have to leave the primary key "id" so leave this empty
        $table->string('name');
        $table->integer('canvas_size');
        // You have to add the foreign key column first
        $table->unsignedBigInteger('user_id'); // Best to make it single
        $table->foreign('user_id')
         ->references('id')->on('users')->onDelete('cascade');
        $table->timestamps();
    });
  • Related