Create items unique to a foreign key

How do I create a constraint such that if a value already exists with the same foreign key, it won’t save, but will save if the value exist but with a different foreign key?
e.g phone -> windows OS saves
laptop -> windows OS saves
but if you enter either of them again it won’t save

'use strict'

/** @type {import('@adonisjs/lucid/src/Schema')} */
const Schema = use('Schema')

class CategorySchema extends Schema {
  up () {
    this.create('categories', (table) => {
      table.increments()
      table.text('category_name').notNullable().unique()
      table.timestamps()
    })
  }

  down () {
    this.drop('categories')
  }
}

module.exports = CategorySchema

'use strict'

/** @type {import('@adonisjs/lucid/src/Schema')} */
const Schema = use('Schema')

class SubcategorySchema extends Schema {
  up () {
    this.create('subcategories', (table) => {
      table.increments()
      table.text('subcategory_name').notNullable()
      table.integer('category_id').unsigned().references('id').inTable('categories')
      table.timestamps()
    })
  }

  down () {
    this.drop('subcategories')
  }
}

module.exports = SubcategorySchema

these are my category and sub category schema, I want the subcategories unique to the category_id, such that I can’t have the same subcategory under a category, but I can have that same subcategory under a different category

e.g. A phone with windows os. Phone is the category, Windows os is the subcategory, now if I try to add windows os as a subcategory under Phone category again, it fails, but when I try to add windows os under laptop or desktop category, it saves.
I hope this is expanded enough

Your question isnt clear at all but this maybe helpful for you

STEP ONE

When you’re running a mkigration and don’t repeat a certain value, I mean you need a certain column has unique value then do this


table.string('brand').unique()

The previous code indicates you’re only allowed to insert non repeteable values

Here more info -> Schema tables

nevertheless if this is not the answer you’re looking for, then edit and expand your question more please

This is a database design issue, not AdonisJs related.

1 Like

You can also create a trigger for unique code. I mostly use uuid via Database.

Expanded it