Who is this article for?

Anyone looking for how to validate unique database column with Laravel Soft-Delete options.

In this article, I’m going to walk through, how to create migration with indexes, create two separate requests for creating and updating a resource with one column as unique and using soft-delete.

Before You Begin

In this article, we are going to create a new vendor table via migration. The table will have id, name, created_at, updated_at and deleted_at columns. The name column will be unique. Which means that vendor name should be unique. But this unique validation should exclude the records that are soft-deleted by Laravel.

The API routes will look something like this -

Step 1 – Create the migration.

As you can see in the migration, rather than having the name as a unique column, we are using name and deleted_at as composite unique keys.

Step 2 - Create a Store Vendor Request Class

We will be using the Laravel Requests to validate the input sent by frontend before creating a new resource.

This request will look into the database where deleted_at is null and then compare the names with the name that was sent via the request.

Behind the scene, Laravel runs this SQL query -

SELECT
  COUNT(*) AS aggregate
FROM vendors
WHERE
  name = "CVS" -- This is a test name sent by the request
  and deleted_at IS NULL; 

Step 3 - Create an Update Vendor Request Class

So, our save request validation look good, what about the update request. A user can update the name of the vendor too. So now we need to create a validation for the update.

As you can see above in the API route for an update, we are passing the id of the vendor and laravel resolve it to object. We can leverage the object in the request validation and use it like -

Laravel fires the following SQL Query to validate the update request name param.

SELECT
  COUNT(*) AS aggregate
FROM vendors
WHERE
  name = "CVS"
  AND id <> 1
  AND deleted_at IS NULL;

And so this is how you can use unique column along with soft-deletes in Laravel.

I know, this article was supposed to be how to test a multi-database Laravel project using SQLite in-memory DB for all databases, I will be covering that in a future article.