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...