CODEWITHSUNDEEP
mysqllaravel
sesc360
sesc360

Reputation: 3255

Primary Key length in SQL

I added some migrations to build SQL tables in a laravel app.

Table users

public function up()
    {
        Schema::create('users', function(Blueprint $table)
        {
            $table->increments('id');
            $table->string('user_id')->unique();
            $table->string('email')->unique();
            $table->string('password', 60);
            $table->rememberToken();
            $table->timestamps();
        });
    }

Table userroles

public function up()
{
    Schema::create('userroles', function(Blueprint $table)
    {
        $table->foreign('user_id')->references('user_id')->on('users');
        $table->foreign('role_id')->references('id')->on('roles')->onDelete('cascade');
        $table->timestamps();
    });
}

But when I run them, I get the error:

  [Illuminate\Database\QueryException]                                                                                                                                                                                          
  SQLSTATE[42000]: Syntax error or access violation: 1072 Key column 'user_id' doesn't exist in table (SQL: alter table `userroles` add constraint userroles_user_id_foreign foreign key (`user_id`) references `users` (`user  
  _id`))

Why is that? The second entry in the users table is "user_id" as expected, I think? Does this column have to be a PK? And as this column will have GUIDs as entries, is it suitable to use GUIDs as PK at all?

Upvotes: 0

Views: 237

Answers (1)

Merhawi Fissehaye
Merhawi Fissehaye

Reputation: 2817

In the userroles table, you need to first define the keys before setting them up for a foreign key.

public function up()
{
    Schema::create('userroles', function(Blueprint $table)
    {
        $table->string('user_id');
        $table->unsignedInteger('role_id');
        $table->foreign('user_id')->references('user_id')->on('users');
        $table->foreign('role_id')->references('id')->on('roles')->onDelete('cascade');
        $table->timestamps();
    });
}

Upvotes: 2

Related Questions