Primary key error when running laravel migrations

I can’t set primary keys using laravel migrations. Example code;

Schema::create('oauth_auth_codes', function (Blueprint $table) {
            $table->string('id', 100)->primary();
            $table->integer('user_id');
            $table->integer('client_id');
            $table->text('scopes')->nullable();
            $table->boolean('revoked');
            $table->dateTime('expires_at')->nullable();
        });

When I attempt to create the above table using laravel migrations php artisan migrate, an error is thrown;

Illuminate\Database\QueryException  : SQLSTATE[XX000]: Internal error: 7 ERROR:  multiple primary keys for table "oauth_auth_codes" are not allowed (SQL: alter table "oauth_auth_codes" add primary key ("id"))

  at /var/www/html/auth.example.test/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664
    660|         // If an exception occurs when attempting to run a query, we'll format the error
    661|         // message to include the bindings with SQL, which will make this exception a
    662|         // lot more helpful to the developer instead of just the database's errors.
    663|         catch (Exception $e) {
  > 664|             throw new QueryException(
    665|                 $query, $this->prepareBindings($bindings), $e
    666|             );
    667|         }
    668| 

  Exception trace:

  1   PDOException::("SQLSTATE[XX000]: Internal error: 7 ERROR:  multiple primary keys for table "oauth_auth_codes" are not allowed")
      /var/www/html/auth.example.test/vendor/laravel/framework/src/Illuminate/Database/Connection.php:458

  2   PDOStatement::execute()
      /var/www/html/auth.example.test/vendor/laravel/framework/src/Illuminate/Database/Connection.php:458

I am using a customized pgsql driver - cockroachdb-laravel - to make a connection from my app to cockroachdb.

@tokoiwesley - what SQL is actually sent through pgsql to CRDB? It sounds like the driver is applying the primary key constraint to more than one column.

When using the statement $table->increments('id'); or $table->bigIncrements('id'); for the primary key, everything is fine. However $table->string('id')->primary(); and $table->uuid('id')->primary(); produce the error.

What Laravel migrations is doing with ->primary() method is creating the table with the first query and then issuing a second query (ALTER) to set the primary key on the table.

Hi @tokoiwesley, to determine whether or not this is a bug with CRDB we’d need to see the actual SQL sent to CRDB. Can you share the SQL created by $table->increments('id');, $table->bigIncrements('id');, $table->string('id')->primary();, and $table->uuid('id')->primary();?

Hi @tim-o, here are the different examples you have requested - with both Laravel’s schema builder code run by the migration and the equivalent SQL that is generated and sent to CRDB;

Note: I am using Laravel v5.6

1. $table->increments('id')

Migration Schema

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

SQL generated

CreateUsersTable: create table "users" ("id" serial primary key not null, "name" varchar(255) not null, "email" varchar(255) not null, "password" varchar(255) not null, "remember_token" varchar(100) null, "created_at" timestamp(0) without time zone null, "updated_at" timestamp(0) without time zone null)
CreateUsersTable: alter table "users" add constraint "users_email_unique" unique ("email")

2. $table->bigIncrements('id')

Migration Schema

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

SQL generated

CreateUsersTable: create table "users" ("id" bigserial primary key not null, "name" varchar(255) not null, "email" varchar(255) not null, "password" varchar(255) not null, "remember_token" varchar(100) null, "created_at" timestamp(0) without time zone null, "updated_at" timestamp(0) without time zone null)
CreateUsersTable: alter table "users" add constraint "users_email_unique" unique ("email")

3. $table->string('id')->primary()

Migration Schema

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

SQL generated

CreateUsersTable: create table "users" ("id" varchar(255) not null, "name" varchar(255) not null, "email" varchar(255) not null, "password" varchar(255) not null, "remember_token" varchar(100) null, "created_at" timestamp(0) without time zone null, "updated_at" timestamp(0) without time zone null)
CreateUsersTable: alter table "users" add primary key ("id")
CreateUsersTable: alter table "users" add constraint "users_email_unique" unique ("email")

4. $table->uuid('id')->primary()

Migration Schema

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

SQL generated

CreateUsersTable: create table "users" ("id" uuid not null, "name" varchar(255) not null, "email" varchar(255) not null, "password" varchar(255) not null, "remember_token" varchar(100) null, "created_at" timestamp(0) without time zone null, "updated_at" timestamp(0) without time zone null)
CreateUsersTable: alter table "users" add primary key ("id")
CreateUsersTable: alter table "users" add constraint "users_email_unique" unique ("email")

Hey @tokoiwesley - the reason the latter two are failing is because they are attempting to add a primary key after table creation. This isn’t allowed currently. You’d need to adjust the code for $table->string('id')->primary() and $table->uuid('id')->primary() so that they add the primary key in the create table statement, rather than afterward in an alter table statement. So rather than:

CreateUsersTable: create table "users" ("id" varchar(255) not null, "name" varchar(255) not null, "email" varchar(255) not null, "password" varchar(255) not null, "remember_token" varchar(100) null, "created_at" timestamp(0) without time zone null, "updated_at" timestamp(0) without time zone null)
CreateUsersTable: alter table "users" add primary key ("id")
CreateUsersTable: alter table "users" add constraint "users_email_unique" unique ("email")

You’d need:

CreateUsersTable: create table "users" ("id" varchar(255) primary key not null, "name" varchar(255) not null, "email" varchar(255) not null, "password" varchar(255) not null, "remember_token" varchar(100) null, "created_at" timestamp(0) without time zone null, "updated_at" timestamp(0) without time zone null)
CreateUsersTable: alter table "users" add constraint "users_email_unique" unique ("email")

Hope that helps!