sequelize + sequelize-hierarchy: How enforce a unique organization name per level?

Question

I've an organizations' tree and want to ensure that there is only one organization (using its name and parent_id) at the same level in the tree, even at the root level.

For instance:

valid trees:

/org1 /org2

/org1/org2
     /org3

invalid trees:

/org1
/org1


/org1/org2
     /org2

Model:

var Organization = global.erp.orm.sequelize.define('Organization',
lodash.extend({}, global.erp.orm.mixins.attributes, {
  name      : {
    type     : global.erp.orm.Sequelize.STRING,
    comment  : 'The organization short name (its common referred name).',
    allowNull: false
  }
}), {
  comment  : 'An organization.',
  hierarchy: true
});

I've tried the following but without any results:

{
  comment  : 'An organization.',
  hierarchy: true,
  indexes     : [
    {
      unique: true,
      fields: ['parent_id', 'name']
    }
  ],
}

UPDATE:

I narrowed the issue to the case that when parent_id is NULL, so it seems that unique composite indexes don't work when one of the values is NULL.

So, there is a solution to this?

Thanks in advance.

Have a look at this, not sure if that is exactly what you want but looks similar - http://stackoverflow.com/questions/26006724/find-whether-graph-has-a-cycle — Bulat, Sep 04 '15 at 19:03
@Bulat Thanks for your comment. No, sadly that isn't the issue I have :( Can you take a new look to the problem with the update I made to the question? Thanks! — Diosney, Sep 04 '15 at 19:24
@ezrepotein sqlite and mysql, but right now I'm testing over sqlite — Diosney, Sep 04 '15 at 20:36
@diosney Can this be the issue you are facing right now: http://stackoverflow.com/questions/22699409/sqlite-null-and-unique ? — ezpn, Sep 04 '15 at 20:39
@ezrepotein Thanks. Sadly it is :( More specifically at http://www.sqlite.org/nulls.html from the answer you provided, the table row stating **nulls are distinct in a UNIQUE column**. That just sucks. This is the only thing so far I approve from MS-SQL. Any ideas on how overcome it? — Diosney, Sep 04 '15 at 20:52

ezpn · Accepted Answer · 2015-09-04T20:56:14.290

2

Cause of this issue lies in the way sqlite and mysql interpret SQL-92 standard. They treat each NULL value as unique. That is why rows with the same values and NULL are possible.

You can read more here: https://www.sqlite.org/lang_createindex.html

This problem cannot be solved using default sequelize index declaration in model if you want to keep NULL values possible.

However you may declare those fields as NOT NULL and come up with your own default "nullish" value, 0 or -1 for example.

edited Sep 04 '15 at 20:56

answered Sep 04 '15 at 20:49

ezpn

1,748
15
22

Thanks, sadly, I'm not setting the foreign key I'm using for uniqueness, but a plugin, so I will negotiate this with plugin author. Thanks again. – Diosney Sep 04 '15 at 21:06

score 0 · Answer 2 · answered Sep 04 '15 at 21:48

SOLUTION USING ONLY COMPOSITE INDEXES

Although I already marked an answer as accepted (and I will keep it that way since it pointed me to the right direction thanks @ezrepotein), I'm adding this answer for completeness, since it was the way I solved the issue. Maybe isn't the best solution but it works by using only unique composite indexes, which is was I wanted.

Using hierarchy_level as the check in variable:

{
  comment  : 'An organization.',
  hierarchy: true,
  indexes  : [
    {
      unique: true,
      fields: ['name', 'hierarchy_level'],
      where : {
        hierarchy_level: 1
      }
    },
    {
      unique: true,
      fields: ['name', 'parent_id'],
      where : {
        hierarchy_level: {
          $gt: 1
        }
      }
    }
  ]
}

sequelize + sequelize-hierarchy: How enforce a unique organization name per level?

2 Answers2