How to validate for uniqueness of 2 columns?

Question

MySQL

There is such code in migration

$table->unique([ "parent_id", "slug" ], "unique_slug");

and such in the validator

"slug" => "required|unique:categories,slug,NULL,id,parent_id,$parentId"

The problem is that parent_id is nullable and neither laravel nor MySQL can validate when it's null.
Those. I can't create a post twice

[
    "slug" => "my_slug",
    "parent_id" => 1
]

but I can do whatever

[
    "slug" => "my_slug",
    "parent_id" => null
]

Ps parent_id is pulled from the package and setting its default value to 0 fails

Answer 1

Judging by this question , uniqueness on two columns will not work in mysql if one of them is null. I did not dig further in this direction and did so. Only the slug field is unique and is stored as parent_parent_..._parent_slug/.../parent_slug/slug. Yes, when you need to update the slug, you need to get all the descendants and update this field from them too, but when you need to read the record to find out the full slug, you don’t need to get all the ancestors

Answer 2

MySQL treats all null values ​​as distinct . Start from this.