Courses

Structuring Databases in Laravel 11

Non-Standard Primary Keys and Unique Indexes

Summary of this lesson:
- Managing unique constraints
- Implementing composite primary keys
- Setting up unique indexes
- Database-level validation

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

The full lesson is only for Premium Members.
Want to access all 18 lessons of this course? (81 min read)

You also get:

  • 69 courses (majority in latest Laravel 11)
  • Premium tutorials
  • Access to repositories
  • Private Discord