Composite keys unique constraint

Hi guys,
I have a vehicle_make table with company name and model of the vehicle. For eg TATA Safari. I can’t have Company name unique because then i won’t be able to add any Tata vehicles, Also I can’t make the model unique as model name can be same for two different vehicle companies.

Is there any way to apply unique constraint on a combination of columns ie company name + model must be unique, but neither should be unique on their own. How to apply this at database as well as validation level?

Any help will be greatly appreciated.
Thanks

hello

at the database level you can use this:

table.unique(['company', 'model'])

http://knexjs.org/#Schema-unique

1 Like

I was looking for a solution to this today.

I came up with a custom compound field unique provider:

'use strict'

const { ServiceProvider } = require('@adonisjs/fold')

class ValidateExists extends ServiceProvider {
  async _uniqueFn (data, field, message, args, get) {
    const Database = use('Database')
    const util = require('util')

    let ignoreId = null
    const fields = args[1].split('/')
    const table = args[0]
    if (args[2]) {
      ignoreId = args[2]
    }

    const rows = await Database.table(table).where((builder) => {
      for (const f of fields) {
        builder.where(f, '=', get(data, f))
      }
      if (ignoreId) {
        builder.whereNot('id', '=', ignoreId)
      }
    }).count('* as total')

    if (rows[0].total) {
      throw message
    }
  }

  boot () {
    const Validator = use('Validator')

    Validator.extend('uniqueCompound', this._uniqueFn, 'Must be unique')
  }
}

module.exports = ValidateExists

Usage is like this:
uniqueCompound:tags,name/organization_id

and optionally:
uniqueCompound:tags,name/organization_id,22
which may be useful for updates

Where the parts there represent:

  • table
  • field names separated by slashes
  • id to ignore

uniqueCompound:[table],[field1]/[field2]/[field3]/[field4],[id]

This should support any number of compound values, though if you’re joining more than two fields for unique keys there’s probably another way to implement things :slight_smile:

It’s a little hacky but seems to work fine

2 Likes

Hi @willvincent
this Validator looks amazing! :slight_smile: Thank you!
I’m testing this but I’m having a small issue here:

{
    "status": 422,
    "code": "E_VALIDATE_FAILED",
    "message": "Validation failed",
    "errors": [
        {
            "message": "path must be a string or object",
            "field": "email",
            "validation": "ENGINE_EXCEPTION"
        }
    ]
}

I have no idea what such path is but I suppose is related to mongoose.
I have two fields in my collection that have to be unique, so I used this setup for validation:

email: 'required|email|uniqueCompound:users,email/username',

Do you have any suggestion please?

Are you trying to use this with mongo? Definitely no guarantee it’ll work with anything but SQL DBs using the core lucid driver.

Hi! Yes, it’s mongo actually. I had also to modify your code a bit, to use “.collection()” instead “.table()”.

I sure don’t understand why people seem to think mongo is a great solution, and try to force it into a framework that was well designed for a SQL backend, but to each their own I guess… glad you found the validator useful :slight_smile:

1 Like

I totally agree with you :slight_smile:
Sometimes choices are not all done by ourselves :smiley:

Very true. I’m probably slightly bitter about it right now. Am actively migrating off of mongo (and meteor) to Adonis. :slight_smile:

perfect !!