Home ยป Laravel composite unique index validations

Laravel composite unique index validations

Laravel composite unique validation rule
Share this post:

Laravel provides several approaches for data validation out of the box, but sometimes the validation rules for non-common cases can be cumbersome or even a little difficult to understand. One of the things some developers find confusing is composite unique validation.

In this article, I want to share with you a more readable and easier-to-understand way you can validate composite unique columns (compound indexes) in your Laravel project.

You can watch this lesson on YouTube: Laravel composite unique validation

But first,

What are composite unique indexes?

A composite unique index arises where you want a combination of two or more columns in your database table to be a unique index. A composite index is also known as a compound index.

For example, let’s imagine a table named subjects with columns course_id and title. We want a combination of course_id and title to be unique.

For example, if we have a row in the database table with course_id: 1, title: “Biology” we don’t want to have another row with the same entry course_id: 1, title: “Biology”. Of course, we can have other rows such as course_id: 2, title: “Biology or course_id: 1, title: “Physics”, etc.

How to define composite unique index columns in Laravel

Given our example subjects table, we can declare our composite cols with the unique index thus:

Schema::create('subjects', function (Blueprint $table) {
            ...
            $table->string('title');
            $table->foreignId('course_id');
            $table->unique(['course_id', 'title']);
            ...
 });

Laravel composite unique validation

Laravel composite unique validation
Laravel composite unique validation

While it is possible to use built-in Laravel validation rules to validate composite unique index, we’re going to use a custom validation Rule to accomplish the same thing. I find the custom validation rule cleaner and easier to understand.

Create a custom validation rule

Let’s create a validation rule named IsCompositeUnique by first running the following artisan command:

php artisan make:rule IsCompositeUnique

The above command will create IsCompositeUnique in App\Rules namespace. Let’s open the file and add the following codes which checks the database to ensure that the columns combinations are unique or throw validation error messages if otherwise. This rule can take care of both create and update (where we specify the id of the record being updated):

<?php
 
namespace App\Rules;
 
use Illuminate\Contracts\Validation\InvokableRule;
use Illuminate\Support\Facades\DB;
 
class IsCompositeUnique implements InvokableRule
{
   private string $tableName;
   private array $compositeColsKeyValue = [];
   private $rowId;
 
   /**
    * Create a new rule instance.
    *
    * @return void
    */
   public function __construct(string $tableName, array $compositeColsKeyValue, $rowId = null)
   {
       $this->tableName = $tableName;
       $this->compositeColsKeyValue = $compositeColsKeyValue;
       $this->rowId = $rowId;
   }
 
   /**
    * Run the validation rule.
    *
    * @param  string  $attribute
    * @param  mixed  $value
    * @param  \Closure(string): \Illuminate\Translation\PotentiallyTranslatedString  $fail
    * @return void
    */
   public function __invoke($attribute, $value, $fail)
   {
    //    if (!notAssociativeArray($this->compositeColsKeyValue)) {
    //        $fail('argument 1 must be an associative array');
    //        return;
    //    }
 
       $passess = true;
 
       if ($this->rowId) {
           $record = DB::table($this->tableName)->where($this->compositeColsKeyValue)->first();
           $passess = !$record || ($record && $record->id == $this->rowId);
       } else {
           $passess = !DB::table($this->tableName)->where($this->compositeColsKeyValue)->exists();
       }
 
       if (!$passess) {
           $fail($this->duplicatesErrorMessage());
       }
   }
 
   private function duplicatesErrorMessage()
   {
       $colNames = '';
       foreach ($this->compositeColsKeyValue as $col => $value) {
           $colNames .= $col . ', ';
       }
       $colNames = rtrim($colNames, ', ');
 
       return "The combination of $colNames must be unique.";
   }
}

Codes Explanation

constructor

The constructor takes parameters which are

  • the table name to search for the records,
  • an associative array of columns to search and their respective values,
  • optional id which is required when we want to update a record

__invoke method

In the __invoke method of, we’re checking

  1. if rowId is passed to the constructor, if so, it means we’re about to update a record, so we take into account that the record found in the database can be the record we want to update hence, no validation error should be thrown.
  2. if rowId is not passed to the constructor it means we’re creating a new record, hence if any matching record already exists, a validation error is thrown.

duplicatesErrorMessage method

The duplicatesErrorMessage is just a helper method that puts together a user-friendly validation error message. For our use case, the message returned by duplicatesErrorMessage when the composite uniqueness is violated will be “The combination of title, course_id must be unique.

How to apply the validation rule

We can apply the composite uniqueness validation rule the same way we apply other rules. For example, let’s create a Request class for validation using the following artisan command:

php artisan make:request CreateSubjectRequest

This will generate the CreateSubjectRequest in App\Http\Requests namespace. We go ahead and apply the validation rule:

When creating a new record:

/**
     * Get the validation rules that apply to the request.
     *
     * @return array<string, mixed>
     */
    public function rules()
    {
        return [
            'title' => ['required', 'string', new IsCompositeUnique('subjects', ['title' => $this->title, 'course_id' => $this->course_id])],
            'course_id' => ['required', 'integer', 'exists:courses,id'],
        ];
    }

Go ahead and use CreateSubjectRequest in your controller method. for example:

public function store(CreateSubjectRequest $request) {
        return response()->json(Subject::create($request->validated()));
}

If you try to create two different records that have the same title and course_id you’ll get the user-friendly error message as we defined in the custom validation class.

When updating an existing record:

The difference when updating is that we pass the id of the record we’re updating to the IsCompositeUnique constructor so it knows not to throw an error when the matching record is the one we’re updating. Here is what applying the validation rule looks like:

'title' => ['required', 'string', new IsCompositeUnique('subjects', ['title' => $this->title, 'course_id' => $this->course_id], $this->id)],

Notes:

  • While we applied the IsCompositeUnique rule to the title field, you can decide to apply it to the other composite columns instead, in this case, the course_id field
  • Git repo for the demo

Any comments, or suggestions… please drop a comment. Also, please share this article. Thanks and happy coding

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.