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