Non-Standard Primary Keys and Unique Indexes

In this lesson, we will discuss primary keys and unique indexes. It's often beneficial to ensure the rules on the DB level to avoid accidents if a new developer forgets to validate data in the future.

Primary Key Without "id" Column?

You're probably used to the code like this:

Schema::create('users', function (Blueprint $table) {
    $table->id();
    // ...
});

In MySQL language, it's transformed into this SQL:

CREATE TABLE `users` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  # ...
  PRIMARY KEY (`id`),
);

It helps to avoid duplicate primary keys, so the identifier is unique, and validation is done on the SQL side.

So, if someone runs a query with an already existing primary key...

INSERT INTO `users` (`id`, `name`, ...) VALUES (1, 'Test User', ...);

The error is this: "Query 1 ERROR at Line 1: : Duplicate entry '1' for key 'users.PRIMARY'"

But what if the id column doesn't exist? Like for example, in a Pivot table for many-to-many relationships.

What should be the primary key here?

Schema::create('role_user', function (Blueprint $table) {
    $table->foreignId('user_id')->constrained();
    $table->foreignId('role_id')->constrained();
});

If we don't add any unique rules, someone may run this query twice:

INSERT INTO `role_user` (`role_id`, `user_id`) VALUES (1, 1);

And end up with this result:

Of course, you may create a separate id column, but it's not the only option.

You may create a composite primary key, like this:

Schema::create('role_user', function (Blueprint $table) {
    $table->foreignId('user_id')->constrained();
    $table->foreignId('role_id')->constrained();
 
    $table->primary(['user_id', 'role_id']);
});

And now, we can't add the same role for the user for the second time. We would get this error: "Query 1 ERROR at Line 1: : Duplicate entry '1-1' for key 'role_user.PRIMARY'"

In SQL, the syntax for such a primary key is this:

CREATE TABLE `role_user` (
  `role_id` bigint unsigned NOT NULL,
  `user_id` bigint unsigned NOT NULL,
  PRIMARY KEY (`role_id`,`user_id`)
);

Unique Column: Validation on DB Level

Even the default Laravel migration for the users table comes with this:

Schema::create('users', function (Blueprint $table) {
    $table->id();
    $table->string('name');
    $table->string('email')->unique(); 
 
    // ...

So, users should have unique emails, with validation happening on the DB level, even if the Laravel developer in the Controller code forgets to validate the uniqueness.

For the UNIQUE index, the SQL error is different than a primary key: "Query 1 ERROR at Line 1: : Duplicate entry 'admin@admin.com' for key 'users.users_email_unique'"

Here are a few more examples.

A table for global settings from pterodactyl/panel project:

Schema::create('settings', function (Blueprint $table) {
    $table->string('key')->unique();
    $table->text('value');
});

It makes sense to ensure that global settings wouldn't conflict, right?

Another example is for account_types from the firefly-iii project:

Schema::create('account_types', function (Blueprint $table): {
    $table->increments('id');
    $table->string('type', 50)->unique();
    $table->timestamps();
});